Connect SSIS to MS Access ACCDB databases

Before Microsoft changed Access from .mdb file extensions to .accdb, SSIS would work right out of the box connecting to Access databases.  Due to the new format, you now need to download additional drivers on any machine that runs the SSIS jobs.  I am going to show you how to do it right now.

Below is an example of trying to connect SSIS to an Access .accdb destination without the new drivers.  Click new for the connection manager.

ac1

 

Click new again to add a new ADO.NET connection.

ac2

 

Select the MS Jet 4.0 OLE Db provider, because that is what Access .mdb uses and that is all that is installed by default.

ac3

 

Point it to the Access database you want to connect to.

ac4

Press the Test Connection button to make sure everything is good…..nope.  We get the “Test connection failed because of an error in initializing provider.  Unrecognized database format” error.

ac5

This is because the Jet 4.0 drivers do not understand the new database format that is .accdb.

To get around this we just need to download a new driver.

Following this link takes you to Microsoft’s site and allows you to download the new driver.  https://www.microsoft.com/en-ie/download/confirmation.aspx?id=13255

 

ac6

 

Pick either the 32 or 64 bit version.

ac7

After installing and rebooting, a new driver shows up.  Now when connecting to .accdb databases, make sure you use the Microsoft Office 12.0 Access Database Engine OLE DB Provider.

ac8

 

After selecting the new driver and pointing it to our database, click Test Connection, and now you get connection succeeded!!!!

ac9

Now you can connect SSIS to the new Access .accdb format, welcome to the future.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s