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

Time:01-01

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.

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

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

  • Related