June 24, 2009 at 8:21 am
Hi,
How should I use the below code in derived column in SSIS.
IIF(TO_CHAR(CREATEDTS)='01/01/0001 00:00:00',to_date('01/01/1753 00:00:00'),CREATEDTS)
Below code gives error. Not sure how to check datetime format.
CREATEDTS =='01/01/0001 00:00:00'? '01/01/1753 00:00:00' : CREATEDTS
Please help.
June 24, 2009 at 10:11 pm
Can you explain what you are trying to do? Based on your code, I am not sure ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 24, 2009 at 10:54 pm
Hi,
IIF(TO_CHAR(CREATEDTS)='01/01/0001 00:00:00',to_date('01/01/1753 00:00:00'),CREATEDTS)
The above code works in informatica like below
IF CREATEDTS = '01/01/0001 00:00:00' THEN
CREATEDTS = '01/01/1753 00:00:00'
ELSE
CREATEDTS = CREATEDTS
END
How do u achieve this in SSIS?
June 25, 2009 at 12:28 am
What's the datatype of CREATEDTS - is it datetime or string?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 25, 2009 at 1:15 am
CREATEDTS is the colulmn from DB2 Server. This is in DATE Format.
June 25, 2009 at 1:52 am
I do not think that 1/1/0001 is a valid date in SSIS and that may be causing some issues for you.
I just tried to create a derived column as follows:
(DT_DATE)"0001/01/01"
but this is interpreted as 1/1/2001 when the package runs.
Can you put a data viewer on the source to check what is actually in that field when it comes into SSIS?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 25, 2009 at 3:09 am
Phil, You are right, In DB2 the least datetime is 01/01/0001 but in Sql Server '01/01/1753' .
Hence we are using the if else statement. My question is how to change the IIF informatica statement similar to SSIS. For example you can change the least datetime '01/01/001' to '01/01/2001'.
IIF(TO_CHAR(CREATEDTS)='01/01/2001 00:00:00',to_date('01/01/1753 00:00:00'),CREATEDTS)
--SSIS (The below logic is correct but not sure how to convert datetime format to string. In Informatica they used TO_CHAR
CREATEDTS =='01/01/0001 00:00:00' ? '01/01/1753 00:00:00' : CREATEDTS
June 25, 2009 at 4:53 am
But when I asked you what the datatype of CREATEDTS was, in SSIS, you said Date.
How can it ever work? It will fail before you even get a chance to process it in a formula.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 25, 2009 at 5:07 am
I dont thing it will fail if i change the date from '01/01/0001' to '01/01/1753'?
CREATEDT is the source column from DB2 TABLE.
I found the below equivalent option for IIF.
CREATEDTS =='01/01/0001 00:00:00'? '01/01/1753 00:00:00' : CREATEDTS
Thanks for your help..
June 25, 2009 at 5:44 am
I was not really any help at all, to be honest - I just asked a lot of (probably) annoying questions 🙂
Here's my point: if a date from another system is coming in to SSIS as a Date, and the Date datatype within SSIS does not support dates of 1/1/0001, it may not fail, but it should not succeed either, because your condition will never evaluate to true.
But it sounds like you have been successful, so I will shut up!
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply