SSIS - Milliseconds truncated from datetime variables

Peter Schmitz

Staff member
If you are using SSIS, chances are that you are using incremental loads in some places. If you are, there's a high likelihood that you aren't limited to using incremental IDs for watermarks, but at times might have to resort to using a LastLoadDate or something similar. In a lot of cases, you might be able to get away with using a simple date, but you might end up in trouble if you need to resort to loading using actual time-stamps. We typically add an "Execute SQL Task" component doing something like the following:

SELECT ISNULL(MAX(<myTimestampColumn>), '1900-01-01') AS MaxLoadDate FROM dbo.<myTable>

We then store the result in a datetime variable, which we pass onto the source component as a parameter. In case you wonder, we use that approach, rather than doing the above selection of the max-date within the WHERE clause because by passing it as a parameter, the value is treated as a constant, whereas specifying it in the WHERE clause might end up with it being evaluated on a per-row basis, and thus ending up significantly slower.

The reason for getting into trouble with this is that SSIS silently truncates an actual datetime (i.e. '2017-09-25 10:02:15:123') and removes the milliseconds (thus turning the former date into '2017-09-25 10:02:15:000'). This means that you will be reloading duplicates from source data. This behavior is not immediately obvious, and will take some time and frustration to troubleshoot.

The solution to it is fairly straight-forward. The script component will instead parse the obtained datetime as a string by using the following:

SELECT CONVERT(nvarchar(23), ISNULL(MAX(<myTimestampColumn>), '1900-01-01'), 121) AS MaxLoadDate FROM dbo.<myTable>

In the WHERE Clause of the Source query, you then do something like the following:

WHERE   <myTimestampColumn> > CONVERT(datetime, ?, 121)

There you go. Happy loading incrementally :)