SQLServerWiki

“The Only Thing That Is Constant Is Change”

SSIS package to transfer database from source instance to destination instance.

Posted by database-wiki on March 21, 2011

Scenario:

Imagine you have 2 instances in two different boxes. You want to transfer a database from one instance to another as needed. May be a package will be handy to do that.

So, for my customer I created a SSIS package and on executing this SSIS package the database from the source gets transferred to destination server.

1: Open BIDS > File > New > Projects

2) Select Integration Services Project.

3) In the main table select VIEW > Solution Explorer

4) Select “Transfer Database task” from the “Control Flow Items” show in the left.

5) Right click “Transfer Database Task and choose edit. Set the properties as show in the screenshot below.

6) Select the source name first by click the “SourceDatabaseName” in my case it’s rayadav. Also test the connection by clicking Test Connection in the same window.

7) Select the DestinationDatabaseName and DestinationDatabaseFiles. Set them appropriately. And click OK.

8) Run the below package and check the progress bar on the top for the success messages.

9) Select Save Packages.dtsx As… from File menu.

10) Now connect to “Integration Services”

Select Object Explorer and expand Stored Packages and right click File System and select Import Package.

11) Right click the imported package and select run package just to make sure everything is fine.

12) Screen shot below shows the progress.

13) Now connect to your database make sure your sql server agent is running. Expand sql server agent and right click job > select new job.

14) Select Steps from the left size of the screen below general and click new job step. Select package source as File System and navigate to the package location where you save before in step 2.

15) Once done click ok and run the job.

mission accomplished. Roger that… 😉

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: