Foreach Loop Container ERROR: [Excel Source [2]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER…"

0
votes

I have a Foreach Loop Container looping through a directory and importing ExcelSource data to SQL Server. The connection is fine until I add the expression to the ExcelSource Connection Property. I followed all the steps in the tutorial. Why do I keep getting the following error?:

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

1
sql-serverexcelforeachssis
Keep Delay validation property to True. Give a try let me know you results/comments. And Post some more details regarding your question. - ram.bi
Although Delay Validation property = TRUE is a valid answer. After manual going through all the files, I realized that a few out of hundreds were not in the same format. After checking the progress of my package I noticed that it looped a bunch of times and then would receive the "CONNECTION ERROR". I just wrote individual packages for those files. THANK FOR YOUR INPUT :) - J.S. Orris
@ram.bi For future reference, do I set the DelayValidation property = TRUE on the ExcelConnectionManager only? Or both the ExcelConnectionManager and OLE DB ConnectionManager? Thanks. - J.S. Orris
@Jefj Orris: It will not be any issue. Please go through following link: sqlservergeeks.com/blogs/raunak.jhawar/sql-server-bi/421/… - ram.bi
I have also found that when focus is on the file when the loop hits the loop will break and give the DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER error - J.S. Orris

1 Answers

1
votes

You would have to set DelayValidation to true on both Foreach Loop Container and Excel Connection Manager for this to work.