How Does SSIS Implicitly Convert Strings to DT_DBTimestamp, and How Does This Differ From SQL Server?

SSIS Source Format Implicit Conversion for Datetime

Overview

When converting strings to datetimes in SSIS, it's important to consider the supported formats for each datatype. This article explores the implicit conversion capabilities of SSIS for the DT_DBTimestamp datatype.

Default Formats for Datetime Datatypes

The following table lists the default formats for various datetime datatypes in SSIS when converting from strings:

Datatype	Format
DT_DBDATE	yyyy-mm-dd
DT_FILETIME	yyyy-mm-dd hh:mm:ss:fff
DT_DBTIME	hh:mm:ss
DT_DBTIME2	hh:mm:ss[.fffffff]
DT_DBTIMESTAMP	yyyy-mm-dd hh:mm:ss[.fff]
DT_DBTIMESTAMP2	yyyy-mm-dd hh:mm:ss[.fffffff]
DT_DBTIMESTAMPOFFSET	yyyy-mm-dd hh:mm:ss[.fffffff] [{	-} hh:mm]

Implicit Conversion for DT_DBTimestamp

Despite not explicitly supporting the "1-Jan" format, SSIS will implicitly convert this string to 1/1/2017 when assigned to a DT_DBTimestamp output column. This is in contrast to converting the same string in SQL Server, which would result in an error.

Experiments on Implicit Conversion

To verify this behavior, we performed experiments using both SSIS and SQL Server:

SSIS Implicit Conversion:

A script component in SSIS iterated over various cultural date formats and assigned them to the DT_DBTimestamp output column. If the conversion was successful, the format and acceptance were recorded. Results were output to a file, highlighting implicit conversions.

SQL Server Implicit Conversion:

An SqlCommand was executed for each date format to test its conversion. Exceptions were caught to determine unacceptable formats.

Additional Information

For more details on data type conversions in SSIS and SQL Server, refer to the following resources:

• Integration Services Data Types
• SSIS TO SQL SERVER DATA TYPE TRANSLATIONS

Conclusion

SSIS provides implicit conversion for certain date formats when assigning strings to the DT_DBTimestamp datatype. This is not the case in SQL Server, where specific formats must be explicitly defined for conversion.

The above is the detailed content of How Does SSIS Implicitly Convert Strings to DT_DBTimestamp, and How Does This Differ From SQL Server?. For more information, please follow other related articles on the PHP Chinese website!