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.
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 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.
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.
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.
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:
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."
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.
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:
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.
MS SQL Server 
/ 12 
SQL 2000 Data Transfer is a Snap with SQL 2005
