question

vijayakarsh avatar image
vijayakarsh asked

Cast dd Mon YYYY to mm/dd/yyyy in SSIS

I have a source file which has a Data column as ddMonyyyy(10Mar2013), I need to convert this during my transformation, I can populate this into a staging and then do a convert on the column but I want to do it through SSIS, is there a way? Is using Script task the only way, if yes how can this be done.
ssiscast-convert
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You really should be using ISO 8601 date format. http://en.wikipedia.org/wiki/ISO_8601
1 Like 1 ·
KenJ avatar image
KenJ answered
You can use a [derived column transformation][1] then reformat your date with the expression: datepart("year", yourcolumn) + datepart("month", yourcolumn) + etc... **-- Added based on follow up --** Since your source column is in a string format that SSIS cannot directly cast as a `DT_DATE` you'll need your derived column expression to massage the string prior to the cast by inserting some hyphens between the parts of the date: (DT_DATE)(SUBSTRING(datestring,1,2) + "-" + SUBSTRING(datestring,3,3) + "-" + SUBSTRING(datestring,6,4)) This assumes that you always have a three character month. Adjust as necessary :) After you do this, you can use the datepart approach outlined above (or just leave it as a date and it can be manipulated any number of ways once you have it in the database). [1]: http://msdn.microsoft.com/en-us/library/ms141069.aspx
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The issue here is the source column is a sting data type, when I try to change the data type in the Flat File Editor, I get an error stating that the data will be truncated. I used the above the solution with data type conversion in the derived column DATEPART("Year",(DT_DATE)DOB) + DATEPART("Month",(DT_DATE)DOB) + DATEPART("Day",(DT_DATE)DOB). This will not work as the type conversion will not processm when I try this I get a error An error occured while evaluating teh function
0 Likes 0 ·
udhaya avatar image
udhaya answered
Refer this link. It has more date functions [Date Formats][1] [1]: http://www.sql-server-helper.com/tips/date-formats.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.