Adding notification messages in SSIS is really quite easy once you understand how you can use expressions, one of my pet hates is receiving process notification emails that contain things like " file(s) processed" or " record(s) processed".
So, now we have a scenario with a disgruntled user how do we create a more professional looking solution? Remember that your boss and maybe even your bosses boss will probably see these emails at some point, so you should make it as perfect as possible.
Not everyone will have an SMTP server available to test the notification especially if reading from home, not to worry in this post I will use some variables to simulate the object properties instead.
Normally to write a custom mail notification one would create an expression on the "MessageSource" property of the "Send Mail Task" for this example I am simply going to create the variable @[User::MessageSource] as a String and evaluate it as an expression. If you have not done this before then click on the variable name in the variables window to select it, then in the properties window change the value of "EvaluateAsExpression" to True.
Next we are going to create an Int32 variable called "RowCount", normally I would populate this variable using a “Row count” task within a Data Transformation Task, but for simplicity we will just create the variable and set it to 2.
The third and final variable in the puzzle is the variable "Plural", again this is going to be evaluated as an expression. We can then set the value of @[User::Plural] as the following expression:
@[User::RowCount] == 1 ? "" : "s"
If you’re not familiar with SSIS expressions then in T-SQL it would look something like the following:
IF @RowCount = 1
BEGIN
SET @Plural = ""
END ELSE
BEGIN
SET @Plural = 's'
END
Now we have set our Plural variable we can now go back and create the expression for the MessageSource variable as the following:
"The successful process imported " + (DT_STR, 10, 1252) @[User::RowCount] + " record" + @[User::Plural] + ".\n\nRegards,\nYour Helpful DBA”
If you click on the "Evaluate Expression" box this will return:
The successful process imported 2 records.
Regards,
Your Helpful DBA
To test that the plural aspect works change the value of the variable RowCount to 1 and evaluate MessageSource again, this time the results will be:
The successful process imported 1 record.
Regards,
Your Helpful DBA
There we have it, if you haven’t used variables as expressions before then it’s a very useful lesson as expressions are the building blocks for practically everything in SSIS, once you’ve mastered this simple concept you will find that everything becomes much easier.