Logging/Emailing skipped records in SSIS Package

Question

Using SQL 2008 R2, I've created an SSIS Package that rips through a flat file and imports them into a SQL table.

If any record in the data file does not contain all the required fields, that record should be skipped in the import process. All skipped records should be emailed to me when the package completes.

Here's the data file structure:
123|ABC|Y|Y
784
456|DEF|Y|Y
789|GHI|Y|N
812||Y|N
...

So, in this scenario, I would want the 1st, 3rd, and 4th record to be imported, and the 2nd and 5th record to be skipped and emailed.

I tried testing this out as is, and since it looks for a pipe delimiter, it reads the second line together with the third as:

784456|DEF|Y|Y

I'm about 3 days old working with SSIS, so if someone can assist me in accomplishing this task, I'd be grateful.

Answer 1

Since it's merging the second line with the third, it sounds like either the row delimiter is incorrect on line 3 or it's not set correctly in the connection manager. I'd take a look at the file in Notepad ++ (or a text editor that will expose hidden characters like Cr and Lf) and verify that the row delimiter is consistent for each row and that it matches what's been set in the connection manager.

Once the row delimiter issue is straightened out, you can separate the erroneous records with a conditional split. Under condition, type [YourColumnName] == "" and under Output name, type Error. Name the default output name "Correct". Now map the "Correct" output to your table and map the "Error" output to a flat file, script component, table, or whatever format you want the errors to go to.

Answer 2

How big are the files? One way, is to use a staging table. NOT a temporary table.. The staging table is a physical table that retains its existence in the database. You dump all records there, then insert the good data into the production/main table, then export the bad rows into a file which you can append to the sendmail task..

(then you can truncate the staging table for the next interval/run/loop/file)

Another way would be to use conditional splits, and then set each row to a variable which then has a format applied to it, appending a delimiter other than a pipe, then into the export file.