AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009

Question

AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009

I have an SSIS package which reads an Excel File (Data Flow Source) and transfer the data to SQL Server using OLEDB Destination Data Flow Item. The OLEDB Connection Manager used for the destination is configured to use Windows Authentication. The package works fine on my development machine. But when I open the same package on another machine and try to execute it gives the following error in Validation phase

Error: 0xC020801C at DFT_NSOffers, Source - 'Subscription Offers$' 1 [347]: The AcquireConnection method call to the connection manager "ExcelConnection_NSOffers" failed with error code 0xC0202009. Error: 0xC0047017 at DFT_NSOffers, DTS.Pipeline: component "Source - 'Subscription Offers$' 1" (347) failed validation and returned error code 0xC020801C. Error: 0xC004700C at DFT_NSOffers, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at DFT_NSOffers: There were errors during task validation

I'm using SQL Server 2005 (Version - 9.0.1399)

How do I fix this? Do I need to install any other component or service pack?

sql-server

excel

ssis

oledb

asked on Stack Overflow Apr 14, 2009 by

user82613 • edited May 21, 2013 by

abatishchev

12 Answers

I don't think it's 64/32 bit error. My Dev machine and DB server are 32bit. But I could make it work. I had to set Delay Validation property of Data Flow tasks to TRUE.

answered on Stack Overflow Apr 17, 2009 by

user82613 • edited Nov 4, 2020 by

dns_nx

64/32 bit error? I found this as a problem as my dev machine was 32bit and the production server 64bit. If so, you may need to call the 32bit runtime directly from the command line.

This link says it better (No 64bit JET driver): http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/da076e51-8149-4948-add1-6192d8966ead/

answered on Stack Overflow Apr 14, 2009 by

Meff

Hi This can be solved by changing the prorperty of the project in the solution explorer then give false to 64bit runtime option

answered on Stack Overflow May 2, 2014 by

Raja • edited Nov 4, 2020 by

dns_nx

I was finally able to resolve the "Excel connection issue" in my case it was not a 64 bit issue like some of them had encounterd, I noticed the package worked fine when i didnt enable the package configuration, but i wanted my package to run with the configuration file, digging further into it i noticed i had selected all the properties that were available, I unchecked all and checked only the ones that I needed to store in the package configuration file. and ta dha it works :)

answered on Stack Overflow Oct 25, 2010 by

Vinay

For me, I was accessing my XLS file from a network share. Moving the file for my connection manager to a local folder fixed the issue.

answered on Stack Overflow Aug 13, 2013 by

Mathias Lykkegaard Lorenzen

If you are receiving preview of data in the excel source. But while executing the data flow task you receive Acquire connection error. Then move the file to local system and change the file path in excel connection manager and try executing again.

answered on Stack Overflow May 6, 2019 by

Khyati Sethia

In my case the problem was the 32/64 bit driver which I solved by configuring the properties of the sql server job:

enter image description here

answered on Stack Overflow Mar 7, 2015 by

Stefan Michev

I had similar issue just that excel was the destination in my case instead of source as in the case of the original question/issue. I have spent hours to resolve this issue but looks like finally Soniya Parmar saved the day for me. I have set job and let it run for few iterations already and all is good now. As per her suggestion I set up the delay validation of the Excel connection manager to 'True. Thanks Soniya

answered on Stack Overflow Jul 5, 2014 by

Abdullah

Setting RetainSameConnection property to True for Excel manager Worked for me .

answered on Stack Overflow Feb 27, 2017 by

Sean Pearce

I had similar issue, trying to load data from Excel spreadsheet; and was running on WinX64. So I went VS BI`s project properties: Configuration Properties \ Dbugging and Switch Run64BitRuntime from True to False. It worked.

answered on Stack Overflow Mar 20, 2017 by

user7742030

I was also getting the same error and it simply got resolved after installing the MS offices driver and Execute the job in 32 Bit DTEXEC. Now it works fine.

You can get the setup from below.

https://www.microsoft.com/en-in/download/confirmation.aspx?id=23734

answered on Stack Overflow Jul 20, 2017 by

Naim Halai

In order to resolve this issue make all your data flow tasks in one sequence. It means it should not execute parallel. One data flow task sequence should contain only one data flow task and for this another data flow task as sequence.

Ex:-

enter image description here

answered on Stack Overflow Oct 14, 2014 by

ravula sandeep • edited Oct 14, 2014 by

ravula sandeep

User contributions licensed under CC BY-SA 3.0