在SSIS中使用参数进行日期计算未给出正确的结果 [英] Date calculation with parameter in SSIS is not giving the correct result
问题描述
我想从数据源加载最近n天的数据。为此，我有一个项目参数 number_of_days。我在带有SQL命令的OleDB数据源中使用参数，并带有子句
WHERE StartDate> = CAST（GETDATE（ ）-？作为日期）
此参数映射到项目参数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，-@ [$$$ :: number_of_days] ，GETDATE（））
我认为T-SQL表达式和SSIS表达式给出相同的含义结果（今天减去10天），但是当我运行该程序包并将结果存储在表中时情况并非如此。请参见date_diff列，该列将由8天而不是10天组成：
如果我用实际值替换参数，则可以得到正确的结果。
数据查看器还显示不正确的日期。部署程序包时，得到的结果与调试器相同。
这是一个错误，还是我在这里遗漏了什么？
我认为主要问题是OLEDB源如何检测参数数据类型，我没有找到提及该数据的官方文档，但是您可以做一个小实验看看：
尝试在OLEDB的SQL命令中编写以下查询：
选择？如Column1
然后尝试解析查询，您将得到以下错误：
'@ P1'的参数类型不能唯一地推导；两种可能是'sql_variant'和'xml'。
这意味着查询解析器试图弄清楚这些参数的数据类型是什么，它与映射到的变量数据类型无关。
然后尝试编写以下查询：
SELECT CAST（？AS INT）AS Column1
然后尝试解析查询，您将获得：
SQL语句已成功解析。
现在，让我们将这些实验应用于您的查询：
尝试使用 SELECT CAST（GETDATE（）-？AS DATE）作为Column1 并且您将得到一个错误的值，然后尝试 SELECT CAST（GETDATE（）-CAST（？AS INT）AS DATE）AS Column1 ，您将获得正确的值。 / p>
更新1-官方文档中的信息
来自以下
它显示参数数据类型被认为是 datetime 。
其他命令显示了一些奇怪的语句：
- 首先，将
@ P1的值设置为1 - 最终查询将使用以下值
1900-01-09 00:00:00
讨论
在SQL Server数据库引擎中，基本日期时间值为 1900-01-01 00:00:00 可以检索通过执行以下查询：
声明@dt datetime
set @dt = 0
选择@dt
另一方面，在SSIS中：
日期结构包括年，月，日，小时，分钟，秒和小数秒。小数秒的固定刻度为7位数字。
DT_DATE数据类型使用8字节浮点数实现。天以整数增量表示，从1899年12月30日开始，午夜为零。小时值表示为数字的小数部分的绝对值。但是，浮点值不能代表所有实数值；因此，在DT_DATE中可以显示的日期范围是有限的。
另一方面，DT_DBTIMESTAMP由内部表示年，月，日各个字段的结构表示，小时，分钟，秒和毫秒。这种数据类型对它可以显示的日期范围有更大的限制。
基于此，我认为datetime基值之间存在差异在SSIS日期数据类型（ 1899-12-30 ）和SQL Server日期时间（ 1900-01-01 ）之间，这导致在执行隐式转换以评估参数值时需要两天。
参考
I want to load data from the last n days from a data source. To do this, I have a project parameter "number_of_days". I use the parameter in an OleDB data source with a SQL Command, with a clause
WHERE StartDate >= CAST(GETDATE() -? as date)
This parameter is mapped to a project parameter, an Int32. But, if I want to load the last 10 days, it is only giving me the last 8 days.
Version info:
- SQL Server Data Tools 15.1.61710.120
- Server is SQL Server 2017 standard edition.
I set up a test package, with as little data as possible. There is this data source:
Parameter:
Parameter mapping:
The T-SQL expression (wrong result):
CAST(GETDATE() -? as date)
The SSIS expression for date_calc (correct result):
(DT_DBTIMESTAMP) (DT_DBDATE) DATEADD("DD", - @[$Project::number_of_days] , GETDATE())
I would think that the T-SQL expression and the SSIS expression give the same result (today minus 10 days) but that is not the case when I run the package and store the results in a table. See column date_diff, which gives 8 days instead of 10:
If I replace the parameter by the actual value, I do get the correct result.
A data viewer also shows the incorrect date. When I deploy the package, I get the same result as from the debugger.
Is this a bug, or am I missing something here?
I think the main problem is how OLEDB source detect the parameter data type, i didn't find an official documentation that mentioned that, but you can do a small experiment to see this:
Try to write the following Query in the SQL Command in the OLEDB Source:
SELECT ? as Column1
And then try to parse the query, you will get the following error:
The parameter type for '@P1' cannot be uniquely deduced; two possibilities are 'sql_variant' and 'xml'.
Which means that the query parser try to figure out what is the data type of these parameter, it is not related to the variable data type that you have mapped to it.
Then try to write the following query:
SELECT CAST(? AS INT) AS Column1
And then try to parse the query, you will get:
The SQL Statement was successfully parsed.
Now, let's apply these experiment to your query:
Try SELECT CAST(GETDATE() - ? AS DATE) as Column1 and you will get a wrong value, then try SELECT CAST(GETDATE() - CAST(? AS INT) AS DATE) AS Column1 and you will get a correct value.
Update 1 - Info from official documentation
From the following 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.
Which implies that the parameter datatype is not related to the variable data type.
Update 2 - Experiments using SQL Profiler
As experiments, i created an SSIS package that export data from OLEDB Source to Recordset Destination. The Data source is the result of the following query:
SELECT *
FROM dbo.DatabaseLog
WHERE PostTime < CAST(GETDATE() - ? as date)
And The Parameter ? is mapped to a Variable of type Int32 and has the Value 10
Before executing the package, i started and SQL Profiler Trace on the SQL Server Instance, after executing the package the following queries are recorded into the 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
The first command exec [sys].sp_describe_undeclared_parameters is to describe the parameter type, if we run it separately it returns the following information:
It shows that the parameter data type is considered as datetime.
The other commands shows some weird statement:
- First, the value of
@P1is set to1 - The final query is executed with the following value
1900-01-09 00:00:00
Discussion
In SQL Server database engine the base datetime value is 1900-01-01 00:00:00 which can be retrieved by executing the folloing query:
declare @dt datetime
set @dt = 0
Select @dt
On the other hand, in 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.
Based on that, i think that there is a difference between the datetime base value between SSIS date data type (1899-12-30) and the SQL Server datetime (1900-01-01), which leads to a difference in two days when performing an implicit conversion to evaluate the parameter value.
References
这篇关于在SSIS中使用参数进行日期计算未给出正确的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
