Transferring a Database Using the SSIS Designer

If you want to learn how to transfer a database from one SQL Server to another, this article can help. It shows you how to do this by using the SSIS Designer.

Contributed by
Rating: 4 stars4 stars4 stars4 stars4 stars / 12
October 17, 2006
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

Introduction

SQL Server Integration Services, SSIS for short, is an enhancement to the Data Transformation Services used with SQL 2000 Server and SQL Server 7.0. This is the tool that should be used for ETL (Extraction, Transformation, and Loading) operations. Significant changes include the enhancements to the graphical designer and its integration with the Visual Studio by the very aptly called Business Intelligence Projects. The graphical designer -- the SSIS designer -- is a part of the Business Intelligence Projects which provides the designer canvas for the projects.

This tutorial shows you step by step how you may transfer a database from one SQL Server to another using the SSIS Designer. In this particular exercise a database on the SQL 2005 Enterprise Server will be transferred to the SQL 2005 SQL Express Server. The step by step process, shown with ample screen shots, should make this very easy to understand and repeat. An understanding of DTS is quite relevant; readers should review the large number of DTS related articles on the ASPFree.com site.

About the SSIS Designer

The designer consists of three tabbed pages for configuring Control Flow, Data Flow, and Event handlers. The SSIS toolbox contains the various items needed for Control Flow as well as tasks needed for maintenance plans. The number of tasks in SSIS is much larger than what one finds in the DTS. In addition to the the tabs, an additional tab pops up during execution to show the progress. A Package Explorer tab displays the contents of the package and the Execution Results tab shows the results after execution. Since databases are at the core of the SSIS, a control tray of Connection Managers will display all the used connections and available connections for the package.

The Source and Destination Servers and Databases

The next picture shows the source and destination servers used in this tutorial. The source server is the SQL 2005 Enterprise Server Hodentek/MYSORIAN. The destination server is the HodentekSQL Express Server. The database, named TestBase on the SQL 2005 Server, will be transferred to the SQL Express Server using the Transfer Database Task in Visual Studio 2000. The TestBase database does not exist at this point in the SQL Express Server. The TestBase database in SQL 2005 Server has system tables as well as two user tables, Argentina and TestValid, as shown.

The next picture shows some of the general details of this database.

The SQL 2005 Server folders on this computer are arranged as shown in the next picture. The MSSQL.1 and MSSQL.4 folders refer to the SQL 2005 Server and the SQL Express Server respectively. The MSSQL.2 and MSSQL.3 folders belong to the OLAP and Reporting Services. The folder location for all these is: C:Program Files Microsoft SQL Server. We may have to use this information in this tutorial.

.  

Creating a Business Intelligence Project

From the VS Studio's File-->New Project the New Project Window may be spawned as shown.

From the Visual Studio Installed Templates on the right, clicking on the Integration Services icon will provide the default integration project with the default name Integration Services Projects 1 as shown.

In this tutorial the name TransferDB has been chosen. This adds a folder structure for the project as shown.

Creating a New Package

It is easy to create a new package. Right click the SSIS Packages folder and click on New SSIS Package as shown. The folder on the machine C:Documents and SettingsJay My DocumentsVisual Studio 2005ProjectsTransferDBTransferDBPackage1.dtsx stores the package. Right click Package1.dtsx in Solution Explorer and change it to DBTrans.dtsx. Make sure you do not change the file type extension.

Adding a Transfer DB Task to the SSIS Designer

From the Control Flow Items in the Toolbox, click and draw the Transfer Data Base Task as shown in the next picture.  If the execution leads to a success this turns blue; if it fails, it turns red. You may also double click this control in the toolbox so that it gets onto the Control Flow tabbed page of the SSIS Designer as shown. The small red circle with a cross implies it still needs some configuration.

Configuring the TransferDB task

Right click the Transfer Database Task to pop up a drop-down list as shown. From this list click on the shortcut ellipsis labeled Edit.

This brings up the Transfer Database Task Editor window. The navigational items on the left are General, Databases, and Expressions. While you are in the General related page, change the name of the task to TwoO5toExpress and for description you may add something suitable such as Copy TestBase to Express.

Click on Databases on the left to reveal the database-related items that need to be configured for this task (on the right) as shown. This consists of Connections, Destination Database and the Source Databases.

