Cannot connect to Excel SSIS

Question

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.

Heron

What are the other errors in the log? One of them contains the actual explanation. Most likely the file path is wrong – Stunsail

[SSIS.Pipeline] Error: Excel Source failed validation and returned error code 0xC020801C. – Heron

[SSIS.Pipeline] Error: One or more component failed validation. – Heron

Is the file a real Excel file or a CSV/HTML file with an .xls extension? Can you open it with Excel? – Stunsail

Error: There were errors during task validation. – Heron

Can you open it with Excel? Where does the file come from? – Stunsail

It is real excel, I can open it. – Heron

Validation errors also appear in the log. They also appear on the source and connection manager in the dataflow design view – Stunsail

Those 4 are all the errors in the log. I've checked the path, it's correct. – Heron

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 – Cabob

Hi mehtat_90, I'm pasting above the complete error message. – Heron

Regarding run64BITRuntime as False - where can I find this setting? Cause I'm looking into project's properties, but cannot see anything like that. – Heron

I found it and it seems that solves my issue, although I'm getting different errors now. I'll try to solve them and let you know.. – Heron

Answer 1

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

Project -> Properities ->

Heron

This fixes the error given; though I wonder why... what is it with 64 bit runtime that SSIS and Excel do not like? My best guess would be missing drivers... anyone? – Katrinka

@Katrinka The Excel provider is not available for 64 bits (or at least was) – Ardeb

Answer 2

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.

Equalizer

Answer 3

• Right Click on Project name
• Select Properties
• Select configuration properties
• Select Debugging
• Select "False" in Run64bitRuntime.

Gyron

Answer 4

You should run sql job in 32 bit therefore you should select 32-bit runtime checkbox in the configuration of SSIS Job.

http://microsoft-ssis.blogspot.com/2012/09/swtich-package-from-64bit-to-32bit.html#:~:text=Right%20Click%20the%20SSIS%20project,and%20select%20false%20under%20Run64bitRuntime.&text=Edit%20your%20job%20and%20then,%22Use%2032%20bit%20runtime%22

Glyptography