Using Transfer Error Messages and Transfer Master Stored Procedures Tasks in SSIS
10. Connect the Transfer Error Messages Task and Transfer Master Stored Procedures Task together as shown in the snippet below.
11. Finally, execute the package by right clicking the TransferErrorMessageAndMasterSPTasks.dtsx package from the Solution Explorer and select the Execute Package option from the drop down list. Once the package has successfully executed you will see the below screen within the control flow designer.
12. Once the package has executed successfully, you can go ahead and execute the below TSQL code to verify whether the user defined error message and the user defined stored procedure has been successfully transferred from the source SQL Server 2005 instance to the destination SQL Server 2008 instance.
SELECT * FROM sys.messages WHERE message_id = ’77777′
SELECT name, type_desc FROM sys.procedures WHERE NAME = ‘sp_KillDatabaseUsers’
In this article you have seen how to configure and use the Transfer Error Messages Task and Transfer Master Stored Procedures Task which are available in SQL Server 2005 Integration Services and later versions.