Configuring Connections

Click on an empty area in the SourceConnection textbox, which gives an arrow handle for the drop-down as shown.

Click on the New connection... ellipsis to pop open the SMO Connection Manager Editor. In the dropdown of the server name click on HodentekMYSORIAN and Use SQL Server Authentication with a User name and Password. You may also test this connection. Click on the OK button.

Similar to the above procedure,  add the destination connection which is the SQL Express Server connection. When the connections are added, the Transfer Database Task Editor should have the following details configured:

Configuring the Source Database

The Action that will be taken is to Copy a database. It can be changed to Move if needed. The default method is DatabaseOffline. Change this to DatabaseOnline. The source database name is TestBase.

Once the connection to the server is established, the handle which will appear when you click on an empty area by the side of the SourceDatabaseName should allow you browse and pick up the database you want to transfer as shown. Since it is online, the source file area gets filled with the location of the source files and is grayed out.

SSIS Designer Behaving Badly

As you might have observed, by configuring the source, the destination gets automatically configured. Let's assume that this is okay (this needs to be assumed because this did not work as implemented). We do not have a destination database as yet, so there is no question of overwriting a file. We may take the default option. This completes the configuration of the task.

Proceed and Execute

Let's execute this package by right clicking the package and clicking on the Execute shortcut in the drop-down. The Transfer Database Task control turns yellow and the Progress tab shows up. When you click on the Progress tab it shows your progress as shown in the next picture. Finally the package failed to execute.

When you look at the output window, which shows the output from the debug process, you will see the following detail. It's not a very helpful message, I'm sure you'll agree. It looks like the SSIS Package finished successfully, but the task failed. This reminds me of the old joke about how "the operation was a success, but the patient died."

Troubleshooting

After encountering the same error message, attempt was made to verify whether the IDE did what it is supposed to do. The first thing checked was to verify if any permission was violated. Finally it was decided to look at the Database files for the source and the destination.

Source

"TestBase.mdf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA",""; "TestBase_log.ldf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA",""
Destination

"TestBase.mdf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA",""; "TestBase_log.ldf","C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
DATA",""

Do you see the problem with this automated wizard? It is trying to copy from SQL 2005 to itself, although it was explicitly told to copy it to the SQL Express. To remedy the situation, change the destination file string to the following:

"TestBase.mdf","C:Program FilesMicrosoft SQL ServerMSSQL.4MSSQL
DATA",""; "TestBase_log.ldf","C:Program FilesMicrosoft SQL ServerMSSQL.4MSSQL DATA",""

Now click OK to the Editor screen and execute the package one more time. The progress now shows the following displayed.

The output of the debug window shows that  the package exited with code 0(0x0), a success.

Final check on SQL Express

If you now go back to SQL Express Server and expand the databases node, you should find the TestBase database. This is indeed true as shown here. The TestBase database has been copied over to the SQL Express Server.

Summary

The package design using the SSIS Designer for transferring a database is very easy. The automatic configuring of the destination created a problem which was rectified by associating the proper destination file with the proper server. It was interesting to note that it took over 12 seconds to transfer a near empty database (total data + log = 5 MB) with just two user tables with both servers on the same machine. The error message produced when the task failed was not very helpful.

blog comments powered by Disqus
MS SQL SERVER ARTICLES

- Windows Azure Media Services Launched by Mic...
- Windows Server 8 Cloud Backup Beta Released
- Idera Announces SQL Compliance Manager 3.6
- Idera SQL Doctor 3.0 and MS SQL Changes
- Microsoft Cuts Windows Azure Compute and Sto...
- Express5800 to Mesh with SQL Server 2012
- Microsoft Azure Outage
- Windows Azure Server Supported by RealCloud ...
- Idera Releases SQL Diagnostic Manager v7.1
- MS SQL Sever 2012 Launch, New Idera Release
- OpenText Azure Cloud Solution, Geminaire Raa...
- Melissa Data Releases MatchUp Tool for SQL S...
- Glovia`s G2 ERP Solution to Support SQL Serv...
- Upgrade Assistant for SQL Server 2012 Releas...
- Azure Update Features Several New Improvemen...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 7 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials