In this post I will outline a straight-forward way to export data to a Microsoft Access .MDB file using SQL Server Integration Services along with the Microsoft Office Access Database OLE DB Provider. I will also show you how to set the Run64BitRuntime package property to ensure your SSIS package can engage the Microsoft Office Access Database Engine provider.

Recently a client was unable to export data to a Microsoft Access .MDB file and enlisted my help. It turned out they were using the wrong Provider to connect to their .MDB file in a destination component. But beyond that, the Run64BitRuntime package property was overlooked. This would have prevented a successful outcome, regardless of whether the correct provider was used.

Per Microsoft’s MSDN documentation, in order to connect to Access 2003 and earlier versions, you need to use the Jet provider. Access 2007 and later versions require the Microsoft Office Access Database OLE DB Provider. See http://msdn.microsoft.com/en-us/library/cc280478(v=SQL.105).aspx for more information.

First, you’ll need to install the 32-bit Access Database Engine on the machine you are running SSIS from. You can get it from download.microsoft.com; just search for “access database engine” in Downloads. You’ll want the 32-bit version regardless of whether you’re using 32- or 64-bit Windows. The provider is only available in a 32-bit version!

Once the provider is installed, create a data flow task with a data source of your choosing. Then create an OLE DB destination component, selecting the newly installed provider as the Provider.

access_provider

Enter the filename of the .MDB you’re exporting to and enter the appropriate credentials. If there is no password on the .MDB file, then leave the “Log on to the server” section alone. Test the connection to verify it succeeds.

Now, the important part! You need to set the package to run in 32-bit mode, otherwise the connection to the MDB will not succeed. This is because the provider is 32-bit. To do this, right click on the package name in the Solution Explorer window in SSIS and select properties.

ssis_solution_explorer_properties

Under the Debugging section, change the value of Run64BitRuntime from “True” to “False.”

ssis_solution_explorer_properties_pages

Give it a whirl by executing your data flow task inside your SSIS package!

Leave a Reply

Your email address will not be published. Required fields are marked *