Post reply

Setting values to variables using Execute SQL Task

  • July 22, 2008 at 11:56 am

    #126468

    Hi,

    I am looking for a way to set the values of variables using Execute SQL Task.

    I would declare 2 variables VarA and VarB as Int32.

    I would give the following code in the SQL Statement:

    SELECT ? = 1

    SELECT ? = ? + 2

    Connection type would be OLE DB

    In the ParameterMapping, i would add the following mappings:

    VariableName Direction DataType ParameterName ParameterSize

    User::VarA Output Long 0 -1

    User::VarB Output Long 1 -1

    User::VarA Input Long 2 -1

    When I look at the value of the variables using the Local Window, I see that the value of VarA is 1 (as expected) and the VarB is 2 (I expect it to be 3 (VarA + 2)). It appears SSIS is considering the value of VarA as 0 in the second instance.

    Can someone tell me how to do this? Am I missing something? I observed that we can do this using 2 Execute SQL Tasks. But I want to achieve this using a single Execute SQL Task.

    a

    Please help.

    Thanks in advance.

    PS: This sounds wierd, but when I was working on my personal machine, it atleast sets the values of the variables. But on my office machine, it errors out saying Incorrect syntax near SELECT ? = 1

    SELECT ? = ? + 2. I don't understand.

  • July 22, 2008 at 12:39 pm

    #846636

    I don't know this for certain, but I'm guessing that the input values are "set" when the query first starts, so the value is zero. IF you set the value of the variable in the designer to be 5, I'll bet it comes out to be 7. You could use a temporary holding variable to do the work for you though:

    DECLARE @NameThatMakesSense int

    SELECT @NameThatMakesSense = 1

    SELECT ? = @NameThatMakesSense

    SELECT ? = @NameThatMakesSense + 2

    (replacing @NameThatMakesSense with a name that makes sense, of course 🙂 )

    Hope this helps!

    Chad

    EDIT: You'll also need to remove the input variable to get this to work

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply