Lookup Match output to check other columns and insert complete row as new row if any have changed

SSIS and Import/Export
1

I have an OLE DB step which selects data from my source table with these columns:

JOBNO,
JOBDATE,
ETA
ARRIVAL
START
FINISH
TIMESTAMP

I then have a LOOKUP transform to check if the JOBNO field exists or not in the destination table. If it does not, then the row is inserted as the NO MATCH output. This part works fine.

But for the MATCH output, I need to do a couple of things. I first need it to find the most recent row for the matching JOBNO based on the TIMESTAMP of the matching row in the destination table. I then need it to check ETA, ARRIVAL, START and FINISH columns of that record with the record in the MATCH output. If any of these 4 columns have changed in any way then I want it to insert the record as a complete new row into the destination table. If however, the record from MATCH output is exactly the same for those 4 columns, then that row should not be inserted.

What is the best way to accomplish this?

Many thanks

2

I would use a staging table to load the data - then you can use simple T-SQL code to identify the rows to compare and perform an INSERT as needed.

You could try and configure the SCD component - which will eliminate the requirement for a lookup and perform the inserts for you, but it would be much simpler to just use a staging table.

2 Likes
3

Thank you - understood and will put in a staging table for this.

Martyn