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
- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
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.
Let's create Source file with below data.
Fig 2: Add UpdateRecordCnt Column with Value 0 by using Derived Column Transformation
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
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
How to use Stored Procedure with Output Parameter in OLE DB Command Transformation