数据库设定：

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)自变量的资料型别。

为什么 SSIS 变量没有将毫秒部分传递到查询中？

将 DateTime 值从 OLE DB 资料型别转换为数据库引擎资料型别时，毫秒会被截断。奇怪的是这两种资料型别都支持小数秒（毫秒）

如果您想了解有关 SSIS 中不同资料型别系统的更多信息，可以查看此答案中的“附加信息”部分。

解决方法

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