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.

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.

Pages: 1 2 3




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |