August 29, 2009 at 6:44 am
[font="Verdana"] Hi All,
I am new to SSIS - here is a problem I am facing:
I have a flatfile containing a column with datetime format [ yyyy-MM-dd hh:mm:ss.fff ]. While defining the flatfile connection manager I declared the column to be DT_DBTIMESTAMP.
All the records a going fine exept few which contains the hour part as single digit.
Example:
a record with AccessDateTime column value: 2002-04-12 1:54:50.000 fails but when I prefix a zero for hour part to look like this 2002-04-12 01:54:50.000 -- it works fine.
I wish to know - if there is a way to pass those records through?
From sqlserver database perspective both are the same - T-SQL update statement with or without prefixing the zero works the same.
Please could you kindly advise on any way to get those records through?
Thanks in advance!
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
August 29, 2009 at 10:54 pm
Hi Sabyasachi,
Here's one thought, although it may not a complete solution:
If you add a Conditional Split transformation, you can set up a test for successful coersion to DT_DBTIMESTAMP with the following condition:
!ISNULL((DT_DBTIMESTAMP)MyDateTime) || ISNULL((DT_DBTIMESTAMP)MyDateTime)
This statement logically says "If MyDateTime is NULL of MyDateTime is Not NULL." Also, set the Error Output to redirect failed rows.
If this is the only case in the Conditional Split and the date in MyDateTime will convert to a DT_DBTIMESTAMP, the row will fall out of the Case Output. If not, the row will be redirected.
Add a Derived Column Transformation and connect the Condition Split's error output to it. In the Derived Column editor, add a Replace MyDateTime function with the following Expression:
(DT_STR,4,1252)DATEPART("yyyy",(DT_Date)MyDateTime) + "-" + (LEN((DT_STR,2,1252)DATEPART("mm",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("mm",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("mm",(DT_Date)MyDateTime)) + "-" + (LEN((DT_STR,2,1252)DATEPART("dd",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("dd",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("dd",(DT_Date)MyDateTime)) + " " + (LEN((DT_STR,2,1252)DATEPART("hh",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("hh",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("hh",(DT_Date)MyDateTime)) + ":" + (LEN((DT_STR,2,1252)DATEPART("minute",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("minute",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("minute",(DT_Date)MyDateTime)) + ":" + (LEN((DT_STR,2,1252)DATEPART("ss",(DT_Date)MyDateTime)) == 1 ? "0" + (DT_STR,2,1252)DATEPART("ss",(DT_Date)MyDateTime) : (DT_STR,2,1252)DATEPART("ss",(DT_Date)MyDateTime)) + "." + RIGHT(MyDateTime,LEN(MyDateTime) - FINDSTRING(MyDateTime,".",1))
This expression tests for lengths of month, day, hour, minute, and second that contain only one character (digit, in this case). If it finds only one, it places a "0" in front of it.
Downstream of the Conditional Split and Derived Column transformations, add a Union All to bring the corrected MyDateTime values back into the data stream along with the output from the Conditional Split's Case.
Hope this helps.
:{> Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
August 30, 2009 at 12:26 am
Thank you Andy - will try this out ...
[font="Comic Sans MS"]--
Sabya[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply