SSIS datetime variable precision problem

Over on Microsoft Connect, someone submitted the following problem:

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.

This could be a pain for sure, but the workarounds aren't all that bad.  I've posted a similar problem with decimal somewhere around here back a few months ago...bottom line is that the types in SSIS don't particularly match SQL Server types. There is a translation, and I'm going to guess that because although SQL Server Integration Services is a Microsoft product word up, that doesn't necessarily mean that SSIS is always going to connect to SQL Server.  I've seen lots of packages where customers use SSIS without ever touching SQL Server; you would think that they get this cleaned up with the next release, but in the meantime most of the translations that are problematic can be imported as a string and then handled somewhere in the pipeline with a reasonable amount of pain.

Lee Everest

-------------------

Dude's watching "The Bachelorette" I think...

Link: https://connect.microsoft.com/SQLServer/feedback/details/351786/ssis-datetime-variable-precision