Send Email when No Rows are transferred in Data Flow Task of SSIS - SQL 2008

There might be some situations when user must be notified that NO RECORD was transferred to destination while executing SSIS Package. There is no one way of doing this, however, I find the following as the most simple and less complicated way. The steps are:

1. Declare a variable at project Scope level in SSIS.

2. Open the Data Flow task where the 0 row count needs to be validated. Add Row Count task between Source and Destination, and assign row count result to a variable that was declared above. Look at the following two images:

 

3. Go back to control Flow Tab, Just below the Data Flow task, that was modified above, add a Precedence Constraint and join it to Send Mail Task, add another Precedence Constraint and Join it to the task that should be executed next. At this point, under the same Data Flow Task, there will be two Precedence Constraints with Green Color (Success). Next, condition needs to be added to these Precedence Constraints, so that in case there is no record an email should be sent to the user.

4. Remember that already a variable has been set above with Row Count, same variable will be used here to decide whether there was any row returned or not. Configure the Precedence Constraint just similar to what is shown in figure below. Doing this, it will make sure that email will be sent only if RowCount is 0.

5. Configure second Precedent Constraint, that was added later so that step next to is executed if RowCount is greater than 0. Similar the screen below:

6. Once these steps are completed, you will notice 'fx' icon next to each of the Precedent Constraint.

7. Next, Set the Precedent Constraint between Email Task and the task that should be executed when more than 0 records are transformed. This step should be ignored, if SSIS has not to proceed further if 0 rows are returned.

After completing all the steps, the Control Flow Task will appear like: