0

So I created a ForEach loop and a data flow task to write from Excel to SQL DB. All works fine with the Excel source hard coded. As soon as I change the connection string to use the file path variable as a data source, I get this error:

[Excel Source 1] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager 1" failed with error code 0xC0202009.

I print the file path in a message box before executing the data flow so I know that the variable is working.

enter image description here

enter image description here

Naturally I browsed tons of answers and tutorials, but nothing. Here's what I tried:

  • Changing the data source on the connection string
  • Using the ExcelFilePath expression instead of the connection string
  • Changing the Excel file name in the connection manager properties
  • Ran the package in 32 bits
  • Set delayed validation to True in all data flow tasks and connection manager
  • Deleting and creating a new connection manager
  • Combinations of the above, lots of trial an error

I'm using Visual Studio 2013.

I'd appreciate your help as I've been plucking my hair all afternoon with this :)

MrM1k4d0
  • 55
  • 1
  • 10
  • It sounds like you are missing the expression of setting the foreach enumerator to filename on the connection manager – KeithL Jun 06 '18 at 17:50
  • Also, "Connection string" which is different than "Excel File Path" if you are only updating the path use the later – KeithL Jun 06 '18 at 17:57
  • I cover using expressions with Excel files in this [answer](https://stackoverflow.com/a/21536893/181965) – billinkc Jun 06 '18 at 19:28
  • change property delay validation. – sandeep rawat Jun 07 '18 at 07:09
  • @billinkc I read your fantastic answer, unfortunately it didn't work. I came across it before posting this question and tried using the excel file path. I don't know what else to do. – MrM1k4d0 Jun 07 '18 at 07:49

1 Answers1

0

I never got this to work, so I imported the Excel files to the DB using SQL:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Test\Excel_Data.xlsx;', 'SELECT * FROM [Sheet1$]')

https://www.sqlshack.com/query-excel-data-using-sql-server-linked-servers/

MrM1k4d0
  • 55
  • 1
  • 10