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

ssis ssis-data-types
Snow_Mac picture Snow_Mac · Jun 21, 2011 · Viewed 10.7k times · Source

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.

Answer

TimG picture TimG · Jul 19, 2012

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 questions

What variable type use to represent a Money in SSIS

I need to load a Money value in a variable in SSIS using a "Execute SQL Task" component. I map the return column of a SELECT to a variable. I've declared the variable "UnknownMoney" as a Double or a Single, …

Read More
How to pass variable as a parameter in Execute SQL Task SSIS?

I have ssis package in that I'm taking values from flat file and insert it into table. I have taken one Execute SQL Task in that creating one temptable CREATE TABLE [tempdb].dbo.##temptable ( date datetime, companyname nvarchar(50), price decimal(10,0), …

Read More
SSIS Convert Between Unicode and Non-Unicode Error

I have an ssis package where I am using an OLEDB source linking to SQL Server 2005 table. All columns except a date column are NVARCHAR(255). I am using an Excel destination and using a SQL statement to create the sheet …

Read More