Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data conversion in SSIS Expand / Collapse
Author
Message
Posted Thursday, December 6, 2012 7:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi everyone,
I am newbie to SSIS, just picked that up ytd. Currently i am given a sample view table to work with. However the EVENT_D and EVENT_T field are in text data type in my access 2003. Its a text field in DD/MM/YYYY HH:MM:SS format respectively. Can anyone guide me how to convert this two field into date and time format respectively in SSIS?

The second qns is how can i concat this two field into one derived column EVENT_DT as datetime in DD?MM?YYYY HH:MM:SS format?

Any help are greatly appreciated.

Thanks
Post #1393533
Posted Thursday, December 6, 2012 8:39 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 1,485, Visits: 1,035
This is an easy and common problem.

In our dataflow task, you need to add the Derived column transformation connected to the Data Flow Source.
(Between your dataflow source and data flow destination.)
And Either Add a new column, or replace existing. and in the expression column of the Derived column transformation you just need to add a type Cast function in front of the field Name
IE (DT_DBDATE)EVENT_D
Thats It.

Here's a tutorial on how to use the Derived column Transformation.
http://www.dotnetfunda.com/articles/article1233-use-of-derived-column-in-ssis.aspx

Since dates are stored in Text what are the chances that some of the data is not in an actual date?

Post #1393566
Posted Thursday, December 6, 2012 5:41 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi,
Thanks for replying. However datas are recorded in DD/MM/YYYY format. e.g. 02/05/2012 are to be understood as 2nd of may 2012.
After this link, http://msdn.microsoft.com/en-us/library/ms141036.aspx
i am getting confused of the date and time and date-time data types.
Haha kindly please help to clear my confusion.

Thanks, appreciated:)
Post #1393810
Posted Friday, December 7, 2012 2:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi Ray M,
As to ans your concern, there will be no invalid dates in the EVENT_D text field as up till now. Assurance are made at client side(presentation layer).
Haha i know it may be simple, but i am too noob
I shall do one step by a time. Lets say now i want to convert my EVENT_D and EVENT_T field to date and time field in SSIS, how shall i do it?

NOTE: EVENT_D values in source are in : DD/MM/YYYY format (02/05/2012 to be read as 2nd May 2012)
EVENT_T values in source are in : HH:MM:SS format eg. 22:10:07

My question:
I do know there is a data conversion in SSIS. I should be using that first right?(in between my source and destination). My concern is which data type should i choose? and will the converted values stayed as what it meant? DD/MM/YYYY and HH:MM:SS?
Please guide me:)

Thanks.
Post #1393937
Posted Friday, December 7, 2012 10:45 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 1,485, Visits: 1,035
My First question would be what have you tried, and where are you getting stuck.
I gave you your first Clue.

I suspect your next issue will be that since its DD/MM/YYYY formatted your data will not be correct so you'll need to do some string manipulation.

Here's a nice article
http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html#!/2008/11/converting-strings-to-dates-in-derived.html

But doesn't necessarily give you your answer. Clue use Substring to re-format the string into MM/DD/YYYY (Or YYYYY-MM-DD and concatenate your time value by adding + " " + EVENT_T

Post #1394166
Posted Saturday, December 8, 2012 10:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Ray M (12/7/2012)
My First question would be what have you tried, and where are you getting stuck.
I gave you your first Clue.

I suspect your next issue will be that since its DD/MM/YYYY formatted your data will not be correct so you'll need to do some string manipulation.

Here's a nice article
http://toddmcdermid.blogspot.com/2008/11/converting-strings-to-dates-in-derived.html#!/2008/11/converting-strings-to-dates-in-derived.html

But doesn't necessarily give you your answer. Clue use Substring to re-format the string into MM/DD/YYYY (Or YYYYY-MM-DD and concatenate your time value by adding + " " + EVENT_T



Thanks the link u posted helps! I get where u are coming from. Wonderful work. As for where am i stuck? Now currently i am still at the understanding of my source data and designing my target table phase. I post this qns becuz i am worried of the data type conversion. . I understood my data but currently i am now stuck at how to derive my target table(the logic). It just seems impossible to do it through SSIS. Some1 told me make use of staging tables, I read on that but still clueless. Do you want to take a look?? or should i start a new thread?

Thanks for being so helpful. At least now i know, my date and time column could be converted.

Post #1394381
Posted Sunday, December 9, 2012 10:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, February 2, 2013 11:09 AM
Points: 97, Visits: 180
Hi ray,
I tried what u suggested on my own but i got this error:

SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC0049064 at Data Flow Task, Derived Column [70]: An error occurred while attempting to perform a type cast.
Error: 0xC0209029 at Data Flow Task, Derived Column [70]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (70)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "EVENT_DT" (118)" 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.
Error: 0xC0047022 at Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (70) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (143)" wrote 0 rows.
Task failed: Data Flow Task
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.


My expression is:
(DT_DBTIMESTAMP)(SUBSTRING(EVENT_D,7,4) + "-" + SUBSTRING(EVENT_D,4,2) + "-" + SUBSTRING(EVENT_D,1,2) + EVENT_T)

My original data are in this sequence:
EVENT_D: DD/MM/YYYY
EVENT_T: HH:MM:SS

May u guide me? THanks
Post #1394453
Posted Monday, December 10, 2012 8:23 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Yesterday @ 3:52 PM
Points: 1,485, Visits: 1,035
I think all you need to do is add a space between your date and time.

(DT_DBTIMESTAMP)(SUBSTRING(EVENT_D,7,4) + "-" + SUBSTRING(EVENT_D,4,2) + "-" + SUBSTRING(EVENT_D,1,2) + " " + EVENT_T)
Post #1394648
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse