none
Data Factory Stored Procedure Activity does not support DateTime 2 causes major issue

    Question

  • I have a major problem due to the Stored Procedure activity in ADFv2 not supporting DateTime2 data types.  When storing datetimes into a table via stored proc in ADF the datetime precision is truncated and rounded due to it only supporting DateTime.  Here's my example:

    I am getting a watermark value (max date) from a source system that is of value "2018-08-06 01:05:54.8820000".  In the copy activity I use this to grab a window of data.  This works correctly.  But then I log that date and time to a SQL table so I can use the date the next day.  To log the date I am executing a stored procedure.  But stored procedure activity only supports DateTime and not DateTime2.  This is causing the date to get truncated and rounded when stored.  When the pipeline runs the next day it is causing data issues because the date has been slightly changed.  For instance, in my invoice table with thousands of records daily there sometimes are records that now get missed because the date and time is at DateTime2 precision.

    I created a test pipeline to prove this is happening.  I created 3 Azure SQL tables all with a DateTime2 column.  I inserted '2018-08-06 01:05:54.8820000' into the first table and then used Data Factory Copy to move it into the second table.  Then I used a stored procedure activity to log the date to the third table.  The stored procedure parameter within ADF is mapped to DateTime data type (no datetime2 support), but the stored procedure script is using datetime2.  In the pipeline, the data gets moved to table 2 correctly but when it gets logged to table 3 turns into '2018-08-06 01:05:54.8833333'.

    This is causing a major issue.  Is there a way to support DateTime2 in Stored Procedure activities or at least a work around?

    Thursday, August 9, 2018 5:44 PM

Answers

  • For a workaround I changed my stored procedure activity parameters to be of type String instead of DateTime, then in the stored procedure script I am passing the parameters into DateTime2 parameters.  This seems to be working for the time being.
    • Marked as answer by FrankMn Thursday, August 23, 2018 9:09 PM
    Thursday, August 9, 2018 6:08 PM