Cannot update Excel source properties in SSIS package

Question

I have an SSIS package that imports an excel spreadsheet into an SQL Server 2008 database.

I have an Excel connection in the Connection Managers tab and it points to an Excel file on my local computer.

But when I open the Excel Source in Data Flow, if I try to view columns or change the "Name of Excel sheet:" I get this error.

Error at LoadMasterData [Connection manager "SourceConnectionExcel"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".

Error at Data Flow Task 1 [Source - Sheet1$ [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionExcel" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

The package was saved from SQL Server Management Studio using Import Data. When I run the package it works fine but I want to be able to specify the spreadsheet name as a variable. When I change the option to specify the sheet name as a variable, it won't let me close the window with the "OK" button, and just keeps giving me the same error as above.

Has anyone experienced this behaviour before that can help me get this working?

Thanks

Answer 1

Are you running on 32bit or 64bit? if you're on 64bit you need to know that there are no providers for Excel - you'll have to enforce the package to not use 64bit. Right click project, properties, debugging: Run64BitRuntime - set to false.