SSIS 2012日期格式dmy vs mdy [英] SSIS 2012 date formats dmy vs mdy

问题描述

有三个SQL Server：

There are three SQL Servers:

• PROD（2008 R2）


• NEW_TEST（2012 ）


• NEW_PROD（2012）

我正在从PROD迁移大量SSIS软件包

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

源数据来自纯文本文件

源DATE数据位于格式为dd / mm / yyyy（即2015年11月5日存储为2015年5月11日）。

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

在SSIS中，DATE源列的定义（文本文件）是Unicode字符串（DT_WSTR），目标列（数据库表中）的数据类型是DATETIME，因此在从文本文件读取数据并将其写入数据库表之间进行类型转换。

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.

当我在PROD（旧）服务器上运行程序包时，数据已正确加载。

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

当我运行相同程序包（但升级到2012年）时）包在NEW_TEST服务器上，数据也可以正常加载。

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

但是，当我在NEW_PROD服务器上运行包时，d ata加载不正确（即2015年5月11日加载日期为2015年5月11日，而不是预计的2015年11月5日加载）。因此，似乎NEW_PROD服务器使用美国（MDY）设置以某种方式转换了英国（DMY）源日期字符串。

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服务器的排序规则设置为 SQL_Latin1_General_CP1_CI_AS


2. NEW_TEST服务器的排序规则为 Latin1_General_CI_AS-这与当前的PROD服务器匹配，因此，似乎NEW_PROD上的排序规则是错误的。


3. 该服务器上服务器设置之间没有其他区别


4. 目标数据库已将NEW_ *服务器以及当前PROD服务器上的排序规则设置为Latin1_General_CI_AS


5. 当我在本地计算机上手动运行软件包时，无论目标如何，数据都会正确加载。


6. 当我从NEW_PROD服务器上的计划作业运行软件包时，数据加载不正确


7. N哇，这很有趣：当我从NEW_TEST服务器BUT上的预定作业运行程序包时，目标连接指向NEW_PROD服务器时，数据正确加载


8. 在所有服务器上，用户运行SSIS服务的语言的默认语言设置为英语（sys.syslanguages中的langid = 23）。拥有预定作业的用户也是如此。


9. 当我将SSIS包中的源数据类型定义从DR_WSTR更改为DATETIME时，无论包在哪里，数据都会正确加载。


10. 当我在源和目标之间添加数据转换，将那一列从DT_WSTR转换为DB DATETIME时，数据在NEW_PROD上加载不正确，但在NEW_TEST上仍然可以正常运行。 / li>
    

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.

我正在尝试找出如何使用调度程序在不使用NEW_PROD服务器的情况下正确加载数据：

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

1. 使用正确的排序规则重建主数据库（不切实际-数据库太多，数据太多）

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

将所有SSIS包中所有日期列上的源数据类型从DT_WSTR更改为DATETIME（它们太多，并且它们在其他两个服务器上也可以正常工作）

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)

将目标数据类型（在数据库表中）从DATETIME更改为VARCHAR（...）

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

因此，长话短说，我试图理解过程的哪个元素负责将源字符串解释为日期以及如何使用DMY而不是MDY而不管排序规则设置是否不正确。我以为我知道了，但是上面列表中的第7项又让我感到困惑。

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?

推荐答案

在处理SQL Server 2012中的日期解释问题时，有4个地方需要检查：

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

1. 数据库的排序规则（最初是从服务器排序规则继承的）

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

SSIS语言设置（程序包级别的LocaleID属性）

SSIS language setting (LocaleID property at the package level)

执行软件包的用户的区域设置（在执行软件包的服务器上的操作系统级别上）

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

与执行上下文关联的数据库登录名的语言设置（登录名的默认语言属性）

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.

这篇关于SSIS 2012日期格式dmy vs mdy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！