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

Question

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

User · Answer

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

User · Answer

Setting RetainSameConnection property to True for Excel manager Worked for me

User · Answer

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 -

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009

Examples related to sql-server

Examples related to excel

Examples related to ssis

Examples related to oledb