none
SSIS: How to use one Variable as Input and Output Parameter in an Execute SQL Task

    Question

  • Hello,

    i need your help,I'm working on this issue since yesterday and have no idea how to deal with it.

    As I already said in the tilte i want to start a stored procedure via a Execute SQL Task which has around 15 prameters. 10 of these should be used as input AND output value.

    As an example:

    i have three  Variable:

    var1    int        2

    var2    int     100

    var3    int     200

    the stroed procedure:

       sp_test

          @var1 int

          @var2 int output

          @var3 int output

       AS

       BEGIN

            SET @var2 = @var2 * @var1

            SET @var3 = @var3 + @var1

       END

    So in the Execute SQL Task i call the Stored Procedure as follwos:

        Exec sp_test  @var1 = ?, @var2 = ? output, @var3 = ? output

    (I use an OLE DB Connection)

    The parameter mapping is as follows:

    User::Var1        input                   numeric              0                 -1

    User::Var2        input/output         numeric              1                 -1

    User::Var3        input/output         numeric              2                 -1

    Now my problem. If i set  Var2 and Var3 as Input parameter the values are still the same after running the package. If i set them to a output value the are both Null because the procedure doesnt get any values.

    I already tried to list them a second time - like

        User::Var2        input                  numeric              1                 -1

        User::Var2        output                 numeric              1                 -1

    or i use a new variable

        User::Var2                  input                  numeric              1                 -1

        User::Var2Return        output                 numeric              1                 -1

    but i alwas get the error

    "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

    Has anybody an idea how I can solve this problem?

    Thanks a lot.

    Kind Regards,

    Alice

    Thursday, March 25, 2010 8:44 AM

Answers

  • Hello Alice,

     

    In your stored procedure you can just call following statement select @var1,@var2,@var3,... at the end of the procedure.

    This will return your variables in a result set.

     

    kind regards

     

    • Marked as answer by Zongqing Li Wednesday, March 31, 2010 8:00 AM
    Thursday, March 25, 2010 10:17 AM

All replies

  • Alice,

     

    Why do you want to use  in out arguments in your stored procs?

    I would use two input parameters for your stored proc and a resultset as output.
    In that case you would only need to split the values in the resultset in the right SSIS variables.

    Besides that, I don't think it is possible to use in out parameter mapping in SSIS... (confirmation needed)

    Kind regards

     Alain

    Thursday, March 25, 2010 9:16 AM
  • Hi Alain,

    thx for your answer.

    I have around 15 procedures called one after the other to calculated and modify my values. Each procedure is responsible for an other but overlapping set of variables. So i thought it would be a good idea to call them one after the other with the needed variables via a execute sql task.

    So if i use a result set, how i get my stored procedure to return 10 values? I would have to use a Function instead of a procedure, wouldn't i?

    As if i have 15 procedures this would be a lot of work.

    But thanks a lot for the idea. I think an other idea would be to create one function which calls all stored procedures and returns all the calculated values as a result set, wouldn't it?.

    Kind Regards.

    Alice

     

    Thursday, March 25, 2010 9:49 AM
  • Hi Alain,

    thx for your answer.

    I have around 15 procedures called one after the other to calculated and modify my values. Each procedure is responsible for an other but overlapping set of variables. So i thought it would be a good idea to call them one after the other with the needed variables via a execute sql task.

    So if i use a result set, how i get my stored procedure to return 10 values? I would have to use a Function instead of a procedure, wouldn't i?

    As if i have 15 procedures this would be a lot of work.

    But thanks a lot for the idea. I think an other idea would be to create one function which calls all stored procedures and returns all the calculated values as a result set, wouldn't it?.

    Kind Regards.

    Alice

     

    Thursday, March 25, 2010 9:49 AM
  • Hello Alice,

     

    In your stored procedure you can just call following statement select @var1,@var2,@var3,... at the end of the procedure.

    This will return your variables in a result set.

     

    kind regards

     

    • Marked as answer by Zongqing Li Wednesday, March 31, 2010 8:00 AM
    Thursday, March 25, 2010 10:17 AM
  • Hallo.

    Thanks. I didn't know this.

    And i have one further question: If i use a Result Set (or a Singel Row - i always get only one row back), i have to use a object-variable - haven't i?

    how can i allocate the value of the first coulumn to a int variable and the value the second one to another?

    Thank you so much.

    Kind regards.

    Thursday, March 25, 2010 10:26 AM
  • As already mentioned by Alain, you'll return all the values as a ResultSet. Capture it in an Object variable, yes, and then parse the resultset, you can use script task for that. The collection will have your parameters in the same sequence as the sequence in which the SELECT query in your stored procedure. So you'd know which index to use for getting a parameter's value.

     

    Hope this helps.

     

    Cheers!!

    Muqadder.  

    Thursday, March 25, 2010 11:54 AM
  • Yes, I have conformed it. It was a painful surprise that I was unable to use the same parameter for both direction of input and output. It gave a wrong result, zero for integer.
    Tuesday, June 28, 2011 8:51 AM
  • Please see my workaround below:

    connect.microsoft.com/SQLServer/feedback/details/126352/

    Jim Jao

    Thursday, February 13, 2014 8:43 PM