Saturday 7 August 2010

SSIS 2008 - "Unspecified error" and "Could not find installable ISAM" Errors After installing Office 2010 and Visual Studio 2010

I recently installed Office 2010 on one of my development machines. Suddently, all of my SQL Server 2008 SSIS packages started to generate errors (when attempting to preview data outputs) to the tune of:


Error at PackageName [Connection manager "ImportFileSource"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.


An OLE DB record is available. Source: "Microsoft Access Database Engine" Hresult: 0x80004005 Description: "Unspecified error".


Error at ImportDataTaskName [OLE DB Source 1 [3639]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ImportFileSource" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)

The SSIS package tasks that had the issues were using a connection string pointing to the Office 12 OLEDB drivers like so:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\temp\FileName.xlsx;Extended Properties="Excel 12.0";Persist Security Info=False"

If you then attempt to do a test run with the same connection string, it will also fail with the following error:

Test connection failed because of an error in initializing provider. Could not find installable ISAM.


The cleanest and most reliable way to fix this issue when using 2007 drivers (rather than attempting to reregister the neccessary dlls or if there are other issues), is to just just install or re-install the "2007 Office System Driver: Data Connectivity Components" - available at the following location:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en

Alternatively you could also just try re-registering the dlls as described here:
http://support.microsoft.com/kb/209805
DDK

2 comments:

Graham said...

Thank you. This worked for me with the Excel 2010 file I was trying to access.

Bharath said...

Thank You David... Your are my life saver :)