Post reply

Reg: Incremental Updates

  • September 10, 2007 at 2:41 am

    #118584

    Hi All,

    Please give me the idea to implement Insert and update package.

    Req:

    1. My table having StoreId, ProductName, Price, ModifiedDate.

    2. I need to create a package which should do insert and update.

    I am not able to do it.

    Please tell me the steps to create it.

    Thanks

    Senthil

    Deloitte

     

  • September 11, 2007 at 1:49 am

    #732679

    Hi SenthilKumar,

     

    You required the data export one table to another table.

     

    Thanks & Regards,

    Amit Kumar

  • September 11, 2007 at 2:46 am

    #732699

    I need to extract data from one table and load in stagging table. my source table will update every 12 hrs so my package should do Update and also insert new records.

    1. Source from SQL Server 2000 DB

    2. Stagging is SQL Server 2005 DB.

     

    Thanks

    Senthilkumar

  • September 11, 2007 at 2:47 am

    #732700

     

    I need to extract data from one table and load in stagging table. my source table will update every 12 hrs so my package should do update and also insert new records.

    1. Source from SQL Server 2000 DB

    2. Stagging is SQL Server 2005 DB.

     

    Thanks

    Senthilkumar

  • September 11, 2007 at 7:15 am

    #732770

    SenthilKumar...

    Once you have a unique identifier the process is relatively easy to complete.

    There are two easy methods that you can use.

    The first way is to add a Lookup and a Conditional Split item to your Data Flow task.

    1. Add a Lookup to the data flow and connect it before your insert task.
    2. Configure the lookup to match existing rows in the destination with the data in the pipeline. Return any NOT NULL column (like the identity of the destination table)
    3. Configure the Lookup Error Output to ignore errors
    4. Add a Conditional Split after the lookup. Set a condition of  ! ISNULL(mycol)  where mycol is the name of the column you returned from the lookup. Note the exclamation mark in the condition.
    5. Connect the default output of the conditional split to your data flow destination

    With this method, all the rows that can be found by the lookup will have a value in the return column, and be filtered out with the conditional split. All the rows not found in the destination will head on to be inserted.

    Another method that works, but only if you have a unique constraint is by sending everything to your data flow destination, and just setting the error output to ignore. Then all rows will attempt an insert, and be skipped if your unique constraint or any other error occurs. This method though easier can actually cause problems as a different error might be occuring but it won't fail the package

Viewing 5 posts - 1 through 4 (of 4 total)

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