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 ...
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply