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

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 开发者_StackOverflow社区datetime field can be specified, maybe somewhere in the package or some setting on the production server itself?

Thanks in advance, Tim

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.