SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - Comparing row values.


SSIS - Comparing row values.

Author
Message
VivaLaPablo
VivaLaPablo
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Paul Hernández
Paul Hernández
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 661
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search