Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
456 views
in Technique[技术] by (71.8m points)

sql - Date calculation with parameter in SSIS is not giving the correct result

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:

data source

Parameter:

parameter

Parameter mapping:

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:

result

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:

enter image description here

It shows that the parameter data type is considered as datetime.

The other commands shows some weird statement:

  • First, the value of @P1 is set to 1
  • 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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...