数据库设定：
CREATE TABLE [dbo].[LOG]
(
[LOAD_DATE] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[PRODUCTS]
(
[PRODUCT_ID] [int] NULL,
[PRODUCT_NAME] [nchar](100) NULL,
[DATE_MODIFIED] [datetime] NULL
) ON [PRIMARY]
GO
INSERT INTO [LOG] (LOAD_DATE)
VALUES (GETDATE())
SELECT * FROM [LOG]
SSIS：
执行 SQL 任务：
SELECT ?=MAX([Load_Date])
FROM [LOG]
该任务有一个资料型别的输出自变量（比如 Param1）DT_DBTIMESTAMP。SSIS 变量资料型别是DateTime.
然后在随后的资料流任务（比如 TASK2）中，我有一个 OLE DB 源 SQL 命令文本：
SELECT *
FROM CANDIDATE
WHERE CANDIDATE_TIMESTAMP>?
上述变量用作输入自变量。
在 SQL 探查器中，我可以看到缺少毫秒。那么如果 SSIS 变量不存盘毫秒部分呢？如何确保毫秒部分传递到查询中？
uj5u.com热心网友回复：
这是 OLE DB 提供程序问题。
OLE DB 提供程序（在我的情况下：SQL Server Native Client 11）将所有DateTime输入自变量转换为DateTime2(0)SQL Server，即使您尝试使用强制转换函式强制资料型别，例如考虑以下 OLE DB 源中的 SQL 命令.
SELECT *
FROM Users
Where CreationDate > Cast(? as datetime2(3))
从下面的 SQL Profiler 荧屏截图中，您可以看到 OLE DB 提供程序如何强制DateTime2(0)自变量的资料型别。
将 DateTime 值从 OLE DB 资料型别转换为数据库引擎资料型别时，毫秒会被截断。奇怪的是这两种资料型别都支持小数秒（毫秒）
如果您想了解有关 SSIS 中不同资料型别系统的更多信息，可以查看此答案中的“附加信息”部分。
更多解释
我将创建一个User::CurrentDate具有以下值的 SSIS DateTime 变量：10/10/2021 12:00:01.001.
在 OLE DB Source 中，我将使用以下陈述句：
SELECT *
FROM Users
Where CreationDate > Cast(? as datetime)
然后，我将选择创建的变量作为输入自变量。
现在，如果我单击预览按钮，则会引发以下例外：
如果我们点击“显示详细信息”按钮，例外堆栈跟踪显示错误是在System.Data.OleDb.OleDbDataReader类上抛出的。这意味着 OLE DB 提供程序是导致问题的原因。
解决方法
You can use String data type to store the value within SSIS and use the CONVERT() function in the OLE DB Source SQL Command to convert it to DateTime within the SQL Server database engine.
Example
Use the following SQL Statement in the Execute SQL Task:
SELECT ? = CONVERT(VARCHAR(23), max([Load_Date]), 121) FROM [LOG]
And store the result within an Output parameter of type String. Then, within the OLE DB Source use the following SQL Command:
SELECT *
FROM CANDIDATE
WHERE CANDIDATE_TIMESTAMP > CONVERT(DATETIME, ?, 121)
References
- SSIS 2012 : work around for missing milliseconds in SSIS datetime variable Solution for incremental load using datetime column
Additional Information
SSIS Data types
For a better understanding, the different data types used in SSIS are worth mentioning. There are several data type systems used within the Integration services:
- The Database Engine data types (SQL Server, Oracle, ...)
- The SSIS variables data types
- The SSIS Pipeline buffer (Data Flow Task) data types
- The connection provider data types (OLE DB, ADO.NET, ODBC)
Each data type used at any level in SSIS may have a corresponding data type in another class. A great resource was provided by "Matija Lah" where most of the data types mappings are provided, besides useful information.
The following table shows the data types mapping at different levels of SSIS (Check the article I mentioned for more explanation):
| SQL Server | SSIS Variables | SSIS Pipeline Buffer | OLE DB | ADO.NET |
|---|---|---|---|---|
| bigint | Int64 | DT_I8 | LARGE_INTEGER | Int64 |
| binary | Object | DT_BYTES | n/a | Binary |
| bit | Boolean | DT_BOOL | VARIANT_BOOL | Boolean |
| char | String | DT_STR | VARCHAR | StringFixedLength |
| date | Object | DT_DBDATE | DBDATE | Date |
| datetime | DateTime | DT_DBTIMESTAMP | DATE | DateTime |
| datetime2 | Object | DT_DBTIMESTAMP2 | DBTIME2 | DateTime2 |
| datetimeoffset | Object | DT_DBTIMESTAMPOFFSET | DBTIMESTAMPOFFSET | DateTimeOffset |
| decimal | Object (< SQL 2012) Decimal (>= SQL 2012) | DT_NUMERIC | NUMERIC | Decimal |
| float | Double | DT_R8 | FLOAT | Double |
| image | Object | DT_IMAGE | n/a | Binary |
| int | Int32 | DT_I4 | LONG | Int32 |
| money | Object | DT_CY (OLE DB) DT_NUMERIC (ADO.NET) | CURRENCY | Currency |
| nchar | String | DT_WSTR | NVARCHAR | StringFixedLength |
| ntext | String | DT_NTEXT | n/a | String |
| numeric | Object (< SQL 2012) Decimal (>= SQL 2012) | DT_NUMERIC | NUMERIC | Decimal |
| nvarchar | String | DT_WSTR | NVARCHAR | String |
| nvarchar(max) | Object | DT_NTEXT | n/a | n/a |
| real | Single | DT_R4 | FLOAT, DOUBLE | Single |
| rowversion | Object | DT_BYTES | n/a | Binary |
| smalldatetime | DateTime | DT_DBTIMESTAMP | DATE | DateTime |
| smallint | Int16 | DT_I2 | SHORT | Int16 |
| smallmoney | Object | DT_CY (OLE DB) DT_NUMERIC (ADO.NET) | CURRENCY | Currency |
| sql_variant | Object | DT_WSTR (OLE DB) DT_NTEXT (ADO.NET) | Object | |
| table | Object | n/a | ||
| text | Object | DT_TEXT | n/a | n/a |
| time | Object | DT_DBTIME2 | DBTIME2 | Time |
| timestamp | Object | DT_BYTES | n/a | Binary |
| tinyint | Byte | DT_UI1 | BYTE | Byte |
| uniqueidentifier | String (OLE DB) Object (ADO.NET) | DT_GUID | GUID | Guid |
| varbinary | Object | DT_BYTES | n/a | Binary |
| varbinary(max) | Object | DT_IMAGE | n/a | Binary |
| varchar | String | DT_STR | VARCHAR | String |
| varchar(max) | Object | DT_TEXT | n/a | n/a |
| xml | Object | DT_NTEXT |
DateTime with Fractional Seconds (Milliseconds)
The following are the DateTime types that stores milliseconds:
1. SSIS Pipeline buffer data types
Based on the official documentation, on the SSIS Pipeline buffer level, two data types stores the date and time with fractional seconds:
- DT_DBTIMESTAMP: The fractional seconds have a maximum scale of 3 digits.
HH:mm:ss.fff - DT_DBTIMESTAMP2: The fractional seconds have a maximum scale of 3 digits.
HH:mm:ss.fffffff
2. SSIS Variables data types
On the SSIS variables level, the DateTime data type stores fractional seconds with a maximum scale of 3 digits HH:mm:ss.fff
3. SQL Server data types
In the SQL Server database engine, the following data types support fractional seconds:
- DateTime: The fractional seconds have a maximum scale of 3 digits.
HH:mm:ss.fff - DateTime2: The fractional seconds have a maximum scale of 3 digits.
HH:mm:ss.fffffff
4. OLE DB Connection provider
In the OLE DB provider, the following data types support fractional seconds:
- DBTIMESTAMP：小数秒的最大刻度为 3 位。
HH:mm:ss.fff - DBTIME2：小数秒的最大刻度为 3 位。
HH:mm:ss.fffffff
uj5u.com热心网友回复：
DT_DBTIMESTAMP2如果需要毫秒部分，可以使用资料型别。IT 是映射到Datetime2SQL Server 中的资料型别的那个。Besdies，请确保您使用的是DateTime2资料型别而不是DateTimeSQL Server 表中的资料型别。
查看以下帖子了解更多信息：DT_Date | DT_DBDate | DT_DBTime | DT_DBTime2 | DT_DBTimeStamp | DT_DBTImeStamp2
0 评论