In this installment of the Simple Series, will show how to call a stored procedure from within SSIS.In the specific example, you will see how to use output parameters within the stored procedure call and how to map parameters to SSIS variables so that you can interact with the procedure. It is important to note the ? as they are placeholders for the variables. They go in the order you put them in, so in this case we have two ?, which correspond to position 0 and 1.
The OUTPUT keyword is used by the procedure, since I am looking to get two pieces of information from it. If you were passing parameters INTO the procedure, you would just have a bunch of ?, one for each of the input parameters.
Now that we have the proper syntax we still need to get those two output parameters to SSIS. To do this we need to map the values coming out of the procedure to usable variables within SSIS. The picture below shows how. There are two things to notice, first the highlighted red, which tells SSIS these parameters are coming FROM the procedure and need to be recorded. If you were passing them in, then it would show Input. Second, highlighted in yellow, are the parameter names, which is a bit confusing. For OLEDB, it is actually a 0 based array of the position of each of the ?. The arrows simply show you each variable and how it is mapped. Don't forget the proper datatype.
That's it. When run the Execute SQL Task the output of the stored procedure in the above example will put the output into two variables which may be accessed by any part of the package.