Why is the SSIS variable not passing the milliseconds part into the query?

DB SETUP:

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:

Execute SQL task:

SELECT ?=MAX([Load_Date])
FROM [LOG]

The task has an output parameter (say Param1) of data type DT_DBTIMESTAMP. The SSIS variable data type is DateTime.

Then in a subsequent Data Flow Task (say TASK2), I have an OLE DB source SQL command text:

SELECT * 
FROM CANDIDATE
WHERE CANDIDATE_TIMESTAMP>?

The above variable is used as an input parameter.

In the SQL profiler, I can see that the millisecond is missing. So if the SSIS variable does not store the milliseconds part? How can I ensure the milliseconds part is passed into the query?

Answer

This is an OLE DB provider issue.

The OLE DB provider (In my case: SQL Server Native Client 11) converts all DateTime input parameters to DateTime2(0) in SQL Server, even if you try to force the data type using casting functions, as an example consider the following SQL Command in an OLE DB Source.

SELECT *
FROM Users
Where CreationDate > Cast(? as datetime2(3))

From the SQL Profiler screenshot below, you can see how the OLE DB provider forced the DateTime2(0) data type for the parameter.

The milliseconds are truncated while translating the DateTime values from OLE DB data types to the database engine data types. The weird thing is that both data types support fractional seconds (milliseconds)

If you want to learn more about different data types systems in SSIS, you can check the “Additional Information” section in this answer.

More explanation

I will create an SSIS DateTime variable User::CurrentDate with the following value: 10/10/2021 12:00:01.001.

In the OLE DB Source, I will use the following statement:

SELECT *
FROM Users
Where CreationDate > Cast(? as datetime)

Then, I will select the created variable as an input parameter.

Now If I click on the preview button, the following exception is thrown:

TITLE: Microsoft Visual Studio

There was an error displaying the preview.

ADDITIONAL INFORMATION:

The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error. (Microsoft SQL Server Native Client 11.0)

If we click on the “Show Details” button, the exception Stack Trace shows that the error is thrown on the System.Data.OleDb.OleDbDataReader class. Which means that the OLE DB provider is the one that cause the issue.

Workaround

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:

1. The Database Engine data types (SQL Server, Oracle, …)
2. The SSIS variables data types
3. The SSIS Pipeline buffer (Data Flow Task) data types
4. 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: The fractional seconds have a maximum scale of 3 digits. HH:mm:ss.fff
• DBTIME2: The fractional seconds have a maximum scale of 3 digits. HH:mm:ss.fffffff