CODEWITHSUNDEEP
sql-serverssisdata-warehouse
JC_BI
JC_BI

Reputation: 419

Deleting rows from the destination that have been deleted from the source SSIS using Conditional Split

I'm building a SSIS package that Update, Insert and Delete rows from the destination based on the Source of course. Insert and update works fine but I'm not able to delete records from the destination that have been deleted from the source, as you can see in the image below I'm doing the test with an excel file and SQL server destination table. I don't know what exactly I have to put for the conditional split object in order to remove the old record from the destination. If you notice the records the has to be delete from the destination is highlighted in the Database Records image. Images below of the entire process step by step:

ETL IMAGE

Excel Source

Conditional Split

Lookup

OLE DB Command object for remove rows

Excel Records

Database records

ETL Running

Upvotes: 4

Views: 2705

Answers (1)

JC_BI
JC_BI

Reputation: 419

SOLUTION: AS Ezequiel López Petrucci explain in the comment, I just had to add my destination table as Another OLE DB Source after that sort the table an then Merge Join both tables (Full Outer Join) in order to compare the records, finally I changed the condition for the Conditional Split transformation. Images are below explain step by step.

ETL Solution Runing

Source A

Both Sort

Merge Join

ConditionalSplitCondition

OLE DB Command to Delete unnecessary rows

Upvotes: 4

Related Questions