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.
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.