Being able to parse flat files is pretty standard work in SSIS but recently I came upon a situation when reviewing a package and there was something very strange happening with a timestamp being imported. Below you can see an example of the source file and its destination in the database.
Clearly there’s something wrong somewhere since we’re losing the time. The database and the flat file configuration are set to use DATETIME2(7) and DT_DBTIMESTAMP2 with a scale of 7 respectively. The data flow is only a connection between the two of these, no conversion in there at all.
The troubling part is that it doesn’t give any warnings or failures when this happens so it could easily spill into production if its overlooked (it did). So, what gives?
Hmm ok what about if we try this a different way and take it as a string and then use a Data Conversion transformation to change it, would that get us the time portion correctly?
Well in that case we read the right values in as text but the conversion fails telling us The value could not be converted because of a potential loss of data. This makes sense since we’ve got 9 digits after the second but our data type only has a scale of 7. That doesn’t solve anything though.
The solution in this case? Fast Parse!
In the Advanced Editor for the Flat File Source if we drill into the output fields we’ll find the FastParse setting under the Custom Properties section for each field. After setting this and changing back to the direct copy that we originally had, now we can see the time portion being processed correctly:
Fast Parse can be used to increase performance of parsing integer, date and time values within flat files when the data is in a more simplified format and doesn’t require complex conversions. In this instance the benefit that we see is that it truncates the portion of the time stamp which we’re unable to store within SQL Server due to our limitation with the scale of 7.
As mentioned, this is purely a truncation rather rounding the remaining digits in the time, but it’s certainly a better alternative than finding the erroneous data some time further down the line.
Andy Brownsword 