Derived Column Transformation

Hello.
I am using Derived Column transformation for calculating the age of individual and then adding the column to my final destination. In SQL, the DOB is varchar(50) and the output column I am creating should be Integer.
Here is the expression I am using for calculating the age:
(DATEDIFF("DAY",(DT_DBTIMESTAMP)TRIM(DOB),(DT_DBTIMESTAMP)TRIM([Service Date])) / 365.25)

In SQL, I have no problems getting the age of a person, but I am having difficulties using Derived Column Transformation.
I get the following error when executing my package:

Error: 0xC0049067 at Data Flow Task, Derived Column [2086]: An error occurred while evaluating the function.
Error: 0xC0209029 at Data Flow Task, Derived Column [2086]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (2086)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (2877)" 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.

Any assistnace would be greatly appreciated.
I've come across a similar issue before. It seems that derived column transformation doesn't like converting char or integer data to dt_dbtimestamp. The way I got around it was using a lookup to a date dimension on the date in char format and pulling back the corresponding datetime format for the record. Is this a possibility?|||

Thank you for your reply.

I guess there are other options I can choose from. However, since this is a SSIS component, I would like to make it work and find out why is it so difficult to deal with. I understand that the problem is data type, nevertheless, the data is already in SQL and it is using SQL data type. If I were importing data from a flat file, I would accept the fact that data type could be more of an issue.

When I run my SQL query (please see two versions), it works like a charm.:

select convert(int,datediff("dd", convert(datetime,DOB),getdate())/365.25) FROM [DATABASE].[dbo].[TABLENAME]

OR

select cast(datediff("day",cast(DOB as datetime),cast([Service Date] as datetime))/365.25 as int) from FROM [DATABASE].[dbo].[TABLENAME]

|||

What format is your date field thats stored as char?

I just tried '20070101' and it didn't work but '01012007' works

|||

Hi there:
Once again, thank you for your reply.

The format I use in SQL is YYYYMMDD; Example: 19900925

|||

Try converting it around with some string functions...

Code Snippet

(DT_DBTIMESTAMP)(RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4))

|||

Hi there:
I will try your suggestion and I will let you know.

|||

Hi there:

I did tried your suggestion. Here is what I wrote:

(DATEDIFF("DAY",(DT_DBTIMESTAMP)(SUBSTRING(DOB,7,2) + SUBSTRING(DOB,5,2) + SUBSTRING(DOB,1,4)),(DT_DBTIMESTAMP)(SUBSTRING([Service Date],7,2) + SUBSTRING([Service Date],5,2) + SUBSTRING([Service Date],1,4))) / 365.25)

I am still having the same error:

Error: 0xC0049067 at Data Flow Task, Derived Column [3949]: An error occurred while evaluating the function.

Error: 0xC0209029 at Data Flow Task, Derived Column [3949]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (3949)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "_AGE" (4008)" 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.

|||

Alright, just did some more testing. For some reason you can't go from char to datetime, you have to go from char to int to datetime. Also, the int format has to be mmddyyyy

Code Snippet

(DT_DBTIMESTAMP)((DT_I4)((DT_STR,8,1252)RIGHT(testDate,2) + SUBSTRING(testDate,5,2) + SUBSTRING(testDate,1,4)))

|||

Hi there:
Unfortunately I tried your last suggestion and I still cannot process the package. I did put a data viewer between my SQL DB Source and the Derived Column and the DOB format comes as 19900921 which is what I have in my DB.

I agree with you that there is something with the conversion that is not working.

I tried the following cast in SQL using the following format(ddmmyyyy):

select cast('20092007'as datetime)--The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I wonder if the way CAST works is what is causing the problem.

By the way, thank you for assisting me. Appreciate your time and effort

|||

I used the following in a variable and it worked:

(DT_I8) (DATEDIFF("DAY",(DT_DBTIMESTAMP) @.[User::CurrentDate], (DT_DBTIMESTAMP) @.[User::QuotaStartDt] )/365 )

The variables I pass into the expression are in the standard Datetime format. The result I get is a 0 but it did not give me any errors.

|||Hi there:
Thank you for your suggestion.

My DOB string is formatted as: yyyymmdd and casting a string to datetime using this format works fine in SQL. If the format is ddmmyyyy, then casting does not work at all, unless I cast the string several times.

I tried using substring, casting etc and I have not seen any positive result. I think I will perform the age calculation in sql and added to my query. Too bad something simple gets so complicated....!

I would like to thank you all for your time and effort.

sql