By: Bhavesh Patel | Comments | Related: 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.
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' ]
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.
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.
All properties are set, now I am going to execute the package.
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.
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.
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.
Now I am going to execute the package.
The package executed successfully and now I am going to check to make sure the procedures exist on the destination server.
Finally, we can see the stored procedures have been copied to the destination server from the source server.
Next Steps
- Kindly run in test server before rolling-out in production.
- Check out the SQL Server Integration Services (SSIS) Tutorial.
- Check out Transfer SQL Server object tasks.
- Check out Transfer Database Task and Transfer SQL Server Objects Task in SSIS.
About the author
Bhavesh Patel is a SQL Server database professional with 10+ years of experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
