Selection of SSIS data types

For SSIS data types, the easy-to-miss accuracy is datetime, incorrect use of data types such as Datetime,time,decimal, which can result in overflow or data loss.

DateTime of 1,variable and parameter

DateTime for Variable and parameter corresponds to Dt_dbtimestamp (datatime for SQL Server) in the format: Yyyy-mm-dd hh:mm:ss XM, for example "2016-05-19 5:41:32 PM ", only accurate to seconds.

2, conversion components

Because the conversion component cannot lose precision, there are three ways to do so: Dt_dbtimestamp,dt_dbtimestamp,dt_dbtimestampoffset.

Dt_dbtimestamp corresponds to the Datetime of SQL Server in the format "Yyyy-mm-dd Hh:mm:ss", accurate to seconds

DT_DBTIMESTAMP2 corresponds to SQL Server Datetime2 (N), with precision, which can be accurate to ss.nnnnnnn

Dt_dbtimestampoffset corresponds to SQL Server DateTimeOffset (N), with precision, which can be accurate to ss.nnnnnnn

3, the effect of type conversion on performance

The conversion of data types is done only when necessary, because the SSIS engine has to pay a price for each conversion.

If you convert a value to an unsupported data type, an additional conversion to the mapped data will be generated inside SSIS, preferably converting the data type to the data type closest to the target.

4, Choice of data type

The data flow of SSIS needs to do a lot of work in memory, eliminating the most time-consuming IO operations and making the reading, conversion, and loading performance of the readings fast. Because SSIS uses memory buffers to complete related operations, the number of rows loaded into the buffer is directly related to the width of the row, the narrower the row width, the more rows the buffer holds, and the greater the amount of data processed, the better the performance. If you want to define the data type of a large input source, you must carefully pick the data type, store and transform the data with the closest size and precision, so that the row width is not too wide, and too many data type conversions can be avoided.

Selection of SSIS data types