SQL Server Integration Services Transfer DB Task

Introduction

Moving forward on the quick tour of SSIS, the next few items that are going to be covered are database maintenance tasks followed by deploying a package and finally calling a SSIS package from some C# code. The most common of the database maintenance tasks is the Transfer Database Task which does exactly what it says.

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

Scenario

Moving or copying a database can be a mundane repetitive task. Some of the common reasons for transferring a database are: deploying a database for the first time, copying live data to a test or development region, or even moving a database to a new server due to new hardware deployment. Whatever the reason may be, the Transfer Database Task can make the job easier, especially if it is a task that happens on a regular basis. This article will deal with transferring live data to a new SQL server that doesn’t have the database yet.

Implementation

Since this is a maintenance task, a new blank project is needed. There is no need to copy and paste the previous SSIS package that was used in the last article. So, with a clean slate, click on the Control Flow tab. All of the work will be done in the Control Flow tab and only one component is needed. Locate and drag the Transfer Database Task from the toolbox to the work area.

Double click the component to bring up the editor window. As with most components there are several different ways to configure the Transfer Database Task. Click on the tab to get to the main configuration information.

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.

First, click in the box next to the SourceConnection and an option to add a new connection is available. Keep in mind that the task is to move or copy a database and not to work with any specific table data so if there are any other connections listed that pertain to a table then it is best not to use that connection. When the adding a new connection, another configuration window will appear and will need to be filled out. This information is where the database is physically located so make sure the fully qualified path and network share path are available.

Once the proper information has been filled in, click OK and then repeat the process DestinationConnection.

In the next table for the Destination table information, fill in the name of the database and whether there is a need to overwrite the existing destination database. In this example, there is no destination database but the option to overwrite is set to True. In the Source table, the option to copy or move the database is available. Since the destination is a new SQL server instance, the copy option is used. Next there is an option to move the database while it is online (in use) or offline (not in use). Since the database being transferred is small at this point and there is a requirement that the database must be available at all times, the DatabaseOnline option is selected. For performance purposes, if the database being transferred is a large database then the DatabaseOffline would be a better choice. Finally, fill in the name of the database that is being transferred.

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!

Once this is completed, click OK.

Testing the Package

There are no other components to configure. Everything is contained in this one component so it is time to test the package. Run the package but please note that the package may take a little longer to run than the previous packages that have been covered in previous articles.

Once the package has finished running, let’s double check to make sure the database was transferred properly. Open up SQL Management Studio and connect to the destination database server. Expand the list of databases to verify that the CreditCard database is there. You can go further and open up the Transaction table to compare the data from the source database.

Try Server Intellect for Windows Server Hosting. Quality and Quantity!

What have we learned?

How to configure a Transfer Database Task.
The different uses for transferring a database.
What the different transfer options and methods are available (Move vs. Copy and Online vs. Offline).

Attachments