Cannot connect to Excel SSIS

7
votes

I'm trying to create a data flow task which brings data from Excel File to SQL table. I'm doing it in SQL Server Data Tools for Visual Studio 2012 as an SSIS package. However, when I run the task I receive below error:

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

Would you be able help please?

Thank you

Complete Error message:

SSIS package "C:\Users\adm.turpan\Documents\Visual Studio 2012\Projects\Integration Services Project1\Integration Services Project1\project.dtsx" starting. Information: 0x4004300A at Excel as source, SSIS.Pipeline: Validation phase is beginning. Error: 0xC020801C at Excel as source, Excel Source [52]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209303. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Error: 0xC0047017 at Excel as source, SSIS.Pipeline: Excel Source failed validation and returned error code 0xC020801C. Error: 0xC004700C at Excel as source, SSIS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Excel as source: There were errors during task validation. SSIS package "C:\Users\adm.turpan\Documents\Visual Studio 2012\Projects\Integration Services Project1\Integration Services Project1\project.dtsx" finished: Success.

3
excelssis
What are the other errors in the log? One of them contains the actual explanation. Most likely the file path is wrong – Panagiotis Kanavos
[SSIS.Pipeline] Error: Excel Source failed validation and returned error code 0xC020801C. – Turpan
[SSIS.Pipeline] Error: One or more component failed validation. – Turpan
Is the file a real Excel file or a CSV/HTML file with an .xls extension? Can you open it with Excel? – Panagiotis Kanavos
There can be several reason for this error and only after looking at the complete error message we will know what's exactly wrong. For time being I would recommend you to try change run64BITRuntime as False and set Delay validation as True – mehtat_90

3 Answers

13
votes

mehtat_90 was right. you need to set run64BitRuntime to False. See below where to find it:

Project -> Properities -> enter image description here

0
votes

for whom, the above answers didn't work, also try this. Some times when the file is open, it creates a temp/hidden file in the same location and it is only visible to the one who opened it. So place a break point right before the load and check the value for "FileName" which is being passed. The temp/hidden files would be in thumb.db for excel and different for different file formats. Hope this helps for someone.