Introduction
SQL Server Integration Services (SSIS) is a Business Intelligence tool which can be used by database developers and administrators to perform Extract, Transform & Load (ETL) operations. In my previous article entitled Using Transfer SQL Server Objects Task and Execute SQL Tasks in SSIS I discussed how to use the Transfer SQL Server Objects Task and the Execute SQL Task which are available in SQL Server 2005 Integration Services and later versions. In this article I will examine the use of the Transfer Error Messages Task & Transfer Master Stored Procedures Task which are 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 Master Stored Procedure Task and Transfer Job 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 Error Message Task & Transfer Master Stored Procedure Task. You can add a custom error message for you application by using sp_addmessage the inbuilt stored procedure.
Overview of Transfer Error Message Task
Database Administrators can use the Transfer Error Messages Task to transfer one or more user defined error message between different SQL Server instances. Using this task you can transfer user defined error messages with an identifier that is equal to or greater than 50000. However, you cannot transfer system error messages which have an identifier less than 50000. The error messages are stored in the sys.messages table in the master database. This task can be configured to transfer all user defined error messages or only a specific user defined error message. Another advantage of this task is that it can be used to transfer error messages between different versions of SQL Server.
Overview of Transfer Master Stored Procedures Task
Database Administrators can use the Transfer Master Stored Procedures Task to transfer one or more user-defined stored procedures between master databases on different instances of SQL Server. However, the only condition to transfer a user defined stored procedure from the master database is that, the owner of the stored procedure must be dbo or else the transfer master stored procedure task will fail. This task can be configured to transfer all user defined stored procedures or only a specific user defined stored procedure. However, you cannot copy any of the system stored procedures which are stored in the master database using this task as this may result in issues with the SQL Server functioning. In this article, I will be using this task to transfer the sp_KillDatabaseUsers user defined stored procedure which can be used to kill the connected users on a database. This stored procedure will be transferred between the master databases of SQL Server 2005 and SQL Server 2008.
Example Using Transfer Error Message Task & Transfer Master Stored Procedures Task
In this example we will first use the Transfer Error Messages Task to transfer a user defined error message with the identifier value 77777 from SQL Server 2005 to SQL Server 2008. The error messages are stored in sys.messages table in the master database. Once the Error Message is transferred successfully, we will use Transfer Master Stored Procedures Task to transfer the sp_KillDatabaseUsers user defined stored procedure between master database of SQL Server 2005 and SQL Server 2008. The sp_KillDatabaseUsers user defined stored procedure can be used to kill the connected users on a database.
1. Create a new SQL Server Integration Services Project and rename the default package TransferErrorMessageAndMasterSPTasks.dtsx
2. Double click the TransferErrorMessageAndMasterSPTasks.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 “Transfer Error Messages Task” from the toolbox to the Sequence Container in the Control Flow window and rename it “Transfer Sample Error Messages”.
5. Double click the Transfer Error Messages Task to open up the Transfer Error Messages Task Editor as shown in the snippet below.
