This SSIS Integration Services article shows how to call a stored procedure with parameters using an OLE DB Command transformation with an example.
The table below shows the Costing table; we use these table columns as the parameter values.
Table to apply the stored procedure, and it has 395 rows.
The image below shows the Deleted stored procedure we created for this demo. It is a simple one that accepts two parameters and deletes table records whose color equals @Color and whose List price is less than the @ListPrice parameter.
The query we used is
USE [Task Results] GO CREATE PROCEDURE [dbo].[spDelteRecords] @Color NVARCHAR(15), @ListPrice Money AS BEGIN SET NOCOUNT ON; DELETE FROM [OLE DB Command Insert] WHERE Color = @Color OR ListPrice < @ListPrice END EXECUTE [dbo].[spDelteRecords] ?, ?
SSIS OLE DB Command calls Stored Procedure with Parameters
Drag and drop the Data Flow Task into the control flow region.
Double-click to open the SSIS Data Flow Region. Then, drag the OLE DB Source and double-click on it to open the Editor. Next, click the New button to configure the OLE DB Connection Manager. Here, we have chosen the existing one.
In this example, we have to choose the existing Costing table from the list.
Next, go to the columns tab to check the Color and ListPrice columns because we want these columns’ values as the Parameters, and click the Ok button.
Add OLE DB Command Transformation and connect to the Source. Double-click on it will open the Advanced Editor. Under the Connection Manager tab, choose the existing OLE DB Connection.
Write the following SQL Command to execute or run the stored procedure. Here ? represent the Parameter value.
Check the command.
Under the Column Mappings, Map the input and Destination Columns. Here, Input Columns come from the Costing table, and Destination means Parameters.
Run the SSIS OLE DB Command calls Stored Procedure with Parameters package.
Within the Management Studio, if you look at the table, there are no white or black products with a list price of less than 16. Therefore, the total number of records becomes 251, where the original records are 395.