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.