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


How to convert YYYYMMDD to datetime using SSIS Data Conversion?


How to convert YYYYMMDD to datetime using SSIS Data Conversion?

Author
Message
pickgoods
pickgoods
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 78
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
dave-dj
dave-dj
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2706 Visits: 1149
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)
pickgoods
pickgoods
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 78
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219446 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219446 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
JustMarie
JustMarie
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2189 Visits: 1362
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?
JustMarie
JustMarie
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2189 Visits: 1362
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219446 Visits: 42002
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
yinchashe
yinchashe
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 0
thanks MrsPost!!! that works
yinchashe
yinchashe
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 0
thanks MrsPost!!! that works
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