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.
Use master
GO
SELECT
* FROM sys.messages WHERE
message_id = ‘77777’
GO
SELECT
name, type_desc FROM
sys.procedures WHERE
NAME = ‘sp_KillDatabaseUsers’
GO
Conclusion
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.
]]>