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

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_INDUCEDT开发者_StackOverflowRANSFORMFAILUREONERROR. 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.

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))

Presumably the data you are trying to import is different today. Is there a problem with your text file data source today that wasn't there yesterday?

The transformations above do not take into account NULLs. Check to see if [PolicyExpire] has a NULL value in one of the records.