Upload data from MS Access database to SQL Server – SSIS

SSIS is a heterogeneous data processing platform. We can handle very old text file to Cloud Database (SQL Azure) and many other third-party data sources.

In this blog post, I am going explain how to create a package that uploads Microsoft Access database to SQL Server.

Implementation

Scenario: I have downloaded a sample database “zipcodes” from about.com database site. I am using this database as my data source.

There are three tables in that source database. But, I am transferring a table “States” with data to SQL Server 2012 database.

Step 1: Create a package and Add ADO NET connection in connection manager

Creating a new connection

Configure connection with MS Access database

Click OK

Step 3: Add ADO NET source into Data Flow Task

Configure ADO NET source task with database connection and table

Step 4: Add ADO NET destination and connect to your local database.

Create connection for Destination database

Create new table. This table schema is extracted from the data source

Map columns between data source and destination  in the Mappings page and Click OK

We have completed the package design.

Step 5: Execute the package now

We have created a simple package to migrate MS Access table to SQL Server.

You can also download Microsoft Access Database Engine 2010 Redistributable here.