Search code examples
sql-serverssisssis-2012

Redirect NULL or blank values from Flat File

I am importing records from a flat file source to a SQL table which has 4 columns which do not accept NULL values. And what I would like to do is redirect the records which contain a NULL or blank value for the particular 4 fields to a flat file destination.

Below you can see the table configuration:

Table Structure

And here is a sample from my flat file source where I have blanked out the county_code in the first record, the UCN in the second record, and the action_id in the third.

enter image description here

If I run my package as it is currently configured, it errors out due to the constraints:

The column status returned was: "The value violated the integrity constraints for the column.".

So my question is how to I redirect these rows? I think I should do a conditional split, but I am not certain and further I don't know how I would configure that as well. My attempts have been futile so far.

Any suggestions?

Solution

  • Add a Derived Column Transformation after your Flat File Source. There you'll test whether the not nullable columns are null.

    For ease of debugging, I would add a flag for each of those columns in question.

    null_timestamp (ISNULL(timestamp) || LEN(RTRIM(timestamp)) == 0) ? true : false

    An expression like this will determine whether the column from flat file is null or whether the trimmed length is zero.

    Once you have your flags tested, then you'd add in a Conditional Split. The conditional split routes rows based on a boolean expression. I would add a Bad Data output to it and use an expression like

    null_timestamp || null_country_code || null_etc

    Because they are boolean, if we OR the values together if any of those were to be true, then the whole expression becomes true and rows are routed to the bad data path.