check if lookup yields any valid rows for insertion before clearing table using ssis

Question

SSIS ignoramus needing help!

the situation: a temp table is populated from an excel file, which has been known to change formats at random times, that is owned by a different group. a lookup need to be performed on the temp table, tableA, to populate tableB with valid data. if the lookup results in 0 rows being returned, an email should be sent and the existing data in tableB should remain untouched. If the lookup results in a number of valid rows > 0, tableB should have all rows deleted and the new records from the lookup on tableA inserted.

question: what would be the best way to check if there are any valid rows and perform the appropriate action(s), depending on my results?

Thanks!

Answer 1

Connect the Matched output from the lookup to a Row Count transform, which will store the count of found rows in a package variable. In the control flow, connect the data flow task (with the lookup) to an Execute SQL to perform the delete. Add an expression to the constraint between data flow and Execute SQL by right-clicking the constraint and choosing Edit. Add a condition like "@myRowCountVariable > 0" - this will prevent the Execute Sql Task from running unless the row count was greater than 0. Connect the Execute Sql to another data flow to perform the insert.

You can add a Send Mail task, and connect it to the original data flow, using an expression like "@myRowCountVariable==0" to control whether the email gets sent.