locked
How to get value from database using SSIS RRS feed

  • Question

  • Hello all,

    I want to save max value of student id in the user defined variable ,I tried with execute sql task but it is displaying 0. I am new to ssis ,thanks in advance

    Thursday, November 21, 2013 12:27 PM

Answers

  • use query like

    SELECT MAX(STudentID) AS MaxID
    FROM StudentTable

    Set resultset property to Single Row. In Resultset mapping tab add your user created variable and map it to MaxID. The type of variable should be integer 


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Thursday, November 21, 2013 12:38 PM
    • Proposed as answer by Mike Yin Friday, November 22, 2013 3:48 PM
    • Marked as answer by Mike Yin Sunday, December 1, 2013 1:11 PM
    Thursday, November 21, 2013 12:35 PM
  • First of all you need to create a new variable in your SSIS package like below:

    then you go to your toolbox and get a "Execute SQL Task", within the SQL task you have to set your connection string, then enter your SQL command

    Next you need to map your parameter, on the screen above click "Result Set" and then click "add" and look for your parameter and click ok

     


    Please mark as Answer if this answers your question ...Thanks, Sary Awwad

    • Proposed as answer by Mike Yin Friday, November 22, 2013 3:48 PM
    • Marked as answer by Mike Yin Sunday, December 1, 2013 1:11 PM
    Thursday, November 21, 2013 9:54 PM

All replies

  • use query like

    SELECT MAX(STudentID) AS MaxID
    FROM StudentTable

    Set resultset property to Single Row. In Resultset mapping tab add your user created variable and map it to MaxID. The type of variable should be integer 


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Thursday, November 21, 2013 12:38 PM
    • Proposed as answer by Mike Yin Friday, November 22, 2013 3:48 PM
    • Marked as answer by Mike Yin Sunday, December 1, 2013 1:11 PM
    Thursday, November 21, 2013 12:35 PM
  • I would also add that your variable should be the same name as the field returned by the query (i.e. MaxID).

    I have not been able to map results to a variable in the past until doing this.


    Bonediggler

    Thursday, November 21, 2013 3:22 PM
  • First of all you need to create a new variable in your SSIS package like below:

    then you go to your toolbox and get a "Execute SQL Task", within the SQL task you have to set your connection string, then enter your SQL command

    Next you need to map your parameter, on the screen above click "Result Set" and then click "add" and look for your parameter and click ok

     


    Please mark as Answer if this answers your question ...Thanks, Sary Awwad

    • Proposed as answer by Mike Yin Friday, November 22, 2013 3:48 PM
    • Marked as answer by Mike Yin Sunday, December 1, 2013 1:11 PM
    Thursday, November 21, 2013 9:54 PM