Output Parameter of Stored Procedure In OLE DB Command – SSIS

I encounter a question today which was about using a Stored procedure inside a Data flow task, and use result of stored procedure which returned by output parameter beside other columns in Data Stream.

Solution with sample:

Suppose your stored procedure has an input parameter and output parameter. I show you a simple stored procedure :


    @input as int,

    @output as datetime output




    set @output=DATEADD(Day,@input,getdate())



I know that this is simple DateAdd function, and I can use a Derived Column Transformation to implement it, and typically there is no need to run a stored procedure for this. But note that this is only a SAMPLE for showing how to work with output parameters of stored procedure.

and suppose you have an OLE DB Source to fetch data from a table in data base and this data needs to be accompany with output parameter of this stored procedure.

the source table structure is :

CREATE TABLE [dbo].[OLEDBCommandInputTable](

    [DaysCount] [int] NOT NULL


and the data in the source table is:

now I want to perform the stored procedure above for each record in the source table, I mean I want to set DaysCount column as input parameter of stored procedure and get result beside this,and fill a destination with combination of DaysCount and output of stored procedure.

1- So, first of all I have an OLEDB Source to fetch data from [OLEDBCommandInputTable] table.

2- in the next step I should set a DUMMY field as DT_DBTIMESTAMP type .

now a big question appears : why this field is needed?!!!

answer is :

in OLE DB Command you can not add output columns, so how you should set output parameters of stored procedure to a column in data stream?

simply you should set all mappings of parameters of stored procedures in "Column Mappings" tab of OLE DB Command Editor, both input and output parameter.

This all simply means that you should map every output parameter in stored procedure to an INPUT Column. so you need a DUMMY input column to fill that column here with output parameter.

Let’s go back to the Data Flow design:

Add a Derived Column Transformation right after OLEDB Source, and write this expression there:


and set "Derived Column Name" as "NewDateValue" .

this is the Dummy field which I explained above.

3- in this step, We need to run our stored procedure with OLE DB Command,

add an OLE DB Command, set connection .

and in the "Component Properties" tab, set SqlCommand with this :

exec SpInputOutput ? , ? output

then go to "Column Mappings" tab,

map columns as this screen shot shows:

4- add Destination to fill results in destination.

I usually use RecordSet Destination in my samples to just show up results with Data Viewers, but you can use any destination type you want.

and At last here is the result with the schema of Data Flow:

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply