Set Date Format on Flat File Source

Question

Set Date Format on Flat File Source

Ted Zatopek Old

Ten Centuries

Points: 1299
More actions
November 18, 2008 at 2:28 pm

#127730

Is there a way to set the format of date fields in a flat file source in SSIS?
I've got many *.csv files that I'm needing to import and the date fields come in as YYYYMMDD. These are formats are unfortunately actually set in legal agreements and cannot be changed at the source.
I know that we could define the date fields as character and then change the datatype in a Data Conversion transformation, but there are hundreds of fields in these files and it would be much easier if I could just set a date format in the source files.
Thanks in advance for any help.

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

If you set a column as a DATETIME datatype, and the data put into that column is in the YYYYMMDD format, no conversion is necessary... it'll just work because of implicit conversons from the character based ISO format.

Of course, the proof is in the code...

CREATE TABLE dbo.SomeDateTable

(

RowNum INT IDENTITY(1,1),

SomeDateColumn DATETIME

)

INSERT INTO dbo.SomeDateTable (SomeDateColumn)

SELECT '20081118' UNION ALL

SELECT '20000101' UNION ALL

SELECT '19630704'

SELECT * FROM SomeDateTable

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 2

That what I would have expected also. However, the date fields are loading with an error (-1071607676 The data value cannot be converted for reasons other than sign mismatch or data overflow. ).

Ted Zatopek Old Ten Centuries Points: 1299 More actions · Answer 3

After lots of searching, I fanally ran across the term "fast parse" in an article and found the following MSDN article.

http://msdn.microsoft.com/en-us/library/ms139833(SQL.90).aspx

This seems to be just the trick I need in order to load date fields formatted as YYMMDD. I've tried it on one of the fields, and it loads without an issue.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Ted Zatopek (11/19/2008)
After lots of searching, I fanally ran across the term "fast parse" in an article and found the following MSDN article.
http://msdn.microsoft.com/en-us/library/ms139833(SQL.90).aspx
This seems to be just the trick I need in order to load date fields formatted as YYMMDD. I've tried it on one of the fields, and it loads without an issue.

Thank you for the link... makes me like ol' school BULK INSERT and BCP even more. 🙂

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)