Wednesday, January 23, 2013

T-SQL Parameters in an SSIS Execute SQL Task

     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
AS
SELECT
FirstName,
      
MiddleName,
      
LastName

FROM Person.Person

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. 

image
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.
image
     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. 
image
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
SELECT
FirstName,
     
MiddleName,
     
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:
image     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.
image In the C# script body add a message box show method to display the value of the RowCount varaible value:
MessageBox.Show(Dts.Variables["RowCount"].Value.ToString());
     Executing the package will not display a message box as shown below:
image      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.

8 comments:

  1. A very informative post.

    I have a question. Let's say I have a sql script.

    DECLARE P1 int = 123
    DECLARE P2 nvarchar = (select string from table where columnvalue = @P1)
    DECLARE P3 nvarchar = (select string2 from table where column2value = @P2)
    DECLARE P4 nvarchar = (select string3 from table where column3value = @P3)
    DECLARE P5 nvarchar = (select string4 from table where column4value = @P4)
    DECLARE P6 nvarchar = (select string5 from table where column5value = @P5)
    DECLARE P7 nvarchar = (select string6 from table where column6value = @P6)

    INSERT INTO TABLE1 (C1, C2, C3)
    SELECT V1, V2, V3 from Table2 where V1 = @p1 and V2 = @p2

    UPDATE TABLE XYZ
    SET X1 = @p1,
    X2 = @P4,
    X3 = @P6
    WHERE X4 = @P2

    UPDATE TABLE ABC
    SET A1 = @p1,
    A2 = @P4,
    A3 = @P6
    WHERE A4 = @P2

    UPDATE TABLE E123
    SET E1 = @p1,
    E2 = @P4,
    E3 = @P6
    WHERE E4 = @P7

    I turn that into a stored proc to run it in SSIS
    How would I set up the SQLStatement for this and how would I set up the parameters window? 1) I am running multiple update and 2) I am using same parameters twice in no specific order.

    ReplyDelete
  2. GREAT QUESTION and I am very sorry that I did not include some examples. Rather than passing the ordinal position in the Paramater Name column use the parameter name. For example @P1, @P2, etc. Using that rather than indexed position will not require the pain of mapping the values and they will be bound based on the name used in the proc.

    ReplyDelete
  3. Ahh, thanks for the quick help. One last thing, how would I set up the SQLStatement i.e. exec this.proc ? ? ? ????...in this example?

    ReplyDelete
  4. You set it up exactly as you had before, EXEC PROC ?, ?, ?, ? etc. In the parameter mapping
    The order of the parameters need to be in the ordinal position in which they occur in the procedure. For example you have parameters p1-p4 in the procedure that are called in just that order. Create the mapping of your variable p1 first and set the parameter name to @p1, then p2, until all parameters are created mapping back to the position they occur in the procedure. Using the ordinal position or parameter name will work the same. Parameter mapping in the task must be the same as the ordinal position that is called in the stored proc either by using the indexed position or physical position in the parameter mapping using the actual parameter name. Let me know if this helps or if I update the post to include mapping by name.

    ReplyDelete
  5. So in the above example, I am first declaring 7 parameters. then I am using P1 and P2 in the next insert statement. After that, I am using p1, p4, p6, p2 in 1st update statement...p1, p4, p6, p2 in next update and p1, p4, p6, p7 in last update.

    So there are 7 distinct parameters.
    and total # of times they are used = 7+2+4+4+4 = 21
    So my SQLStatement should be EXEC this.storedprc

    in parameter mapping, what should I call P1 to P7 only (# = 7) and use the names (i.e. "@P1") or do I call 21 parameters (Parameter variables would be used more than once...is that even possible)...

    ReplyDelete
  6. ***..So my SQLStatement should be EXEC this.storedprc (HOW MANY QUESTION MARKS SHOULD GO HERE?)...***

    ReplyDelete
  7. Regardless of whether the parameter is mapped by index or parameter name you will only ever have the same number of question marks as parameters. If your stored procs expect seven parameters you will have seven question marks. The mapping does not go by how many times in the body the parameter is used only by the name or the ordinal position. SSIS prepares the statement by extracting the variable values and replacing them with the question marks,
    EXEC proc ?, ?, ?, ? is what we see at execution it is passed to SQL as
    EXEC proc P1(value), P2(value), P3(value), P4(value). You do not have to map each occurence of the parameter ony the value of each variable to where its corresponding parameter exists in the EXECUTE statement.

    ReplyDelete
  8. Ahh, that clears so much confusion for me. thanks! :)

    ReplyDelete