Post reply

How to Create a Package which should do an Incremental UPDATE using SSIS

  • August 31, 2007 at 2:35 am

    #120365

    Hello All,

    For my Requirement is:

    My Client(Retail Store) have a table (in Data Warehouse) with over 1 million records.

    The Table have like this.........

    StoreName, ProductName, StandardPrice, CurrentPrice, BillPayedByCash/CreditCard)

    ---------------------------------------------------------------------------------------------

    TescoNL      NevaMen        50$                 45$             CreditCard 

    TescoScot   NevaMen        90$                 75$             CreditCard 

    TescoSZ     NevaMen        70$                 65$             CreditCard 

    ..........................Like this....

     

    I need to Pull all the Records from this table to SqlServer2005 table.

    It is working First time fine, Secound time while running the package it is not Updating. It is inserting every time.

    Please tell me How to solve this problem.

    I need to Incremental Update : give me a clear picture which Transformation task i need to use. How to find the Existing Records.

    Becase there is no ID Colum.

    I am using SQL TASK now whis is Deleting and Inserting every time but It not a correct way.

     

    Please Guys Give me the Good Picture for this.

     

    Thanks For All .

    Thiru

     

     

     

  • August 31, 2007 at 6:23 am

    #730728

    Without any unique identifiers / indexes in your table this will be very slow and inefficient.

    Why doesn't your table have any? Why dont you add some when the data is imported?

     

    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 2 posts - 1 through 1 (of 1 total)

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