Using Transfer SQL Server Objects Task and Execute SQL Tasks in SSIS
SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers or administrators to perform Extract, Transform & Load (ETL) operations. In my previous article entitled Transfer Logins Task and Transfer Database Task in SSIS I discussed how to use the Transfer Logins Task and Transfer Database Task which are available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of Transfer SQL Server Objects which is available in SQL Server 2005 and later versions.
Overview of SQL Server Tasks in SSIS 2005 & Later Versions
The SQL Server Tasks within SSIS can be used to copy, modify and delete SQL Server objects and data. The different types of SQL Server Tasks available in SSIS are Execute SQL Task, Bulk Insert Task, Transfer Database Task, Transfer Login Task, Transfer SQL Server Objects Task, Transfer Error Message Task, Transfer Job Task and Transfer Master Stored Procedure Task. If you are new to SQL Server Integration Services then I would recommend you to start with my previous article entitled SQL Server 2008 Integration Services Tasks to understand different types of Integration Services Tasks which are available in SQL Server 2005 and later versions. However, in this article you will see an example for how to configure and use Transfer SQL Server Objects Task.
Overview of the Transfer SQL Server Objects Task
Database Administrators can use the Transfer SQL Server Objects Task to transfer different types of objects between SQL Server instances. The objects which can be transferred using the Transfer SQL Server Object Task are Tables, Views, Stored Procedures, User Defined Functions, Defaults, and User Defined Data Types. Depending up on the source SQL Server Version there are different types of objects available to copy. Other than database objects, this task also provides for copying database users, database roles, SQL Server Logins and Object Level Permission.
Overview of the Execute SQL Task
The Execute SQL Task can be used to execute any TSQL code such as truncating a staging table, executing queries, stored procedures etc from a SSIS Package. This task can also be used in combination with For Loop and Foreach Loop containers to run multiple TSQL statements.
Example Using Transfer SQL Server Objects Task
In this example we will first use the Execute SQL Task to create a new user database named Products on a SQL Server 2008 Instance. Once the database is successfully then, by using Transfer SQL Server Objects Task, we will transfer the database objects such asTables, Views, and Stored Procedures etc from a SQL Server 2005 to a SQL Server 2008 instance. While copying user tables you can also specify whether to copy Indexes, Triggers, Full-text indexes, Primary keys, foreign keys, Referential Integrity etc.
1. Create a new SQL Server Integration Services Project and rename the default package TransferSQLServerObjectsTask.dtsx
2. Double click the TransferSQLServerObjectsTask.dtsx package to open it in Design mode.
3. Drag and drop a Sequence Container from the toolbox to the Control Flow window.
4. Drag and drop “Execute SQL Task” from the toolbox to the Sequence Container in the Control Flow window and rename it “Create Products Database”.
5. Double click the Execute SQL Task to open up the Execute SQL Task Editor as shown in the snippet below.
In the Execute SQL Task Editor, you need to configure a connection to a SQL Server, for the “SQLSourceType”Property choose “Direct Input” from the drop down list. Next, copy the below TSQL code and paste it into the SQLStatement textbox. Finally click OK to save changes in the Execute SQL Task Editor.
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’Products’)
DROP DATABASE [Products]
CREATE DATABASE [Products] ON PRIMARY
( NAME = N’Products_Data’,
FILENAME = N’D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAProducts.mdf’ ,
SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5% )
( NAME = N’Products_Log’,
FILENAME = N’D:Program FilesMicrosoft SQL ServerMSSQL10.SQL2008MSSQLDATAProducts_Log.ldf’ ,
SIZE = 3MB , MAXSIZE = UNLIMITED , FILEGROWTH = 5% )
6. Drag and drop “Transfer SQL Server Objects Task” from the toolbox to the Sequence Container in the Control Flow window.