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.

 

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

 

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.

 

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

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.

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

 

 

Pick either the 32 or 64 bit version.

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.

 

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

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