Transfer master stored procedures task in SSIS

Hello friends, today we are going to learn about “Transfer master stored procedures task”. As the name itself talks about its behavior, It transfer user stored procedures created in “Master” database from one SQL Server instance to other SQL Server instance. Lets create a package using “Transfer master stored procedures task” and have a hands on experience.

First of all we will take “Transfer master stored procedures task” in to control flow region(Shown below).

To configure the configuration of “Transfer master stored procedures task”, we will right click on it and click on “Edit” menu that will bring the “Transfer master stored procedures task” editor screen in front of us(As shown in the below screenshot).

In “Transfer master stored procedures task”, we have to specify below configuration settings.

Source connection: We will provide the source(SQL Server instance), from where we want to transfer the stored procedures.

Destination connection: We will provide the destination(SQL Server instance), where stored procedure will be transferred.

IfObjectsExits: There are three option available for this property which are following.

1) Fail task: if we specify “Fail task”, if the stored procedure which is being transferred from the source to destination server is already existing on the destination server, task will be failed.

2)Overwrite: if we specify “Overwrite”, if the stored procedure which is being transferred from the source to destination server is already existing on the destination server, task will overwrite the stored procedure at destination server.

3)Skip: if we specify “Skip”, if the stored procedure which is being transferred from the source to destination server is already existing on the destination server, task will skip that stored procedure and will not be transferred.

TransferAllStoredProcedures: If we set this property “True”, all user defined stored procedures from the master database will be transferred.

StoredProceduresList: We can specify the list of stored procedures which will be transferred.

I am going to specify the list of stored procedures which will be transferred from source to destination server, hence I will click on the button(Encircled by red color) as shown in the below screenshot.

Once I click on the button, a new pop window with all user defined stored procedures(existing in master database) will appear in front of us(As shown in the below screenshot).

Since we have only one user defined procedure in Master database at source server, we are only seeing one stored procedure in the list, we will click on “OK” button to save the settings. Now we are back to the “Transfer master stored procedures task” editor screen, we will click on the “OK” button to save the settings.

Now are done with our package, lets execute it by pressing the “F5” key and see the results.

Green color of task is indicating that our task ran successfully, lets check the master database at destination server and see if the stored procedure has been transferred or not.

We can see in the above screenshot that stored procedure has created under master database node.I hope you will find this article useful, please feel free to ask any question related to this article.I will be back with one more article on SSIS, till then take care.