locked
select rows for ETL processing RRS feed

  • Question

  •  

    I'm new to ssis (in case that isn't obvious!)

    I want to set up a package to automate a transfer of data from a relational database to a datawarehouse.  I would like to transfer rows daily, but I only want to transfer those which have not previously been transferred.

    How/where do I tell the package/data flow task/ whatever to just transfer the daily differential from the last time it ran?

    Thanks

    Wednesday, January 23, 2008 8:01 PM

Answers

  •  SqlSister wrote:
    So... do I use a variable? Where do I store the date to which I am comparing?

     

     

    You can use a table in your database to store the last date / time (or ID, or whatever you end up using) that you extracted. Then use an Execute SQL Task to retrieve that value at the beginning of your packages.

    Thursday, January 24, 2008 2:04 AM

All replies

  • I would suggest a book called The Microsoft Datawarehouse Toolkit
    Wednesday, January 23, 2008 8:07 PM
  • Typically, you select your source records by a date column.  In reality, this question has no real simple answer that we can give you because only you know your data.  How do you identify the rows to select?  Does the data change rapidly?  Does the data change only once per day?  Etc...

     

    Wednesday, January 23, 2008 8:19 PM
  • So... do I use a variable? Where do I store the date to which I am comparing?

     

    Wednesday, January 23, 2008 8:46 PM
  • Generally you just pull all the data from the previous day, so this can be part of your query.
    Wednesday, January 23, 2008 8:51 PM
  •  SqlSister wrote:
    So... do I use a variable? Where do I store the date to which I am comparing?

     

     

    You can use a table in your database to store the last date / time (or ID, or whatever you end up using) that you extracted. Then use an Execute SQL Task to retrieve that value at the beginning of your packages.

    Thursday, January 24, 2008 2:04 AM