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.