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




Array

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 |