SSIS provides several tasks that directly pass T-SQL queries directly to SQL Server. There are numerous occasions in an SSIS package where when input needs to be passed into a T-SQL statement prior to the execution. Several examples of this are creating dynamic statements and passing parameters to a stored procedure. I covered creating dynamic statements using variables in SSIS in this post, so this post will focus on a bit more simplistic approach of passing parameters to execute SQL tasks.
In order to prepare for the SSIS package pre-create the stored procedure that will be called in the AdventureWorks2012 database, the below code can be used:
CREATE PROC usp_GetName @BusinessEntityID INT
WHERE BusinessEntityID = @BusinessEntityID;
Create an SSIS package called T-SQLParameters and a create a package scoped variable called BusinessEntitytyID, leave the default data type of integer, but set the value to 10.
Drag and drop an execute SQL task onto the control flow pane and rename it Retrieve Person Name.
Configure the task’s SQLStatements property to pass the command EXEC usp_GetName ? and create a connection manager to an instance of SQL that has the sample database AdventureWorks2012 that has the stored procedure usp_GetName already created.
The T-SQL statement is obviously is not syntactically correct based on T-SQL standards, the whole “?” thing should be a clue. The “?” is a place holder for parameters in a statement, both input and output parameters are supported, in either execute SQL or data flow tasks. The statement in the task is configured to execute a parameter in SSIS, but the task now needs to be configured to recognize the parameter being passed. In the Parameter Mapping menu configure the Variable Name as BusinessEntityID, Direction as Input, Data Type as Long, and the Parameter Name as @BusinessEntityID.
The Parameter Name can either be the ordinal position in which the parameter appears, using a zero based index, in the task, input or output, or by the parameter name. In this case there is only one parameter so the name could be replaced with “0”.
Executing the package now will successfully execute the stored procedure usp_GetName passing the BusinessEntityID of 10 based on the valued of the associated parameter. In order to make things more interesting let’s add an output parameter to the usp_GetName stored procedure and reconfigure the execute SQL task. Alter the stored procedure using the below DDL statement:
ALTER PROC usp_GetName @BusinessEntityID INT, @Rows INT OUTPUT AS
LastName FROM Person.Person WHERE BusinessEntityID = @BusinessEntityID SELECT @Rows = @@ROWCOUNT;
Add a variable to the package called RowCount and leave the defaults, this will be used to capture the output parameter RowCount fo the stored procedure. The Retrieve Person Name execute SQL task needs to be modified to successfully execute. Modify the SQLStatement property to now show:
DECLARE @RowCount INT
EXEC usp_GetName ?, @RowCount = @RowCount OUTPUT
SELECT ? = @RowCount
The Parameter Mapping needs to be changed to take into account the OUPUT parameter so modify this by adding the RowCount variable and setting its direction as OUPUT and ordinal position to 1 in the Parameter Name property:
In order to verify that the output value of the query is being properly assigned drag and drop a script task onto the control flow pane and configure it to accept the RowCount as a ReadOnlyVariable.
In the C# script body add a message box show method to display the value of the RowCount varaible value:
Executing the package will not display a message box as shown below:
This sample focused on an execute SQL task, but a similar technique can be used for a data flow task source using a variable. This technique is outlined on my post SSIS - Replace Dynamic SQL With Variables.
The sample package for this post can be downloaded from my site here.