Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSIS Transfer Error Messages and Master Stored Procedures Tasks


By:   |   Last Updated: 2019-04-12   |   Comments   |   Related Tips: More > Integration Services Development

Problem

One thing that DBAs do is add custom error messages to their database instances as well as additional management with stored procedures to the master database.  In addition, there is a need to keep these items in synch across all of the managed instances. In this tip we will look at how we can transfer custom error messages and custom stored procedures in the master database to other instances using SQL Server Integration Services (SSIS).

Solution

There are various transfer tasks available in SSIS and in this tip I am focusing on the Transfer Error Messages task and the Transfer Master Stored Procedures task to accomplish our goal.

SSIS Transfer Error Messages Task

Often there is a need to add custom error messages to be used for certain circumstances. All error messages are stored in the table sys.messages in the master database. If the message_id >= 50000 then it is a user-defined message and less than 50000 is a system error message which cannot be transferred using the Transfer Error Messages task. Also, user-defined error messages may be available in a number of different languages and the task can be configured to transfer only messages in selected languages.

As a part of deployment or synchronization purpose, we need to synchronize these messages on multiple SQL Server instances. Using the Transfer Error Message task in SSIS we can transfer all user-defined error messages or only specific user-defined error messages to another SQL Server instance.

I have two SQL Server instances. By querying the sys.messages table I can check for any custom error messages.  As we can see below there are none that exist.

How many error messages is available in primary server

Adding Custom Message

To add a custom error message, you can use the system procedure sp_addmessage. Below I added a custom error message to one of my instances.

sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'     [ , [ @lang= ] 'language' ]
     [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]
     [ , [ @replace= ] 'replace' ]
Configure own custom message
Getting custom message using T-SQL

I added a custom error message which is shown in the above query result. Now I need to transfer this custom message to another SQL Server using SSIS.

I created a new SSIS project called Transfer_Error_Msg_Master_Procedure. I dragged a Transfer Error Message Task from the SSIS Toolbox to the Control Flow.

Taking a task Transfer error messages task

Now I need to configure the properties to transfer the SQL Server error messages between two instances of SQL Server. First, we open the Transfer Error Messages Task Editor window and select the source and destination connections.

For the message synchronization, there are 3 options for the property IfObjectExists.

  • FailTask: This task will fail if duplicate error messages exist in the destination server.
  • Overwrite: Error messages will be overwritten if exist in the destination server.
  • SKIP: Repeat messages are skipped while synching in the destination server.

For synching messages, there are two choices for preparing them from the source server. While the selection of TransferAllErrorMessages is True, all available user-defined error messages will be synched, otherwise you can prepare your own customized collection set. To be more specific, the language option is available for filtering error messages.  I configured the properties as follows.

Configure Transfer error message task

All properties are set, now I am going to execute the package.

Execute a package of Transfer error message task

The package executed successfully, now I am going to check the error messages on the destination server and we can see the error message now exists on the destination server.

Getting a custom message in destination server

SSIS Transfer Master Stored Procedures Task

We know the master database is a SQL Server root database. Without the master database we can’t start SQL Server. It stores all of the system level information for SQL Server. For example: logins, endpoints, information about user-defined databases, system configuration details, etc.

In addition, as a part of server administration, we need to maintain user-defined procedures in the master database for the purpose of configuration, server health checks, etc. To keep things in synch on other servers, we need to synch these procedures to other SQL Server instances.

I am going to check for any user defined stored procedures in the master database on my source server.

Getting a user defined procedure list from master database in source server

I have a couple of procedures available on the server and now I need to transfer those procedures to the destination server.

I created a new SSIS project and dragged a Transfer Master Stored Procedures Task to the Control Flow. Now we need to a configured the task.   The property window is very similar to the transfer error message task.

As a part of synchronization, there are three options available for the stored procedures property IfObjectExists.

  • FailTask: This task will fail if duplicate procedures exists in the destination server.
  • Overwrite: Procedures will be overwritten if exists in the destination server.
  • SKIP: Repeating procedures are skipped while synching the destination server.

In addition, you have two options for preparing the collection from the source server before synchronizing those procedures. While enabling the option TransferALLStoredProcedures as a true, all procedures will be synched otherwise you can prepare your own stored procedure collection set.

In the background, the SMO connection manager is enabled in the package for connecting to the SQL management object server which handles the transfer for the master stored procedure task for the source and destination servers.

I configured the task as follows.

Configure of Transfer Master Stored procedure task

Now I am going to execute the package.

Execute Transfer Master Stored procedure Task

The package executed successfully and now I am going to check to make sure the procedures exist on the destination server.

Getting a procedure list in destination server

Finally, we can see the stored procedures have been copied to the destination server from the source server.

Next Steps


Last Updated: 2019-04-12


next webcast button


next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools