SSIS - Comparing row values.

• VivaLaPablo

  SSC Journeyman

  Points: 75

  More actions

  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

• Paul Hernández

  SSCarpal Tunnel

  Points: 4961

  More actions

  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