Data conversion in SSIS

Question

Post reply

Data conversion in SSIS

10e5x

Default port

Points: 1445
More actions
December 6, 2012 at 7:54 am

#271387

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

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?

10e5x Default port Points: 1445 More actions · Answer 2

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:)

10e5x Default port Points: 1445 More actions · Answer 3

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.

Ray M SSC-Insane Points: 21093 More actions · Answer 4

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

10e5x Default port Points: 1445 More actions · Answer 5

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.

10e5x Default port Points: 1445 More actions · Answer 6

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

Ray M SSC-Insane Points: 21093 More actions · Answer 7

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)