Transferring a Database Using the SSIS Designer

(Page 1 of 7 )

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.

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.

Next: The Source and Destination Servers and Databases >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: Transferring a Database Using the SSIS Designer

· SSIS is well integrated with the Visual Studio 2005. In SQL 2000 server it was a...    · Good tutorial. However, what do you do when you get the error below because one or...   >>> Post your comment now!