Tip: Dealing With ASCII Date 00000000

Thought I would share this since it caused me so much grief.

In some mainframe systems, some dates are stored as the string "00000000". In my SSIS package, I was trying to anticipate for this string, as well as any other combination of zeros (e.g., "000", "0000", etc), since I had already seen lots of dirty data in the flat file (like non-printing characters, etc).

So, what I tried to do was perform an integer conversion on the string and test if it was the equivalent of the numerical value zero:






Code Snippet

(DT_STR)[ColumnName] == 0 ? ....
Now, for some reason, that doesn't work, even though a similar operation in SQL does work:





Code Snippet

SELECT TOP 1 ISNUMERIC('00000000') FROM tableName

In the end, I had to resort to testing for a match on the literal string "00000000" and hope that no other dates came in as "000" or other variation. Fortunately, this has been true so far.

However, the moral of the story is, converting a series of zeros into a numeric zero, and testing against that, does not seem to work. I don't have a good explanation for why that is, but I would guess it has something to do with the limitation of the conversion function.

Happy integrating.