Missing Access OLE DB Connector in SSIS

And finally a new production server came along with SQL Server 2012. All works fine but the only Access database connection that I have. The image that you see above is the dialog box that I see when adding a new OLE DB connection. As you can see there is no connector for Access…

This scenario is quite common in either new server installations or once you move your code to a production server. The reason behind is that the Access OLE DB connector is installed as part as Office, which probably you will have it installed in the development and UAT servers, but you should never have it installed in the production servers.

To have this connector available in the production environment you need to download and install the “Microsoft Access Database Engine 2010 Redistributable” from here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255

There are 2 versions, 32-bits and 64-bits. If you plan to have Visual Studio installed in the server, so you can debug code, you need to install the 32-bits version. Remember to click the “32-bit runtime” option when scheduling the SSIS packages. But if you are not using Visual Studio in the server at all (because is a production server), you can install the 64-bits version (if your server is 64-bits) and run the SSIS package in full 64-bits. You must decide which version you need to install as these two versions cannot be installed together. You either install one or the other.

Advertisements
Tagged with: , ,
Posted in SSIS
3 comments on “Missing Access OLE DB Connector in SSIS
  1. Ayman El-Ghazali says:

    Thanks for the good post. Do you have any idea if this install is cluster aware or does it need to be installed on each node of a SQL Cluster?

    We had a big debate with our operations team regarding the older version of the driver. They did not want to install it because it was going to be part of the cluster and they didn’t not want to add any unnecessary cluster services. I could not find any information on the link you provided.

    Thanks

  2. jbartual says:

    This is a connector for SSIS and SSIS is not a cluster aware service. So then the connector needs to be installed in all the nodes running SSIS. If you want to install the connector in only one node you can do it, as long as you run in that node the packages that access the Access datasources.
    Hope this helps.

  3. Thanks, this does help a lot. We’ve had software that was only installed on node before and if we move the SQL Instance to another node we run into problems. I remember one time it was because pkzip was only installed on one node and it was a huge problem, in fact people got called on the weekend to fix it. It could have been just a simple 2 minute install upfront.

    Anyway, thanks for the info.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 24 other followers

Categories
%d bloggers like this: