none
Question regarding SSIS Execute SQL Task question for reading from OUTPUT parameter using variables

    Question

  •  

    Hi,

    I am trying to understand the steps involved in executing a stored procedure from Execute SQL Task.

    Method 1
    Step 1: Dynamically construct the sql and assign it to a string variable.
    @[User::mysql] = "exec [dbo].[spAdd2NumbersTest] " +  (DT_WSTR, 10) @[User::Num1] + " , " +   (DT_WSTR, 10) @[User::Num2]

    Step 2: In the Execute SQL Task editor set the following properties
    a. SourceType = Variable
    b. SoruceVariable = @[User::mysql]

    Question.
    1. How & where do I dynamically set the value of variable @[User::mysql]?
    2. Lets say the stored proc returns the sum of two numbers. How do I capture the return value from the stored proc?
    3. If I want to use an OUT variable in the argument list of the stored procedure then how do I construct the value of @[User::mysql] such that the stored proc populates an output variable of type @[User::mysum]?


    Method 2
    I think the practice for executing INSERT, UPDATE, DELETE type procedures is to dynamically set the property of SqlSatementSource using the Expression option in Execute SQL Task Editor. This way we don't need the variable @[User::mysql] and the SourceType = Direct Input. This method is fine and I guess easiest of all. But I wonder how to solve the problem reading the value/s returned from the stored procedure. Also How to I dynamically set the values for variables @[User::Num1] & @[User::Num2]?

    Method 3
    I could set the SourceType = Direct Input & SQLStatement = dbo.spAdd2NumbersTest ?, ? and in the parameter mapping section assign variables @[User::Num1] & @[User::Num2].
    Using this method I can have variables with Direction = OUTPUT and that way I can resolve the problem of reading values returned from the stored procedure.

    I am finding it difficult to map the DataType of the variables to the SqlDataType that the stored proc is expecting. BOL states that it depends on the connection; which in my case is OLEDB. I would love to see a page where the mapping is given

    E.g Long = Int32, Short = smallint, TIMESTAMP = datetime ......


    I would ideally like to use Method 1 or 2 and know the following.
    1. How to dynamically set values of variable @User::Num1 & @User::Num2
    2. Read from OUTPUT arguments provided to stored procedure.
    3. I would like to use the syntax of the following type.
            EXEC    @User::return_value = [dbo].[spTest]
            @Num1 = @User::Num1,
            @Num2 = @User::Num2,
            @mysum = @User::mysum OUTPUT

        rather than use ? for passing parameters.

    Many Thanks & Have a Happy Day
    Tejas
    Monday, June 30, 2008 12:57 AM

Answers

  •  bytebugs wrote:

     

    Hi,

    I am trying to understand the steps involved in executing a stored procedure from Execute SQL Task.

    Method 1
    Step 1: Dynamically construct the sql and assign it to a string variable.
    @[User::mysql] = "exec [dbo].[spAdd2NumbersTest] " +  (DT_WSTR, 10) @[User::Num1] + " , " +   (DT_WSTR, 10) @[User::Num2]

    Step 2: In the Execute SQL Task editor set the following properties
    a. SourceType = Variable
    b. SoruceVariable = @[User::mysql]


    ssis variable values can be evaluated at run-time using expressions.  this is accomplished by setting the variable's EvaluateAsExpression property to "True".  to use this value as a sql statement, set the variable data type to string.



    Question.
    1. How & where do I dynamically set the value of variable @[User::mysql]?
    2. Lets say the stored proc returns the sum of two numbers. How do I capture the return value from the stored proc?
    3. If I want to use an OUT variable in the argument list of the stored procedure then how do I construct the value of @[User::mysql] such that the stored proc populates an output variable of type @[User::mysum]?

     

    the answers to these questions are explained fairly well in the BOL: http://msdn.microsoft.com/en-us/library/ms141003.aspx

     


    Method 2
    I think the practice for executing INSERT, UPDATE, DELETE type procedures is to dynamically set the property of SqlSatementSource using the Expression option in Execute SQL Task Editor. This way we don't need the variable @[User::mysql] and the SourceType = Direct Input. This method is fine and I guess easiest of all. But I wonder how to solve the problem reading the value/s returned from the stored procedure. Also How to I dynamically set the values for variables @[User::Num1] & @[User::Num2]?

     

    stored procedure parameter values can be mapped to ssis variables.  again, all of this is explained fairly well in the BOL: http://msdn.microsoft.com/en-us/library/ms141003.aspx

    Method 3
    I could set the SourceType = Direct Input & SQLStatement = dbo.spAdd2NumbersTest ?, ? and in the parameter mapping section assign variables @[User::Num1] & @[User::Num2].
    Using this method I can have variables with Direction = OUTPUT and that way I can resolve the problem of reading values returned from the stored procedure.

    I am finding it difficult to map the DataType of the variables to the SqlDataType that the stored proc is expecting. BOL states that it depends on the connection; which in my case is OLEDB. I would love to see a page where the mapping is given

    E.g Long = Int32, Short = smallint, TIMESTAMP = datetime ......

     

    if possible, i suggest that you use an ssis variable expression to create the sql statement that executes your stored procedure.  then, you can use this variable in the execute sql task.  bear in mind that ssis variable expressions have a 4000 character limit.

    i strongly suggest that you carefully walk through the BOL to solve your issues with this -- there is no "best way" to solve them.  if you have any specific problems, i.e., sql statement not executing, output or return values not yielding expected results, then come back and ask us a specific question -- this is the best way we can help you.

     

    hth

    Monday, June 30, 2008 4:51 AM
    Moderator

All replies

  • I think the answer to my questions about how do I dynamically set the value of the variable in SSIS could be the following.
     
    1. In can use the Execute SQL Task (1 per variable assignment) and write simple select statement and set the ResultSet property to 'Single row' (which will expects a single value to be returned from the query)  and set the value of variables.
    SELECT 3 AS Num1
    SELECT 5 AS Num2
    SELECT 3 + 5 AS Addition
    SELECT 3 * 5 AS Multiplication
     
     
    Ideally I would have liked to use a query of the following type enabling me to update more than one variable.
    SELECT 3 AS Num1, 5 AS Num2, 3 + 5 AS Addition, 3 * 5 AS Multiplication
     
     
    What do you folks do for a similar requirement? 

     

    I still don't have a clue about how to use the variables as input and set property of  OUTPUT parameter to a stored proc.

    I guess I can return a single result from my stored proc and then map it to a variable in the resultset.  
     
    Many Thanks & Have a Happy Day !
    Tejas.

     

    Monday, June 30, 2008 2:24 AM
  •  bytebugs wrote:

     

    Hi,

    I am trying to understand the steps involved in executing a stored procedure from Execute SQL Task.

    Method 1
    Step 1: Dynamically construct the sql and assign it to a string variable.
    @[User::mysql] = "exec [dbo].[spAdd2NumbersTest] " +  (DT_WSTR, 10) @[User::Num1] + " , " +   (DT_WSTR, 10) @[User::Num2]

    Step 2: In the Execute SQL Task editor set the following properties
    a. SourceType = Variable
    b. SoruceVariable = @[User::mysql]


    ssis variable values can be evaluated at run-time using expressions.  this is accomplished by setting the variable's EvaluateAsExpression property to "True".  to use this value as a sql statement, set the variable data type to string.



    Question.
    1. How & where do I dynamically set the value of variable @[User::mysql]?
    2. Lets say the stored proc returns the sum of two numbers. How do I capture the return value from the stored proc?
    3. If I want to use an OUT variable in the argument list of the stored procedure then how do I construct the value of @[User::mysql] such that the stored proc populates an output variable of type @[User::mysum]?

     

    the answers to these questions are explained fairly well in the BOL: http://msdn.microsoft.com/en-us/library/ms141003.aspx

     


    Method 2
    I think the practice for executing INSERT, UPDATE, DELETE type procedures is to dynamically set the property of SqlSatementSource using the Expression option in Execute SQL Task Editor. This way we don't need the variable @[User::mysql] and the SourceType = Direct Input. This method is fine and I guess easiest of all. But I wonder how to solve the problem reading the value/s returned from the stored procedure. Also How to I dynamically set the values for variables @[User::Num1] & @[User::Num2]?

     

    stored procedure parameter values can be mapped to ssis variables.  again, all of this is explained fairly well in the BOL: http://msdn.microsoft.com/en-us/library/ms141003.aspx

    Method 3
    I could set the SourceType = Direct Input & SQLStatement = dbo.spAdd2NumbersTest ?, ? and in the parameter mapping section assign variables @[User::Num1] & @[User::Num2].
    Using this method I can have variables with Direction = OUTPUT and that way I can resolve the problem of reading values returned from the stored procedure.

    I am finding it difficult to map the DataType of the variables to the SqlDataType that the stored proc is expecting. BOL states that it depends on the connection; which in my case is OLEDB. I would love to see a page where the mapping is given

    E.g Long = Int32, Short = smallint, TIMESTAMP = datetime ......

     

    if possible, i suggest that you use an ssis variable expression to create the sql statement that executes your stored procedure.  then, you can use this variable in the execute sql task.  bear in mind that ssis variable expressions have a 4000 character limit.

    i strongly suggest that you carefully walk through the BOL to solve your issues with this -- there is no "best way" to solve them.  if you have any specific problems, i.e., sql statement not executing, output or return values not yielding expected results, then come back and ask us a specific question -- this is the best way we can help you.

     

    hth

    Monday, June 30, 2008 4:51 AM
    Moderator