asked
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, other images are links: ![ETL][1] ![Conditional Split][2] [Exce Source][3] [OLE DB Command object for remove rows][4] [Excel Records][5] [Database Records][6] [ETL Running][7] [1]: /storage/temp/4575-etl.png [2]: /storage/temp/4576-conditional-split.png [3]:
https://i.stack.imgur.com/rmzKn.png [4]:
https://i.stack.imgur.com/U8GRi.png [5]:
https://i.stack.imgur.com/DKIqT.png [6]:
https://i.stack.imgur.com/fPDA5.png [7]:
https://i.stack.imgur.com/h6tTl.png
SOLUTION: 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. ![SolutionRuning][1] ![SourceA][2] ![SortStep][3] ![MergeJoin][4] ![ConditionSplitCondition][5] ![OLE DB Command][6] [1]: /storage/temp/4578-etl-solution-running.png [2]: /storage/temp/4579-source-a.png [3]:
https://i.stack.imgur.com/mXpv0.png [4]:
https://i.stack.imgur.com/fKsqL.png [5]:
https://i.stack.imgur.com/y3AvL.png [6]:
https://i.stack.imgur.com/sBgRJ.png
17 People are following this question.
