I have an OLE DB step which selects data from my source table with these columns:
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?