While designing a package last week with our consultant, Latha Chathri (twitter), I came across a need to call a stored procedure drom within a Data Flow Task. I have never needed to do this before and while the call is simple there are a few quirks that I found and would like to share with you.
I've created a sample procedure with a single input parameter (@incoming) and a single output parameter (@outgoing).
In the OLEDB Command, I write the execute statement.
I need to go to the Column Mapping tab to map data flow columns to the procedure parameters and I hit the first snag.
While the component does know there are parameters, it makes no assumption as to how you are going to access or organize them, so it complains. I can satisfy this complaint by marking each of the parameters with a ?, just like you do in just about any other SSIS component that takes parameters. Here is the new sql command.
Now, when I click over to the Column Mappings tab, I see this:
I actually mapped the columns, but the cool thing is the component picked up the parameter names from the procedure! I even get a freebie: @RETURN_VALUE. You need to map that to a column in order to pass that down stream. I'm getting ahead of myself, I explain more a little later in the blog. As of this step, you are done. This component will call the procedure and pass in the parameters.
"But wait!", you say. "If I look at the parameters at the top of the blog post, I see an input parameter and an OUTPUT parameter. How do I get the output from the procedure as well as the return value?". Good question. The answer is counter intuitive.
The proc_output is defined in the mapping tab as an INPUT column, but in this case it is actually an output column. I defined it as a derived column of the proper datatype prior to this step in the Data Flow Task.
The proc_output column will contain the value returned by the procedure and I can use that downstream. This goes for the return value as well. The parameter results will overwrite those default values. Ignore the column titles "Input Column' and 'Destination Column' and just think of this in terms of 'local variable' and 'parameter name'. Now, I want to show you one more thing before we go.
The procedure used in this example is rather trivial and many real-world situations call for more complex procedures. There are two ways in which you can call a stored procedure: 'Pass-by-Position' or 'Pass-by-Parameter'.
'Pass-by-Parameter' is my favorite method of calling a stored procedure. You list out the parameter names and pass them values. I could call our test procedure as such:
declare @out int
exec dbo.usp_testoledbcmd @outgoing=@out OUTPUT
Notice I do not include @incoming. If it is defined within procedure with a default, I don't need to pass a parameter. SQL Server will realize I have nothing for it and push in the default defined within the procedure.
If wanted to do 'Pass-by-Position' I would write the statement like this:
declare @out int
exec dbo.usp_testoledbcmd 2,@out OUTPUT
As you can see, in this case I added a 2 in front of the @out OUTPUT. That is because when I 'Pass-by-Position' I can't skip one. SQL Server will map each value in the execute statement to the corresponding variable position. I don't like this at all and never use it (I don't see a need to).
To show you how this looks when you try to mix the two methodologies, I did a simple test.
I originally tried this: @outgoing=?,?. I wanted the value of the output parameter, but didn't want to have to type out the other parameter (actually the real procedure had 15 params and I only cared about 5). Turns out, you can't do that. You either 'Pass-by-Position' or 'Pass-by-Parameter'. Here's the proof:
Lesson of the Day: Inputs can be Outputs and it's all or nothing.