SSIS And Excel On A 64-Bit Server

So here’s the setup:  I created an SSIS package which, among other things, reads an Excel file for certain data.  Working in development, the package ran just fine.  But when I published it to production, I started getting an error, namely, “The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.”

The fix for that is to run the package in 32-bit mode.  But when trying to run in 32-bit mode, I still got the same error.  This lead me to look around some more.

After Googling it some more, I discovered that the fix for that is to install BIDS or the Client Tools.  Not wanting to install BIDS (I’m trying to install the minimum number of tools necessary for each server), I installed the Client Tools.  But that didn’t fix the problem—I still couldn’t find the right executable.  I had to install BIDS on the server.  After installing BIDS, everything worked fine.

By “everything worked fine,” I mean “I ended up with yet another problem.”  This time, “The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered.”  In this case, the problem is that I didn’t have Office installed on the server.  There’s a pretty good reason Office wasn’t installed on that server:  I don’t want it to be!

Fortunately, there is a solution.  It involves downloading Office data connectivity components.  Here’s a warning, though:  after you download and install the components, your ODBC driver listing still won’t include them, if you go through the Control Panel.  The reason is that this is the 64-bit ODBC viewer, located at C:\Windows\System32\odbcad.exe.  If you want to check to make sure that the drivers are installed, go to the 32-bit version:  C:\Windows\SySWoW64\odbcad.exe.

But after all of this, everything really worked fine.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s