Mapping C# DateTime to SQL Server datetime2 via SSIS

With SQL Server 2008, Microsoft introduced the new, improved datetime2 format. This newer time storage format is great, because it takes up less storage space, plus you have control over precision and can define your field to the exact specification required. Database columns defined as datetime2 can be mapped in SSIS by using the DT_DBTIMESTAMP2 type. However, in the scenario where you may have a Script Transformation in your SSIS package, and want to assign a .NET DateTime type to a Data Flow column that is mapped to a datetime2 field, you might encounter a DoesNotFitBufferException.

The reason for this is likely down to your specified field precision, and is easily fixed.

The datetime2 data type

The enhanced control offered by the datetime2 data type is a massive plus to database professionals everywhere. Compared to the previous datetime data type, datetime2 offers a wider range of available values, a potentially lower storage cost, and a higher level of accuracy. This Stack Overflow post has some great answers that summarise the comparison perfectly, as well as tips for which of the available date and time data types to use for any given scenario.

As mentioned above, the corresponding SSIS data type is DT_DBTIMESTAMP2 data type. This allows similar options to the matching SQL data type, meaning that you can use this type to map datetime2 columns in your SSIS packages.

Having increased control means that you need to be more aware of the precision of your date values. Should you attempt to shoehorn a value with a long precision (i.e. 01/01/0001T00:00:00.000) into a mapped column with a shorter precision, then you’ll run into problems, just like if you tried to insert a 300 character string into a DT_STR column with a length of 255. As such, it’s important to ensure that your data is sanitised before assigning it to your Data Flow columns (as always in SSIS). Interestingly, I ran into this problem when returning a date value created in some C# code using the DateTime.Now function (executed via a Script Transformation), due to the fact that the .NET DateTime struct was created with a higher default precision than my target output column - datetime2(0).

Unsurprisingly, this resulted in a DoesNotFitBufferException.

Dealing with the DoesNotFitBufferException

So be sure to sanitise first. I did so by adding this basic method to my code, but there are loads of other ways to do this. My way is pretty rigid, but you can play about and do something to dynamically work out a certain degree of precision in your milliseconds by using the Math.Round function.

private DateTime SanitiseDate(DateTime unsanitisedDate)  
{  
return new DateTime(unsanitisedDate.Year, unsanitisedDate.Month, unsanitisedDate.Day, unsanitisedDate.Hour, unsanitisedDate.Minute, unsanitisedDate.Second);  
}

It’s all common sense really, but when you start working with the more customisable options afforded to you by datetime2, you need to start paying attention to things like precision in your SSIS packages.