SSIS - 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?

Answer 1

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/

Answer 2

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.

Answer 3

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 :)