This article will show you how to Transfer SQL Server Stored Procedures in SSIS. For this, we will create an SSIS package to transfer Stored Procedures from one Database to another using the Transfer SQL Server Objects task.
Please refer Transfer Server Objects task to understand the functionality of every SSIS property.
Transfer Stored Procedures Using Transfer SQL Server Objects Task in SSIS
If you observe the below screenshot, there is one Database called [Duplicate AdventureWorks2014]. We have already Transferred the tables in the Tables with Data article.
Using the SSIS Transfer SQL Server Objects task, we will transfer the stored procedures from [AdventureWorks2014] to [Duplicate AdventureWorks2014].
If you want to Transfer User Defined Functions, then please refer to the User Defined Functions article.
STEP 1: Open BIDS and Drag and drop the Transfer SQL Server Objects Task from the toolbox to control flow.
Double click on the Transfer Objects Task will open the Editor to configure it.
General Tab
In this tab, enter the Name and description
STEP 2: Select the SourceConnection property and click on it to create a New connection. If you have already created it, then select it.
Once you click on <New connection…>, an SMO Connection Manager Editor window will open to configure the connection.
STEP 3: For this example, we select our localhost instance and use Windows Authentication. If you are working for an organization, select SQL Server Authentication, and enter the credentials.
Click on the Test connection button to check whether the connection is throwing errors or not.
STEP 4: Click on the SourceDatabase option and select the database. For the Transfer SQL Server Stored Procedures in the SSIS demo, we chose the [Adventureworks2014].
STEP 5: Select the DestinationConnection property and click on it to create a New connection. If you have already created it, then select it.
We have already mentioned the localhost instance in SourceConnection, and we also use the same instance here. If you want to create a new one, click on it will open the SMO Connection Manager Editor window to configure the connection.
STEP 6: Click the DestinationDatabase option and select the database you want to use. For Transfer SQL Server Stored Procedures in the SSIS example, we choose [Duplicate AdventureWorks2014].
STEP 7: If you require to copy all the objects (views, function, stored procedures, tables) from the source database, then set the CopyAllObjects option to True. In this example, We are going to send Stored Procedures only. So we leave it to default False.
If we set the CopyAllObjects option to False, the following property ObjectsToCopy will get enabled.
STEP 8: Click on the ObjectsToCopy property to configure it. ObjectsToCopy property will explore many options of the Transfer SQL Server Objects Task. Let us concentrate on options that belong to or are related to this example. In this SSIS objects Task example, Our requirement is transferring the SQL Server Stored Procedures. So we have to understand options such as CopyAllStoredProcedures and StoredProceduresList.
If you require copying all the Stored Procedures from the source database, set the CopyAllStoredProcedures option to True. However, we are sending all the Stored Procedures. To show you, We leave it to default False, and later we will select them in the StoredProceduresList.
STEP 9: Click on the StoredProceduresList option and click on the collections. Once you click on the (…) button beside Collections, it will open the Select Stored Procedures window to select the available ones from the source connection.
We are selecting all of them for this example, but you can try different options.
STEP 10: Click ok to close the Select Stored Procedures window, then click ok to finish configuring the Transfer SQL Server Objects Task.
Let us run the package to see whether we successfully transferred them from source to destination.
Let’s open the Management Studio and check for the Stored Procedures.
From the above screenshot, you can observe that we successfully transferred the Stored Procedures.
