Output Parameter of Stored Procedure In OLE DB Command – SSIS

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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 :


CREATE PROCEDURE dbo.SpInputOutput

    @input as int,

    @output as datetime output

AS

BEGIN

    SET NOCOUNT ON;


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

END

GO


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

) ON [PRIMARY]


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:

NULL(DT_DBTIMESTAMP)

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:

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply