How to use Stored Procedure with Output Parameter in OLE DB Command Transformation in SSIS Package - SSIS Tutorial

When it comes to create an SSIS Package for Insert/Update, we have different solutions, We can store the data from source if not Database table to some staging table and then use TSQL Statements ( Merge Statement). The other way if we don't want to use the staging table to load the data first, we can use Data Flow Task, Inside Data Flow Task we can use Lookup against our destination to find out if Record Need to Insert or Update. For Insert we can use OLE DB Destination and to update the record in table we can use OLE DB Command Transformation. OLE DB Command Transformation executes the SQL Statement for each of the record that make it slow transformation but it will work just fine for small number or records.

We often want to know how many records got updated and save the count in some audit table for our reference. Today we are going to use the Stored Procedure in OLE DB Command Transformation that is going to return use Number of Records update by each record.

To keep the things simple, I am not go use very few columns for Table and Source File. you can have an many as you want.

use Test
go
IF OBJECT_ID('dbo.Customer', 'U') IS NOT NULL
    DROP Table dbo.Customer 
GO
Create Table dbo.Customer
(CustomerID INT, 
CustomerNAME VARCHAR(100))

Insert into dbo.Customer 
values (1,'AAMIR'),(1,'AAMIR'),
(2,'Raza'),(3,'Robert')

--Create a Procedure to Update the CustomerName
--Depending upon CustomerID

IF OBJECT_ID('dbo.UpdateCustomer', 'P') IS NOT NULL
    DROP PROCEDURE dbo.UpdateCustomer
GO
CREATE PROCEDURE dbo.UpdateCustomer
@CustomerID INT,
@CustomerName VARCHAR(100),
@RecordCnt INT OUTPUT
AS  

   update dbo.Customer 
   set CustomerName=@CustomerName
   where CustomerId=@CustomerID
--set the Output Variable value with Number of records updated
   SET @RecordCnt=@@ROWCOUNT
RETURN
GO

Let's create Source file with below data.

CustomerID,CustomerName

1,Aamir Shahzad

2,M Raza

3,Robert Ladson

Step 1:

Create new SSIS Package, Bring Data Flow Task to Control Flow Pane and then Drag Flat File Source as we are reading data from Flat File. Create Connection to Flat file that has above data.

Step 2: 

As OLE DB Command Transformation does not let us add Column so we have to prepare a column in which we can save the update record count by each row. Bring Derived Column Transformation and add a new column as shown below.

Fig 2: Add UpdateRecordCnt Column with Value 0 by using Derived Column Transformation

Step 3:

Now bring OLE DB Command Transformation. Create OLE DB Connection to a database in which your Stored Procedure it and then configure as shown below.

Fig 3: Set the Connection manager in OLE DB Command Transformation

Now go to Component Properties Tab and then provide the SQLCommand as shown below.

Exec dbo.InsertCustomer ?,?,? output

Each ? will be mapped to the input columns, for output parameter with ? we have to put "output" as you can see above

Fig 4: Call Stored Procedure in OLE DB Command with Output Parameter in SSIS Package

Next go to Column Mapping and map the columns as shown below.

Fig 5: Map the input columns to Stored Procedure Parameters in OLE DB Command Transformation

Step 4:

Add Multicast Transformation to the OLE DB Command Transformation and Add Data Viewer to see the data.In real time you might write these records to Audit Table or some file.

Fig 6: See the Update Record Count for each of the input Row

Video Demo: 

How to use Stored Procedure with Output Parameter in OLE DB Command Transformation