有没有更好的方法来解析 SSIS 中的 [Integer].[Integer] 样式日期?

2021-12-30 00:00:00 etl sql-server ssis ssis-2012

我正在处理一个 SSIS ELT 脚本，该脚本需要从以 [INTEGER].[INTEGER] (

相关答案

我有很多与这个问题相关的答案:

CAST 与 ssis 数据流隐式转换差异
日期时间的 SSIS 源格式隐式转换
导入 Excel 日期时的 SSIS 错误(截断错误)

I'm working on an SSIS ELT script that needs to parse dates from a TSV file that are stored in the format [INTEGER].[INTEGER] (Excel integer dates followed by second since midnight, e.g., 42825.94097; or microseconds since midnight, e.g., 42831.1229166667). I've come up with the following approach:

Derived Column function to split the input into a date part and a time part

Derived Column function to append the parsed dates together, e.g.,

DATEADD("day",StartTime_Date,DATEADD("second",StartTime_Time,(DT_DATE)"1/1/1900"))

Is there a more elegant way to do this without resorting to a Script Component?

解决方案

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." Read more

From the description above you can see that you can convert these values implicitly when mapping them to a DT_DATE Column after converting it to a 8-byte floating-point number DT_R8.

Use a derived column transformation to convert this column to 8-byte floating-point number:

(DT_R8)[dateColumn]

Then map it to a DT_DATE column

Or cast it twice:

(DT_DATE)(DT_R8)[dateColumn]

Experiments

i created a SSIS package with one DataFlow Task

The DataFlow Task Contains a Script Component (as Source) that generate one output row (one column of type DT_R8) with the value 42825.94097.

The Script Component is linked to a derived column that convert this column into DT_DATE using the following expression

(DT_DATE)[Column]

the output i get is as shown below