SSIS converts .csv date field to incorrect format on production SQL server

3
votes

I am using SSIS 2005 to read data off a .csv file into a SQL Server 2005 Database. I am using a Flat File connection manager for the .csv and an OLEDB Connection Manager for the resulting rows.

The .csv file contains a field which is a date in UK format (dd/mm/yyyy), which may be written, say, 7/3/2011 for 7th March 2011. This column is then mapped onto the equivalent datetime field in the SQL Server database.

The problem I am facing is that, while everything works fine on the development machine, when used on the production environment, the dates get changed to US (mm/dd/yyyy) format (if valid), when they are inserted into SQL Server. Is there some place where the desired Region/Format (in this case UK) for the destination datetime field can be specified, maybe somewhere in the package or some setting on the production server itself?

Thanks in advance, Tim

1
sql-serverdatessisformatpackage
I would check regional settings on the servers. I have no direct knowledge that this matters, but I imaging if it's set for UK/Europe the date formatting would be different. – JNK
Thanks for your comment. It could be something to do with the profile that runs the job on the production server. I have added a script component to cast the string to a date and insert this into the datetime field. Hopefully this should work irrespective of the regional settings. – TMM

1 Answers

3
votes

You can set the locale in the Flat File Connection Manager Editor or LocaleID in the Properties box for the connection (same thing). Setting it to "English (United Kingdom)" will interpret the dates correctly, because SSIS uses the locale in the package or data flow, not the OS locale.

Note that you have to set the format at the source, not the destination: datetime is a binary format in MSSQL so dates aren't stored internally in a locale-specific format anyway.