March 18, 2008 at 8:56 pm
Hello,
I have a source table with a date field that is a char(26). I am loading this to a destination table with field type datetime, and I am transforming this in my SSIS package.
My issue comes up in finding that the source field contains different formats, for example:
1/1/2001 12:00:00 AM and 2008-03-01 24:00:00
Using SSIS, I have set up a Data Flow Task with the source task, destination task, and a derived column transformation task in between them. If I use a simple (DT_DBTIMESTAMP)Prev_Appt_TS Expression in my Derived Column Transformation, the rows with date format 2008-03-01 24:00:00 do not get transformed and therefore the process errors out.
I am trying to figure out how to convert this source field with the different datatypes to one datetime destination field.
Thanks for your help! 🙂
March 18, 2008 at 11:11 pm
It has everything to do with the time portion of '2008-03-01 24:00:00'
24:00:00 is no such value for a DATETIME Data Type. 24 needs to be 00
The way I would tackle this problem is to replace the value of 24 to 00 in the source Table and then you will be able to convert it to a DATETIME Data Type.
March 26, 2008 at 4:05 pm
Thanks for your response. That makes sense, since I've found that if I leave off the time piece it converts just fine. However, I cannot alter the source data, and there are about 50 of these fields with char/datetime conversions that are happening, and the refresh is being pulled nightly (source then destination being updated nightly). Any other ideas?
March 26, 2008 at 4:28 pm
Since you're using a derived column transformation before throwing it into the destination, that's the place you can tackle dropping out the invalid time markers. Same as described before - remove/replace all instances of 24 in the hour column, and THEN convert it.
Otherwise - capture the errors (the ones that fail the conversion), and THEN run a separate converion process on just the date portion, and insert THAT into the destination as well
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply