SSIS Derived Column YYYYMMDD to MM/DD/YYYY with Nulls -- sql server dts

Zach.Baier
9/11/2007 6:44:00 AM

I have a fixed length (ragged right) flat file with several dates in the
format YYYYMMDD which I am trying to load into SQL smalldatetime fields.

I have set the input for the fileds at a width of 8 (dt_Str) and output
width of 10.
I have added a single derived column data flow element and specifed a
replace field for each of the date columns with an expression like

LEN(TRIM(CloseDate)) == 0 ? NULL(DT_DATE) :
(DT_DATE)(SUBSTRING(CloseDate,5,2) + "/" + SUBSTRING(CloseDate,7,2) + "/" +
SUBSTRING(CloseDate,1,4))

The issue is that on some records, spaces come through as null (what I want)
on others it inserts the default date (1900-01-01 00:00:00). The date fields
all allow nulls. It the results seem random.

jhofmeyr NO[at]SPAM googlemail.com
9/12/2007 5:15:19 AM

On 11 Sep, 14:44, Zach.Baier <ZachBa...@discussions.microsoft.com>
[quoted text, click to view]

Hi Zach,

Have you confirmed that there is no difference between the values that
are correctly converting to NULL and the ones that are coming through
as the default date? You may find that some are spaces while others
have a different value (maybe another special character that is not
eliminated by TRIM) Instead of replacing the column, try adding the
converted date as a new column and leaving the original one unchanged,
or add an additional column setting it to LEN(TRIM(CloseDate)) to test
the if condition.

Good Luck!
J

sql server dts : SSIS Derived Column YYYYMMDD to MM/DD/YYYY with Nulls