SSIS 2012 dmy vs mdy date formats

Question

There are three SQL servers:

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

I am transferring a large number of SSIS packets from PROD to the NEW_TEST and NEW_PROD servers.

Raw data comes from flat text files.

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

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

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

When I run the same package (but updated until 2012) on the NEW_TEST server, the data is also loaded.

However, when I run the package on the NEW_PROD server, the data does not load correctly (i.e. 11/05/2015 loads as May 11, 2015 instead of the expected November 5, 2015). Thus, it seems that the NEW_PROD server is somehow converting the UK source date string (DMY) using the US (MDY) settings.

After spending a considerable amount of time trying to figure out what was going on, I discovered this:

• The NEW_PROD server has a mapping setting with "SQL_Latin1_General_CP1_CI_AS"
• The NEW_TEST server has a sorting of "Latin1_General_CI_AS" - this corresponds to the current PROD server, so it seems that the mapping to NEW_PROD is incorrect.
• There are no other differences between the server settings at the server level, except for the above.
• The target database is configured on Latin1_General_CI_AS on the NEW_ * servers, as well as on the current PROD server
• When I run the package manually on my local computer, the data is loaded CORRECTLY regardless of the purpose.
• When I run the package from a scheduled task on the NEW_PROD server, the data is loading incorrectly.
• Now it’s interesting: when I run the package from a scheduled task on the NEW_TEST server, but with the target connection pointing to the NEW_PROD server, the data is loaded CORRECTLY
• On all servers, the user who starts SSIS has the default language set to British (langid = 23 in sys.syslanguages). The same applies to the user who owns the scheduled task.
• When I change the data source type definition in an SSIS package from DR_WSTR to DATETIME, the data is loaded CORRECTLY regardless of where the package is running.
• When I add data conversion between source and target, converting this single column from DT_WSTR to DB DATETIME, the data is loading INCORRECTLY on NEW_PROD, but it still works fine in NEW_TEST.

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

• restore your main database with proper sorting (impractical - too many databases, too much data)

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

• changing the target data type (in the database table) from DATETIME to VARCHAR (...)

So, a long story, I’m trying to figure out which process element is responsible for interpreting the source string as a date and how to get it to use DMY instead of MDY regardless of the wrong sorting setting. I thought I got it, but then point 7. in the above list puzzled me again.

Any weak hints?

Answer 1

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

• Db mapping (originally inherited from server sorting)

• Configuring SSIS (LocaleID property at the package level)

• Regional settings of the user executing the package (at the OS level on the server on which the package is running)

• Database login language setting associated with the execution context (the "Default language" property for logging in)

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