CODEWITHSUNDEEP
sql-serverdatessis
Ashwin Shankar
Ashwin Shankar

Reputation: 21

Date format issue while importing from a flat file to an SQL database

I Created an SSIS package where the Data is taken in from a flat flie and is transferred to the database.

But this had the confusing and hilarious effect of interpreting dates inconsistently. Any dates, where the day was greater than the 12th of the month would be correctly interpreted as dd/mm/yyyy but anything less than that would be backwards.

So 02/15/2012 would be correctly read as the 15th of Feb 2012.

But 02/09/2012 would happily be interpreted as 2nd of Sep 2012.

Upvotes: 2

Views: 17044

Answers (1)

Casual Pundit
Casual Pundit

Reputation: 279

This issue is common and I have encountered it many times.

First thing, check if your SSIS Locale is not set to United States.
You need to set it to English(United Kindom) or Dutch (Netherlands).
To do that click anywhere in your SSIS package and view properties and set LocaleID (under Misc). In Connection Managers, select the flat_source_file Connection and edit the LocaleID in the properties panel. If that doesn't help then do the following:

I would set the source column in your 'Flat File Source' to string datatype.
Then use a 'Data Conversion' transformation to cast the date column as either DT_DATE or DT_DBDate.

DT_DATE is a date structure that consists of year, month, day, and hour.
DT_DBDATE is a date structure that consists of year, month, and day.

Now use the new transformed column in the destination window.

Upvotes: 1

Related Questions