locked
COLUNM VALUE IN VARIABLE. RRS feed

  • Question

  • Hi,

    I need to implement following scenario in my daily run ETL job.

    My job is reading data from oracle table and writing it into SQL server table.  I have sequence_number column in my source and target tables. 

    When I run the job next time, i need to take sequence_number from the target SQL Server tables and pull rows from source where sequence_number > source_number

    How I can implement this in SSIS. Any help would be really appreciated.

    Best regards,
    Sengwa


    Monday, June 8, 2009 9:43 PM

Answers

  • Place an Execute SQL Task on the design surface.
    Open up the editor and select your connection manager type and connection manager.
    Type in your SQL Statement, and select a Single Row result set type.
    Change to the Result Set tab, press Add.  Use a result name of zero (0), which means you want the first column.  Pick an integer variable from the dropdown list.

    Done!
    Todd McDermid's Blog
    • Marked as answer by Sengwa Tuesday, June 9, 2009 7:07 PM
    Tuesday, June 9, 2009 3:31 PM

All replies

  • Do you mean you need to select rows from your source where your source's sequence_number is larger than the largest sequence number in your destination?

    If you need to do that, then use an Execute SQL Task to retrieve the largest sequence number from your destination table and place it in an SSIS variable.

    Then, use property expressions, a parameterized query, or an SSIS variable to construct a SQL statement for your Source component that has a WHERE predicate that includes only rows with a sequence_number greater than the value you retrieved into your SSIS variable.
    Todd McDermid's Blog
    • Proposed as answer by Sudeep Raj Tuesday, June 9, 2009 4:51 AM
    Monday, June 8, 2009 10:26 PM
  • Thanks a lot for your response Todd. Yes, you are correct. I need to select max(sequence_number) from the target before pulling data from the source.

    Can you please let me know how to store the value in a variable?

    Best regards,
    Segwa
    Tuesday, June 9, 2009 1:01 PM
  • Place an Execute SQL Task on the design surface.
    Open up the editor and select your connection manager type and connection manager.
    Type in your SQL Statement, and select a Single Row result set type.
    Change to the Result Set tab, press Add.  Use a result name of zero (0), which means you want the first column.  Pick an integer variable from the dropdown list.

    Done!
    Todd McDermid's Blog
    • Marked as answer by Sengwa Tuesday, June 9, 2009 7:07 PM
    Tuesday, June 9, 2009 3:31 PM
  • Thanks Todd.

    Appreciate your response. It worked.

    Best regards,
    Sengwa
    Tuesday, June 9, 2009 7:08 PM