Cannot loop through Excel 2003 files in SSIS 2008

Question

Hi,

I am trying to execute a SSIS 2008 package on a 64-bit OS and import Excel 2003 files to SQL Server 2008.
I have created an OLEDB Connection to the Excel file with a Connection String that retrieves the Excel file from a variable, inside the ForEach Loop Container.
The Run64BitRunTime is set to false.
I am not able to edit the SQL Command on the OLEDB Source in the Data Flow task. It returns an error :

Error 2 Validation error. Load List Staged Table: Load List Staged Table: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "List OLEDB to Excel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. 0 0

Appreciate any help.

Answer 1

Check this out: http://dougbert.com/blogs/dougbert/archive/2008/06/16/excel-in-integration-services-part-1-of-3-connections-and-components.aspx

I've had problems with excel on 64 bit systems - You'll have to run the 32 bit dtexec for the job. Why you are getting errors inside BIDS, I don't know.

Answer 2

Yes, I am running BIDS on a 64-bit OS. I resolved the issue by using an Excel Connection Manager in the Control Flow and an Excel source in the Data Flow Task instead of the OLEDB source and assigning a default workbook name to the variable. I also made changes to the data access mode in the Source editor. Thanks for the response.