Home > SSIS > Upload data from MS Access database to SQL Server – SSIS

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.


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

pic2Configure connection with MS Access database

pic4Click OK


Step 3: Add ADO NET source into Data Flow Task


Configure ADO NET source task with database connection and table

pic5Step 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

pic7Map 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

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

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


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

%d bloggers like this: