Post reply

Storing result value to variable

  • November 26, 2006 at 4:12 pm

    #115183

    Hi

    I have a procedure that select max value from table.The result of the proc shld be stored in variable.Please let me know how to proceed as I am new to SSIS.

    Thanks in advance

    Usha

  • November 27, 2006 at 8:04 pm

    #674212

    Hi,

    If you are using an execute sql task do the following.

    1. Create your variable.

    2. right click your Execute SQL Task

    3. On the general tab , set the resultset type to single row.

    4. On the result set tab, click add,  select the column returned by your procedure in the result name column and select your variable in the variable name column.

    5. Click ok.

    You then have your result mapped to your variable.

    I hope that helps!

     

  • November 27, 2006 at 8:39 pm

    #674215

    Hi

    Thanks so much for your reply.But the value of variable is not getting updated after execution.I can view the new value in watch window when i debug.

     

    Regards

    Usha

  • November 27, 2006 at 9:29 pm

    #674219

    Hi,

    Could you give me some more details of your process?

    Have you checked that your variable is in scope?

     

  • November 28, 2006 at 3:13 pm

    #674545

    Hi

    I did the following steps:

    Add a variable in variables pane.. call it "MaxVal" and select

    the right datatype..

    Drag&Drop an Execute SQL Task and then configure it with following

    specs:

    General tab

    - Choose a connection manager for query (create it before configuring

    this execute sql task) in Connection option.

    - Choose "Direct Input" in SqlSourceType

    - Write SQL Statement that returns max value in SQLStatement option

    - Choose "Single Row" in ResultSet option

    Now click on ResultSet tab

    - Click on "Add" button

    - Write "0" (without quotes) on ResultName column and select the right

    variable in Variable column

    - Pay attention on type of variable.. It must be the same than the DB

    column type. (for example, int = INTEGER, varchar = STRING)..

    but I able to view the value in watch window but not in variables window.My question is will that get updated in variable window as well.

     

    Thanks

    Usha

  • November 28, 2006 at 8:07 pm

    #674570

    Hi ,

    Ok now I see what you mean. The variables window shows the value of the variable when the project was loaded.

    If you specified the variable in the Package Configuration it will fetch that value from the database on each load.

    So if you wanted to save away the MaxID, to next time start from that point again, then you have to update the value of the variable in the database. Which would then be read the next time the package is loaded.

    But aside from that, the value in the variables window doesn't change.

     

  • November 30, 2006 at 12:49 am

    #674860

    Thanks so much

    Regards

    Usha

Viewing 7 posts - 1 through 6 (of 6 total)

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