Looping through excel files

Question

I am trying to load multiple excel files using for each loop container. In my 'Excel Connection Manger' I am accessing the data through 'SQL Command from variable'. I am getting an error message when I try to make the filename dynamic in the connection manager properties. I tried to use two one with Connection String and then with Excel File Path but I get the following message:

[Excel Source [124]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

and I couldn't bypass it. I am loading the data to Azure and the version is below:

Microsoft SQL Azure (RTM) - 12.0.2000.8 May 28 2024 14:55:16 Copyright (C) 2022 Microsoft Corporation

Thanks,

Ali.

Answer 1

Hi @Ali Ahad,

Please have a check that you enable delay validation on the connection manager.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

I have several SSIS packages looping over file in a folder, also Excel-files.

But I never use a SQL command, instead an expression => ExcelFilePath, see

https://learn.microsoft.com/en-us/sql/integration-services/connection-manager/excel-connection-manager?view=sql-server-ver16

=> "Options" => "Excel file path"