SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data conversion in SSIS


Data conversion in SSIS

Author
Message
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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
Ray M
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 1076
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
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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, appreciatedSmile
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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 noobSad
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 meSmile

Thanks.
Ray M
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 1076
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
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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.
10e5x
10e5x
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 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
Ray M
Ray M
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2133 Visits: 1076
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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search