Transfer data from one instance/database to another instance/database in SQL Server with SSIS

There are many occasions where you need to transfer data from one instance/database of SQL Server to another instance/database of SQL Server. May be archiving, may be for ware house etc.

This is really very much needed utility, not only that, I have came across this question many times in different forums so though to explain this in my blog.

Today, I am not going into much details of each aspect, just transfer data from one source of SQL Server to another source.

Well, open new project from your VS2008. Project type “Business Intelligence Projects” and template should be “Integration services project”.

As soon as you create new project, drag “DataFlow” from tool box to “control flow” which is your work area. See image below for more detail

To configure that “dataflow” double click on that so that you will be redirected to the “Data Flow” tab, right beside “Control Flow” tab.

Now, take Ado.Net Source and Ado.Net Destination task from tool box and drop it into your work area. Connect Ado.Net Destination with Ado.Net Source. Now we will configure both tasks one by one.

Double click on Ado.Net Source task to configure it.

Note: I assume our source is AdventrueWorks database and HumanResouce.Employee table and destination is DataBaseNew on same server. Destination table should be created as “HREmployee” in destination database.

Create your database connection by clicking on “New” button in “Ado.net connection manager”.

From “Data Access Mode” select “Table or View” and from “Name of the table or View” should be “HumanResouce.Employee”. Configuration should look like this:

 Now, double click on “Ado.Net Destination” task. Set the destination database. If you already have table in destination database than select it or click on “new” and create one. Finally click on OK button and run the package by hitting “F5” and confirm whether data is there in your destination database.

This is really very basic example, I will post few more advanced article for this in very near future.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com