This is the fourth article in the series of Migrating SQL Server Objects using SSDT 2017. In the third article, we learned how we could transfer SQL logins between the servers. In this article, we will learn how to copy error messages to another instance using the Transfer Error Messages Task of SSDT 2017.

Demo Setup

For demonstration, I have created three user-defined error messages on Nisarg-PC\SQL01. The script to create them is following:

You can view the error messages by executing the following query:

Now let us create a package with the Transfer error messages task.

Create SSIS Package

First, let us create a new SSIS project. Launch SQL Server Data tools. (SSDT 2017) 🡪 Crete New Project 🡪 Select Integration service project. The name of the project is Transfer User Defined Error Messages.

To transfer the error messages, we are using the Transfer Error messages Task. Drag Transfer Error messages task from SSIS toolbox and drop it on Control Flow window.

Now, let us configure it.

Configure Transfer Error Messages Task

To configure, double-click on Transfer Error Messages Task. A dialog box named Transfer Error Messages Task editor opens. Click on Messages. You can see the following configuration.

1. Source and Destination connections
2. Messages

Let us configure the source and destination connection.

Configure Source and Destination Connection

To configure the source connection, click on SourceConnection and select New Connection.

In the SMO connection manager editor, specify the Nisarg-PC\SQL01 in the Server name and select windows authentication to log on to the server.

Click OK to save the connection and close the dialog box. To configure the destination connection, click on DestinationConnection and select New Connection.

Similarly, for destination connection, specify the Nisarg-PC\SQL02 in the Server name and select windows authentication to log on to the server.

The Connection section looks like the following image:

Select error messages

Under the Messages section, there are four options.

1. IfObjectExists: You choose the action when the error messages already exist on the destination server. You can select any of the following:
   1. FailTask: If the message exists, the task execution will be terminated; report the status of the task as failed
   2. Overwrite: If the message exists, the task will overwrite the definition of the error message
   3. Skip: If the message exists, the task will ignore those messages
2. TransferAllErrorMessages: If you want to transfer all error messages, select True. If you want to transfer specific messages, then select False
3. ErrorMessageList: When we select True in TransferAllErrorMessages drop-down box, this option enables. To select the specific messages, click on ErrorMessageList. A list will be open in a dialog box. You can select the messages that you want to transfer
4. ErrorMessageLanguageList: If the error messages are in a different language, you must choose the language of the message

In our case, we are transferring all error messages, so select True from TransferAllErrorMessages drop-down box. If the message exists on the destination server, the task will overwrite them, so I have selected Overwrite in IfObjectExists drop-down box.

Click OK to save the configuration and close the dialog box.

Let us configure the Notify Operator task.

Configure Notify Operator Task

First, drag the Notify operator task from the SSIS toolbox, drop it in the Control flow surface, rename it to Send Notification, and double-click on it to configure.

A dialog box named opens. You must specify the values that are used to send notifications.

First, we must configure the connection between the server on which the SQL Server operator has been created. To do that, click on the New in Notify Operator task dialog box. Another dialog box named Connection Properties opens. Specify the appropriate values of the following parameters.

1. Connection Name: Specify the desired connection name. In our demo, I have named it as SQLConnection
2. Server Name: Specify the hostname on which the operator has been created. I have created an operator named DBASupport on Nisarg-PC\SQL01
3. Logon Information: Specify the authentication method that is used to connect to the SQL Server. In this demo, we are using windows authentication

Click OK to close the dialog box.

Close the dialog box to save the connection properties. As you can see, the operator named DBASupport is populated in Operators to notify list box.

Specify the email subject in the Notification message subject textbox. In our demo, the subject line is the Status of Migration of User-defined error messages.

Specify the email body in the notification message body. In our demo, the email body is following:

Hello DBASupport.,
The User-defined messages have been transferred successfully.

Finally, the Notify operator task looks like the following image:

The email must be sent after error messages are transferred successfully. So, we must connect both tasks using a connector.

The Transfer Error Messages Task has been configured successfully.

Summary

In this article of this series, we learned about the Transfer Error Messages Task of SSDT 2017. I have explained how we can configure it to transfer user-defined error messages between Nisarg-PC\SQL01 and Nisarg-PC\SQL02. In the next article, we will deploy the SSIS packages using SSDT 2017. We will also learn how to automate the SSIS packages’ execution using SQL Server Agent Job.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
• Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022