Using Transfer Error Messages and Transfer Master Stored Procedures Tasks in SSIS

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.

Continues…

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |