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:
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.