Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS - Comparing row values. Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 8, 2013 9:41 AM
Points: 1, Visits: 21
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

Post #1448832
Posted Friday, May 3, 2013 5:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
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
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1449152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse