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
![pic3](https://sqlserverrider.com/wp-content/uploads/2013/01/pic311.png?w=1100)
Step 3: Add ADO NET source into Data Flow Task
![adonet](https://sqlserverrider.com/wp-content/uploads/2013/01/adonet.png?w=1100)
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
![pic6](https://sqlserverrider.com/wp-content/uploads/2013/01/pic69.png?w=1100)
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
![pic8](https://sqlserverrider.com/wp-content/uploads/2013/01/pic88.png?w=1100)
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.