June 24, 2010 at 6:19 am
I'm taking data from a flat file that contains datetime2 (DT_DBTIMESTAMP2) dates. This data has to end up in a database field that has a datetime datatype.
So, in SSIS I convert the datatype to DT_DBTIMESTAMP, but unfortunately SSIS 2008 seems to round the date off inccorrectly (at least compared to how SQL Server rounds it off).
This is quite easy to test out. Create a flat file with one column in it and one value:
''2010-06-23 22:43:41.0685500''.
Add a flat file source connection and specify a datatype of DT_DBTIMESTAMP2. Then add a Derived Column or Data Conversion (it makes no difference) and convert the column to DT_DBTIMESTAMP column. Then transfer to a table with a single datetime column.
In every test I've done I get the following conversion: "2010-06-23 22:43:41.067"
Unless I'm missing something, the conversion should really be: ''2010-06-23 22:43:41.069''. But because SQL Server has a resolution of 3ms for the datetime datatype that would get rounded up to ''2010-06-23 22:43:41.070''. So why is SSIS rounding down to .067ms?
If you try this in SQL Server and convert the original datetime2 value to a datetime value you (correctly) get .070 ms.
Has anyone come across this before or can someone try this out and see if they get the same results?
Thanks
June 29, 2010 at 2:55 pm
No, I haven't run accross this specifically, but I can see your logic and agree. You might report this as a defect on http://connect.microsoft.com and see what they say.
CEWII
June 30, 2010 at 9:45 am
Thanks for the link. I'll look at that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply