SSIS DateTime Variable Precision - by Chris Howarth

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 351786 Comments
Status Closed Workarounds
Type Suggestion Repros 2
Opened 6/18/2008 11:45:24 AM
Access Restriction Public


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

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