Search code examples
sqlsql-serverdatetimessisetl

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?

Solution

  • 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.

    enter image description here

    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.

    enter image description here

    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.

    enter image description here

    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)

    enter image description here

    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.

    enter image description here

    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

    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