sql - 使用 SSIS 中的参数进行日期计算未给出正确的结果

Question

我想从数据源加载最近 n 天的数据。为此，我有一个项目参数“number_of_days”。我在带有子句的 SQL 命令中使用 OleDB 数据源中的参数

WHERE StartDate >= CAST(GETDATE() -? as date) 

此参数映射到一个项目参数，一个 Int32。但是，如果我想加载最近 10 天的数据，它只会给我最后 8 天的数据。

版本信息:

• SQL Server 数据工具 15.1.61710.120
• 服务器是 SQL Server 2017 标准版。

我设置了一个测试包，数据尽可能少。有这个数据源:

参数:

参数映射:

T-SQL 表达式(错误结果):

CAST(GETDATE() -? as date)

date_calc 的 SSIS 表达式(正确结果):

(DT_DBTIMESTAMP) (DT_DBDATE) DATEADD("DD", - @[$Project::number_of_days]  , GETDATE())

我认为 T-SQL 表达式和 SSIS 表达式给出相同的结果(今天减去 10 天)，但当我运行包并将结果存储在表中时情况并非如此。请参阅 date_diff 列，它给出了 8 天而不是 10 天:

如果我用实际值替换参数，我会得到正确的结果。

数据查看器也显示不正确的日期。当我部署包时，我得到了与调试器相同的结果。

这是一个错误，还是我遗漏了什么？

Answer 1

我认为主要问题是 OLEDB 源代码如何检测参数数据类型，我没有找到提到这一点的官方文档，但您可以做一个小实验来了解这一点:

尝试在 OLEDB 源中的 SQL 命令中编写以下查询:

SELECT ? as Column1

然后尝试解析查询，你会得到以下错误:

The parameter type for '@P1' cannot be uniquely deduced; two possibilities are 'sql_variant' and 'xml'.

这意味着查询解析器试图弄清楚这些参数的数据类型是什么，它与您映射到它的变量数据类型无关。

然后尝试编写以下查询:

SELECT CAST(? AS INT) AS Column1

然后尝试解析查询，你会得到:

The SQL Statement was successfully parsed.

---

现在，让我们将这些实验应用到您的查询中:

尝试 SELECT CAST(GETDATE() - ? AS DATE) as Column1，你会得到一个错误的值，然后尝试 SELECT CAST(GETDATE() - CAST(? AS INT) AS DATE) AS Column1 你会得到一个正确的值。

更新 1 - 来自官方文档的信息

来自以下OLEDB Source - Documentation :

The parameters are mapped to variables that provide the parameter values at run time. The variables are typically user-defined variables, although you can also use the system variables that Integration Services provides. If you use user-defined variables, make sure that you set the data type to a type that is compatible with the data type of the column that the mapped parameter references.

这意味着参数数据类型与变量数据类型无关。

---

更新 2 - 使用 SQL Profiler 的实验

作为实验，我创建了一个 SSIS 包，将数据从 OLEDB 源导出到记录集目标。数据源是以下查询的结果:

SELECT *
FROM dbo.DatabaseLog
WHERE PostTime < CAST(GETDATE() - ? as date)

并且参数 ? 映射到 Int32 类型的变量，并且具有值 10

在执行包之前，我在 SQL Server 实例上启动了 SQL Profiler Trace，在执行包之后，以下查询被记录到跟踪中:

exec [sys].sp_describe_undeclared_parameters N'SELECT *
FROM dbo.DatabaseLog
WHERE PostTime < CAST(GETDATE() -@P1 as date)'

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N'@P1 datetime',N'SELECT *
FROM dbo.DatabaseLog
WHERE PostTime < CAST(GETDATE() -@P1 as date)',1
select @p1

exec sp_execute 1,'1900-01-09 00:00:00'

exec sp_unprepare 1

第一个命令exec [sys].sp_describe_undeclared_pa​​rameters是描述参数类型，如果我们单独运行它会返回如下信息:

表明参数数据类型被认为是datetime。

其他命令显示一些奇怪的语句:

• 首先将@P1的值设置为1
• 使用以下值执行最终查询 1900-01-09 00:00:00

讨论

在 SQL Server 数据库引擎中，基本日期时间值是 1900-01-01 00:00:00，可以通过执行以下查询来检索:

declare @dt datetime
set @dt = 0
Select @dt

另一方面，在 SSIS 中:

A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits.

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE.

On the other hand, DT_DBTIMESTAMP is represented by a structure that internally has individual fields for year, month, day, hours, minutes, seconds, and milliseconds. This data type has larger limits on ranges of the dates it can present.

基于此，我认为 SSIS 日期数据类型 (1899-12-30) 和 SQL Server 日期时间 (1900-01) 之间的日期时间基值存在差异-01), 这导致在执行隐式转换以评估参数值时两天有所不同。

---

引用资料

• Integration Services Data Types
• Parsing Data
• Data type conversion (Database Engine)