SSIS OLE DB Command calls Stored Procedure with Parameters

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.

Costing Table

Table to apply the stored procedure, and it has 395 rows.

Table to Apply

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.

Stored Procedures

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.

Data Flow Task

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.

SSIS OLE DB Command calls Stored Procedure with Parameters

In this example, we must choose the existing Costing table from the list.

Choose Existing Table

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.

Select Required Columns

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.

SSIS OLE DB Command calls Stored Procedure with Parameters

Write the following SQL Command to execute or run the stored procedure. Here ? represent the Parameter value.

Execute SP

Check the command.

SSIS OLE DB Command calls Stored Procedure with Parameters

Under the Column Mappings, Map the input and Destination Columns. Input Columns come from the Costing table, and Destination means Parameters.

Parameters Mapping

Run the SSIS OLE DB Command calls Stored Procedure with Parameters package.

Run SSIS OLE DB Command calls Stored Procedure with Parameters

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.

Result