Wednesday, December 22, 2010

SSIS - Office 2010 Woes

Good news: I got a new laptop at work that's much faster than my previous one.
Bad news: new laptop = setup issues.

My previous laptop was 32-bit and this one is 64-bit which came with its own challenges. I have the 64-bit Office 2010 installed on it. If you have ever worked with SSIS in a 64-bit environment then you know where this post is going.

I am working on an SSIS package that import data from Excel 2007 to SQL Server. I had created the package on my previous 32-bit laptop and I have to tweak it. Now I get this error: "
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."

I search for a solution and I come across a few things. One is to set my project to use the 32-bit runtime. Another one is to download the Microsoft Access Database Engine 2010 Redistributable which has backwards connectivity components. Another thing suggested changing the connection string to Microsoft.ACE.OLEDB.14.0.

Neither of the above solutions have worked. What eventually worked is downloading and installing the 2007 Office System Driver: Data Connectivity Components. Just for reference this is my connection string: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=filename.xlsx;Extended Properties="EXCEL 12.0;HDR=YES;IMEX=2";

I hope this post will help someone facing the same problems. Merry Christmas!

Just a note: in order to install the Access Database Engine 2010 32-bit version I had to uninstall any 4-bit versions of Office.

No comments:

Post a Comment