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.

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

pic2Configure connection with MS Access database

pic4Click OK

pic3

Step 3: Add ADO NET source into Data Flow Task

adonet

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

pic6

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

pic8

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.

Advertisements
  1. No comments yet.
  1. January 24, 2013 at 12:45 pm

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: