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 12»»

How to convert YYYYMMDD to datetime using SSIS Data Conversion? Expand / Collapse
Author
Message
Posted Wednesday, November 26, 2008 11:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:37 AM
Points: 4, Visits: 74
I have many *.csv files with a column value of YYYYMMDD. I need to import the string YYYYMMDD to a datetime column in a table. I expect to see the value in the datetime column in the table as "2008-06-03 00:00:00.000" for example.

I created a SSIS package and added Data Conversion between Flat File Source and OLE DB Destination on Data Flow designer. I have tried the all four Data Types related to date like [DT_DBDATE] ... to [DT_DATE] on Data Conversion Editor.

None of them are working but return error message saying:
"[Data Conversion [822]] Error: Data conversion failed while converting column "InvoiceDate" (35) to column "InvoiceDate" (835). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". "

If you execute the simple select statement "select convert(datetime, '20080603') as n", it will return
the result I want "2008-06-03 00:00:00.000". So, I'm sure that YYYYMMDD could be convertible to datetime format.

Do you know how to solve the converstion problem using SSIS?

Thank you for your expertise help.

Helpwanted
Post #609337
Posted Wednesday, November 26, 2008 11:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:59 AM
Points: 405, Visits: 1,139
SSIS is not based on T-SQL, so you can't use T-SQL staments in the expression editor.

I think to acheive the result you want, you will have to break apart the source date (YYYYMMDD) and contruct the date yourself

(you'll have to check the syntax on this, but hopely you'll get the gist of what its doing)

SUBSTRING(0,4, {sourceDate} ) + '-' + SUBSTRING(5,2, {sourceDate} ) + '-' + SUBSTRING(7,2, {sourceDate} ) + ' 00:00:00'

output that to a DT_DBTIMESTAMP data type.

let me know if that works


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #609348
Posted Wednesday, November 26, 2008 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:37 AM
Points: 4, Visits: 74
Hi, Dave

Thank you for reply with suggestion. I understood that the T-SQL statement cannot be used on Data Conversion editor as the editor has no option to take it. Like I said the editor only provides 4 drop-menu options of DATE types. I have found a solution to do that using Derive Column Transformation instead of Data Conversion.

But Data Conversion should do as its name says ..... data type conversion from simple string YYYYMMDD to datetime, just like the result from the T-SQL statement.

I'm so curious why Data Conversion fails to do that. Anyway, thank you all of you. But I still want to hear from you if knows how to get the result from the string YYYYMMDD to "2008-06-03 00:00:00.000" using DATA Conversion?

Thank you!
HelpWanted

Post #609392
Posted Wednesday, November 26, 2008 9:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
The data conversion is failing because there's bad data in that column somewhere in the file. You need to find out what the bad data/date is and fix it in the file.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #609549
Posted Wednesday, November 26, 2008 9:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
Either that, or you're mapping doesn't actually equal what's in the file. Could be something stupid like a stray comma in some name in the file.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #609550
Posted Monday, February 9, 2009 3:44 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:27 AM
Points: 277, Visits: 618
Sorry to revive an old thread but I'm having the exact same problem and didn't see that a final 'Hey - this worked!' was posted.

I double-checked all my data and it's all in YYYYMMDD format.

I have the source set as a string.

I've tried breaking it out into MM-DD-YYYY and other permutatations.

In no way can I get this value to convert to a datetime.

One oddity here. I have the source set as a non-unicode string. When I tried to pull the year from the field I received an error that the YEAR function does not support data type of "DT_WSTR" and the source truly is set to be DT_STR.

Thoughts?
Post #653292
Posted Wednesday, February 18, 2009 10:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 30, 2014 8:27 AM
Points: 277, Visits: 618
OK - here's the short answer on how I did this.

Create a Derived Column transformation, create a new column, and put this in the expression field. Use your own field name, obviously.

(DT_DATE)(SUBSTRING([field],1,4) + "-" + SUBSTRING([field],5,2) + "-" + SUBSTRING([field],7,2))

Personally I name the derived column as CONV_[field] just so I know it's a converted field and what field I converted. Use whatever naming convention you like.

Use the derived column in your destination mapping.

Longer answer.

Make sure you have your source coming in as a [DT_STR] data type.

Even longer answer.

If you're setting this up for the first time and creating the table, here's the way I found it most convenient to get things correct in the long run.

When creating your flat file data connection set the data type to [DT_DBTIMESTAMP] for all the fields that have the YYYYMMDD format. After you get all the fields properly formatted create an OLE DB destination and connect the two. Make sure you have your OLE DB connection manager in place. Select the connection manager and next to the field for 'Name of the table or the view' click on the New button. You'll get a window with the code to create the table.

Personally, I copy this code and bring it over to the Management Studio and put it in a query window. Put in your schema, desired table name, add the values for your decimal fields, etc. Run the query to create the table.

Now go back and edit the flat file connection manager. Change all the date fields to [DT_STR] and change the output column width value to match the input column width, as the value will default to 50 when you change the data type.

Disconnect the source from the destination and then put your transformations in place. Convert the date values as above and when you finally do connect the destination change your mapping to the converted/derived column.

Hope this helps.
Post #659626
Posted Wednesday, March 11, 2009 7:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 35,770, Visits: 32,435
Heh... thanks for posting your solution. I guess that's why I don't like DTS or SSIS... T-SQL makes it so much simpler...

DECLARE @SomeDate VARCHAR(8)
SET @SomeDate = '20090311'

SELECT CAST(@SomeDate AS DATETIME) AS DateTimeDataType



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #673874
Posted Tuesday, August 24, 2010 11:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 24, 2010 11:10 PM
Points: 1, Visits: 0
thanks MrsPost!!! that works
Post #974629
Posted Tuesday, August 24, 2010 11:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 24, 2010 11:10 PM
Points: 1, Visits: 0
thanks MrsPost!!! that works
Post #974630
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse