Hello friends, I could not write any article for last few weeks as my website (Passionforsql.com) was down and I was working on that issue. I am back with a new article which is again a SSIS topic. Today I am going to talk about “Transfer Database Task”.
Transfer Database Task: Using “Transfer database task” , we can transfer whole database from one server to other server(Move or Copy). We can transfer the database from one server to another server either by coping or by moving it.
Firstly we will drag and drop a “Transfer database task” into “Control flow” region(As shown below).
Now we will configure the “Transfer database task” by right clicking on it and click on “edit” menu that will bring the “Transfer database task editor” screen in front of us(As shown below).
On the above screen, we have to specify the source and destination connection, to specify the source connection we will click on the drop down and click on the “New Connection” item(As shown below).
As soon as we click on the “New Connection”, a new window will appear (As shown below).
Since my source server is my local machine hence, I have mentioned “localhost” in the “Server name” field and click on “OK” button to save the settings(I am using windows authentication here). Similarly we will specify the destination server where we want to move or copy our database. Now we need to specify the source database name which needs to be copied or moved. Below are few properties(of source database) which are important to understand.
Action: In this property we specify whether we are going to move the source database or copy the database to the destination server.
Method: Using this property, we specify if the source database will be moved/copied in offline mode or will be online during the transfer. In offline mode source database is detached and then database files are copied/moved to destination server and then attached to destination server. In case of copy after successful transfer of database files, database is attached at the source server.
Source Database name: In this field we specify the name of the database which is going to be transferred.
Source Database Files: As soon as we specify the source database name, this field automatically fill the details of mdf and ldf files of the source database.
Reattach source database: In this property we specify if the source database will be reattach or not in case of failure. If we set the property true and any failure occurs , database will be reattached.
I have set all above properties of source database(As shown below).
Lets specify few properties of destination database.
Destination Database Name: In this property we can specify a database name and with this same name destination database will be created.
Destination database files: Here we can specify the location of the database files at the destination server.
Destination Overwrite: We can set this property’s value as true or false, if this property is true and there is already a database with same name at the destination server then it will be overwritten.
Important point: If we have selected offline mode of transfer the database, we have to provide value for “Network File Share” field for source as well as destination server as shown below.
Note: Copy the “Destination Folder” path value and paste it to the “Network file Share” field and same process should be followed for Source database. Otherwise your package will not be build successfully.
Below is the screenshot of “Transfer database task Editor” where I have filled all the properties of source and destination server.
Now we are done with the configuration settings, lets click on “OK” button to save the settings. We will run this package by pressing “F5” key and check the result.
Green color of task is indicating that our package ran successfully. I hope you will find this article useful. Next time I will be back with one more SSIS topic till then take care.
703 total views, 1 views today