Delete records that don exist in the destination

Hi all,

I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.

The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?

Am I correct in the way I am doing or there is something better to handle this?

Thanks in advance

MShetty wrote:

Hi all,

I am developing an ETL wherein the requirement is to do an incremental load and at the same time, if there is a record that got deleted in the Source delete it from the destination too, makes sense.

The approach am doing is, pick data from the SRC and Destination, pass it onto a Merge join component, do a Full Outer join, then pass the rows to a conditional split. Newly Added records and updated records I can handle, how do I handle the Deleted records?

Am I correct in the way I am doing or there is something better to handle this?

Thanks in advance

That sounds like it will work. You basically need to compare the source and destination. Any records that are in the destination but not the source have to be removed - it sounds as if that is what you are attempting.

They can be deleted using an OLE DB Command. or you can push the records to be deleted into a temporary table and delete them using an Execute SQL Task.

-Jamie

|||

Hi Jamie,

Thx for the comments, I started working exactly the same way, but now am into an issue here. I am starting with two small tables as the first step. The table has this schema.

UserId (int) (PK)

UserName (varchar)

IsActive (bit) i am taking two DFT's one each to the two Databases and the selected records are passed onto a Merge Join. UserId is the join key here. Now the records that are having a match already are returned from this component right ? If I make the join type as a Ful Outer JOin and pass on to conditional split transform, how do I get the records that are in Destination DB but were deleted from the Source, I am just getting the newly added records and the updated one's. Please guide..

Thanks a lot

|||

I don't think you need a fullt outer join, just a left join will do it (with the incoming data on the left input).

Described here:

Get all from Table A that isn't in Table B
(http://www.sqlis.com/default.aspx?311)

-Jamie

|||

Thx for the inputs Jamie.

My problem of deleting the records in the destination that were removed from the Source DB was solved by using an IsNull (SrcTable.PK) in a conditional split and then direct those rows to an OLE DB Command Component.