Someone emailed me today asking:
I have an SSIS package that outputs data that can’t be inserted into destination tables into flat log files [JT: I presume he means that error rows are redirected to a flat file]
What is the easiest way to then after processing has completed:
A) Check to see whether there has been any data that could not be inserted into destination tables
B) Then email a recipient with the dirty data in question.
So basically he wants to capture erroring rows from the dataflow and then email them to himself but he only wants to send the email if there is at least one error row. This felt like a good candidate for a SSIS nugget, especially as I haven’t written one for ages.
I’ve set up a very simple data flow that manufactures 3 rows of dummy data using the Script Component and then passes it into a Derived Column Component which simulates an error on one of the rows by dividing by zero. I terminate the success output with a Union All component and the erroring rows get pushed into a flat file destination. Note that the location of the file is stored in a variable called @[User::ErrorRows_fileLocation] (that’s very important) and we use an expression on the ConnectionString property of our Flat File Connection Manager to set it to the value of @[User::ErrorRows_fileLocation].
An important thing here though is to notice that I am populating a variable called @[User::NumberOfErrorRows] with the number of rows in the error output using a Rowcount Component. Let’s take a look at that dataflow after execution:
You can see that we have three rows, 2 of them go down the ‘Success’ output of “DER Simulate an error by dividing by zero” whereas the remaining row goes down the error output. I push that erroring row through the Rowcount and into a file.
Thus, after execution of the dataflow we know the number of rows that have failed. We can use that value inside a conditional precedence constraint to determine whether to execute the Send Mail Task or not:
The Send Mail Task simply sends the file to a named recipient. We use an expression on the FileAttachments property of the Send Mail Task to attach the file that we created earlier:
That’s basically it. The package can be downloaded from here:
It can be downloaded and run as is, no need to configure it. Just make sure you have a c:\temp folder because that's where it tries to put the file. The Send Mail Task will fail because it doesn't know where the SMTP server is but that's OK because it doesn't detract from the main point of the demo. UPDATE: I forgot to say, this package was built on SQL Server 2008 RC0 so it won't run on SSIS 2005. Sorry! Does anyone fancy building the same in SSIS2005?
-Jamie