SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using the Error Output of the Derived Column

The other day I was building a simple data import package when I ran into a common situation; invalid conversions in a Derived Column.  The conversion was taking an ISBN13 which is usually defined as char(13) and converting it into a bigint.  All valid ISBN13s are valid bigint.  The flat file source I was importing had invalid ISBNs in it, the first set of which where simply blank.  I was about to modify my derived column to shunt all blank ISBNs to 0 when it turns out there is a second set of bad data; invalid ISBNs.  These are either ISBN10s, which contain a letter in the 10th position, or plain character garbage.  This changes the whole plan of attack.

The simple derived column was going to turn into a nightmare of nested if/then constructs to convert those invalid values into a 0.  To try and capture a sample of all invalid ISBNs, I modified the Data Flow as follows:

The error output is on Failure, not truncation, because I want to see all invalid data and I know this is more than just numbers that are too large for the data type.  Then it hit me.  There right in front of me was the solution.  With a little tweaking:

I know the Error Output is an invalid ISBN and I want that to be converted to a 0.  Open up the Invalid ISBN component and you simply see this:

There you go.  If you have the potential for numerous invalid values in a specific column, rather than build complex Derived Column formulas, build the one formula that is valid, shunt the invalid records to another data path and put your default values in.


This blog is syndicated from SSIS - SQL Server Tidbits(http://www.josefrichberg.com/)


Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...