SSIS 2012 date formats dmy vs mdy

Question

There are three SQL Servers:

• PROD (2008 R2)
• NEW_TEST (2012)
• NEW_PROD (2012)

I am migrating a large number of SSIS packages from PROD to both NEW_TEST and NEW_PROD servers.

Source data comes from flat text files

Source DATE data is in the format of dd/mm/yyyy (i.e. 5th of November 2015 is stored as 05/11/2015).

In SSIS, the definition of the DATE source column (text file) is Unicode string (DT_WSTR) and the target column (in DB table) data type is DATETIME so a type conversion happens between reading the data from text file and writing it into the DB table.

When I run the package on the PROD (old) server, the data is loaded correctly.

When I run the same (but upgraded to 2012) package on the NEW_TEST server, the data loads OK, too.

However, when I run the package on the NEW_PROD server, the data loads incorrectly (i.e. 05/11/2015 is loaded as 11th of May 2015 instead of expected 5th of November 2015). So it seems that the NEW_PROD server somehow converts UK (DMY) source date string using US (MDY) settings.

After spending a significant amount of time trying to understand what is going on, this is what I discovered:

1. NEW_PROD server has collation set to "SQL_Latin1_General_CP1_CI_AS"
2. NEW_TEST server has collation of "Latin1_General_CI_AS" - this matches the current PROD server so it seems that the collation on the NEW_PROD is incorrect.
3. There are no other differences between server settings at the server level except for the one above.
4. The target database has collation set to Latin1_General_CI_AS on both NEW_* servers as well as on the current PROD server
5. When I run the package manually on my local machine, the data is loaded CORRECTLY regardless of target.
6. When I run the package from a scheduled job on the NEW_PROD server, the data is loaded INCORRECTLY.
7. Now, an interesting thing: when I run the package from a scheduled job on the NEW_TEST server BUT with target connection pointing to the NEW_PROD server, the data loads CORRECTLY
8. On all servers, the user that runs the SSIS service has default language set to British (langid = 23 in sys.syslanguages). The same applies for the user that owns the scheduled job.
9. When I change the source data type definition in the SSIS package from DR_WSTR to DATETIME, the data is loaded CORRECTLY regardless of where the package is executed.
10. When I add a data transformation between source and target, transforming that one column from DT_WSTR to DB DATETIME, the data loads INCORRECTLY on NEW_PROD but still runs ok on the NEW_TEST.

I am trying to figure out how to make the data load correctly on the NEW_PROD server, using scheduler, without:

1. rebuilding its master database with the correct collation (impractical - too many databases, too much data)

2. changing source data type from DT_WSTR to DATETIME on all date columns in all SSIS packages (too many of them plus they work fine on the other two servers)

3. changing target data type (in the DB table) from DATETIME to VARCHAR(...)

So, long story short, I am trying to understand which element of the process is responsible for interpreting the source string as date and how to make it use DMY instead of MDY regardless of the incorrect collation setting. I thought I got it but then item 7. on the above list got me puzzled again.

Any faintest hints?

Answer 1

There are 4 places to check when dealing with date interpretation issues in SQL Server 2012:

1. Collation of the db (initially inherited from server collation)

2. SSIS language setting (LocaleID property at the package level)

3. Regional settings of the user executing the package (at OS level on the server where the package is executed)

4. Language setting of the database login associated with the execution context (the "Default language" property of a login)

There are probably priorities between them but I just set them all to the same value and the issue is now gone.