SSIS redirect empty rows as flat file source read errors

Question

I'm struggling to find a built-in way to redirect empty rows as flat file source read errors in SSIS (without resorting to a custom script task).

as an example, you could have a source file with an empty row in the middle of it:

DATE,CURRENCY_NAME
2017-13-04,"US Dollar"
2017-11-04,"Pound Sterling"

2017-11-04,"Aus Dollar"

and your column types defined as:

DATE: database time [DT_DBTIME]
CURRENCY_NAME: string [DT_STR]

with all that, package still runs and takes the empty row all the way to destination where it, naturally fails. I was to be able to catch it early and identify as a source read failure. Is it possible w/o a script task? A simple derived column perhaps but I would prefer if this could be configured at the Connection Manager / Flat File Source level.

Use a conditional split where date equal blank "" to redirect empty rows. — SqlKindaGuy

Raul Sebastian Raul Sebastian · Accepted Answer · 2018-01-23T12:27:50

The only way to not rely on a script task is to define your source flat file with only one varchar(max) column, chose a delimiter that is never used within and write all the content into a SQL Server staging table. You can then clean those empty lines and parse the rest to a relational output using SQL.

This approach is not very clean and a takes lot more effort than using a script task to dump empty lines or ones not matching a pattern. It isn't that hard to create a transformation with the script component

This being said, my advise is to document a clear interface description and distribute it to all clients using your interface. Handle all files that throw an error while reading the flat file and send a mail with the file to the responsible client with information that it doesn't follow the interface rules and needs to be fixed.

Just imagine the flat file is manually generated, even worse using something like excel, you will struggle with wrong file encoding, missing columns, non ascii characters, wrong date format etc. You will be working on handling all exceptions caused by quality issues.

SSIS redirect empty rows as flat file source read errors

2 Answers