Post reply

SSIS - Comparing row values.

  • May 2, 2013 at 8:26 am

    #272023

    Hi All

    I have a question regarding managing data based on whether or not the row has previously been included in an extract. The background is that i have to create a flat file extract to feed another database, this extract must contain new record and any updates to historic records.

    Using SSIS 2008 r2 - I have three tables:

    SourceDataView - This is the data source.

    StagingTable

    BatchHistoryTable - A record of all rows included in previous extracts.

    Brief dataflow explanation is:

    Data gathered from SourceDataView>

    Lookup task checks the Unique Record ID against the BatchHistoryTable>

    LookupNoMatchOutput to StagingTable

    LookupMatchOutput to Conditional Split task

    Conditional Split then uses an expression to check if certain fields have changed>

    Row exists in BatchHistory and no fields have changed = Ignore

    Row exists in BatchHistory and fields have changed = Forward to StagingTable

    I now have a StagingTable which has new records (inserts) and records which have changed (updates).

    The history table might look as below -

    ID - Name - FavouriteFood -RecordType

    1 - Jim - Curry - Insrt

    1 - Jim - Fish - Update

    The problem is that because the conditional split checks the history row by row, a changed record will duplicate to the StagingTable in future extracts i.e. the expression checks [1 - Jim - Fish - Update] against the table and as soon as it hits [1 - Jim - Curry - Insrt] it sees this as a changed to the record - so i now end up with this:

    ID - Name - FavouriteFood -RecordType

    1 - Jim - Curry - Insrt

    1 - Jim - Fish - Update

    1 - Jim - Fish - Update

    Unfortunately the is no Unique RowId and i have limited control over the database design.

    Any advice much appreciated.

    Thanks

  • May 3, 2013 at 5:49 am

    #1612161

    Hi,

    can you please explain your situation a little bit more. How limited is your control over the database design? If you don't have unique ID how do you make the look-up?

    I think you have many opportunities to solved your drawbacks, but I cannot suggest an alternative until I fully understand your scenario.

    Kind Regards

    Paul Hernández

Viewing 2 posts - 1 through 1 (of 1 total)

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