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




Array

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 |