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?

CodePudding user response：

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):

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

CodePudding user response：

You can use the DT_DBTIMESTAMP2 data type if you need the milliseconds part. IT is the one mapped to the Datetime2 data type in SQL Server. Besdies, make sure you are using the DateTime2 data types instead of DateTime in the SQL Server table.

Check the following post for more information: DT_Date | DT_DBDate | DT_DBTime | DT_DBTime2 | DT_DBTimeStamp | DT_DBTImeStamp2