SSIS 2008: Derived Column Transform a String to a Date Data Type

ssisssis-data-types

I had this SSIS package working yesterday and now I'm getting this error with no changes overnight.

Basically I'm getting a String that looks like: yyyymmdd, I need to transform it into a Date Data Type. So I take substrings, to get yyyy/mm/dd then cast it into a Date Type.

The Path:

Flat File Source —> Dervied Column —> All my SSIS package intergration/insertion tasks

Here are the Expressions: 

(DT_DATE)(SUBSTRING([PolicyExpire],1,4) + "/" + SUBSTRING([PolicyExpire],5,6) + "/" + SUBSTRING([PolicyExpire],7,8))
(DT_DATE)(SUBSTRING([BirthDate],1,4) + "/" + SUBSTRING([BirthDate],5,6) + "/" + SUBSTRING([BirthDate],7,8))
(DT_DATE)(SUBSTRING([DLIssueDate],1,4) + "/" + SUBSTRING([DLIssueDate],5,6) + "/" + SUBSTRING([DLIssueDate],7,8))

Here is the error:

Error: 0xC0049064 at Extract EXD data
from Flatfile into YD db 1, Derived
Column [3352]: An error occurred while
attempting to perform a type cast.

Error: 0xC0209029 at Extract EXD data
from Flatfile into YD db 1, Derived
Column [3352]: SSIS Error Code
DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "component "Derived Column"
(3352)" failed because error code
0xC0049064 occurred, and the error row
disposition on "input column
"PolicyExpire" (3368)" specifies
failure on error. An error occurred on
the specified object of the specified
component. There may be error
messages posted before this with more
information about the failure.

Error: 0xC0047022 at Extract EXD data
from Flatfile into YD db 1,
SSIS.Pipeline: SSIS Error Code
DTS_E_PROCESSINPUTFAILED. The
ProcessInput method on component
"Derived Column" (3352) failed with
error code 0xC0209029 while processing
input "Derived Column Input" (3353).
The identified component returned an
error from the ProcessInput method.
The error is specific to the
component, but the error is fatal and
will cause the Data Flow task to stop
running. There may be error messages
posted before this with more
information about the failure.

Best Answer

Your substring paramaters are incorrect for the month and day portions of the date.

For example, it should be

SUBSTRING([PolicyExpire],5,2)

instead of

SUBSTRING([PolicyExpire],5,6) to get the month value.

The third parameter of the substring function is the LENGTH of the substring to find (in this case 2), not the END POSITION of the substring.

Try this

(DT_DATE)(SUBSTRING([PolicyExpire],1,4) + "/" + SUBSTRING([PolicyExpire],5,2) + "/" + SUBSTRING([PolicyExpire],7,2))

(DT_DATE)(SUBSTRING([BirthDate],1,4) + "/" + SUBSTRING([BirthDate],5,2) + "/" + SUBSTRING([BirthDate],7,2))

(DT_DATE)(SUBSTRING([DLIssueDate],1,4) + "/" + SUBSTRING([DLIssueDate],5,2) + "/"  + SUBSTRING([DLIssueDate],7,2))
Related Topic