Home Dashboard Directory Help
Search

SSIS DateTime Variable Precision by Chris Howarth


Status: 

Closed
 as Fixed Help for as Fixed


21
0
Sign in
to vote
Type: Suggestion
ID: 351786
Opened: 6/18/2008 11:45:24 AM
Access Restriction: Public
3
Workaround(s)
view

Description

Package variables defined in SSIS as type DATETIME can only represent values as granular as 1 second, yet SQL Server's DATETIME datatype supports milliseconds.

This leads to a loss of precision when populating a datetime package variable with a datetime value returned by SQL Server and can lead to issues when performing, for instance, datetime-based data loads where the datetime value stored in the package variable is derived from the maximum date already stored in the destination table.
Details
Sign in to post a comment.
Posted by Russ Loski on 8/22/2013 at 3:09 PM
I tested this again. This time I was able to get precision down to milliseconds. I'm not sure if my last test was faulty.
Posted by Russ Loski on 6/12/2013 at 3:30 PM
Not working in version 11.0.2100.60 of the SSIS designer. I am sure this is previous to service packs. Will watch when I can get access to the later service packs.
Posted by Erik_Kirk on 5/17/2013 at 9:24 AM
In SSIS 2012 this does not appear to be fixed. Am I missing something?
Posted by Derek Robinson on 11/29/2012 at 4:02 AM
4 years later and Microsoft have not issued a hotfix for this major oversight. Welcome to the mysterious World of Pain that is SSIS programming.
Posted by mishkin2011 on 8/8/2012 at 5:22 PM
vote up - very frustrating issue!
Posted by Christopher Haws on 4/5/2012 at 11:01 AM
Can this please be reopened? It was mentioned that this would be fixed in the next major release, which means it should have been fixed in SQL Server 2008 R2, however it wasn't and is still not fixed in SQL Server 2012.

Thanks,
Chris
Posted by Roman_K on 7/15/2011 at 1:30 AM
Is this problem solved? I see datetime-value with milliseconds (DT_DATE) in DataViewer (Grid) before OLE DB Destination (MS Sql Server db), but there are rounded values in datetime table column.... ((((

I need milliseconds to determine order of operations.
Posted by Microsoft on 8/13/2010 at 10:54 AM
Thanks for reporting this issue. The fix will be available in the next major release of SQL Server Integration Services
Posted by dejan1 on 6/20/2010 at 7:12 PM
This is a major limitation. Not only is there an issue of precision loss, but trying to use the variable in Execute SQL Script task or OLEDB Source task in data flow results in type errors.

There has to be a SQL Server compatible datetime data type in SSIS. It is really amazing SSIS does not support SQL Server data types considering it is a SQL Server ETL tool!
Posted by CraigIW2 on 4/27/2010 at 2:08 AM
I just discovered this too and it renders SSIS DateTime variables utterly useless.

Now I have to use varchar and ugly, unsafe string parsing and casts, thanks.
Posted by Microsoft on 7/2/2008 at 1:48 PM
Thanks for sending us your feedback.

We are unable to add this functionality in SQL Server 2008. We will reconsider this in our next major release planning.

Please expect an update from us within a few months.

Thanks,
Ritu
Sign in to post a workaround.
Posted by Dan Guzman on 10/18/2010 at 9:31 AM
SSIS DateTime variables have a precision that includes fractional seconds but the precision is lost when data are mapped from database data. The string variable work-around can work even with native datetime datatypes; no need to convert on the SQL side.

For input, map the database datetime value to the SSIS string variable and specify data type DBTIMESTAMP. For output, trim the excess fractional portion from the string in script (C# snippet below) and map the string variable to the database column with data type VARCHAR.

    DateTime dateTime = DateTime.Parse(Dts.Variables["DateTimeString"].Value.ToString());
    Dts.Variables["DateTimeString"].Value = dateTime.ToString("yyyy-MM-dd HH:mm:ss.fff");
Posted by dejan1 on 6/20/2010 at 8:03 PM
Another workaround is to use string SSIS variable and load it using this code (in Execute SQL Task):
SELECT ? = convert(varchar(23), MAX(sql_datetime_column), 121) FROM sql_table;

This string variable containing a datetime including milliseconds can then be used as an input variable in Execute SQL tasks and OLEDB source components in Data Flow tasks...

Posted by Chris Howarth on 6/18/2008 at 11:46 AM
A workaround exists, but it is unnecessarily 'clunky' and leads to the creation of a package that's difficult to maintain. See the following thread for more info:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3498076&SiteID=17