How Does SSIS Implicitly Convert Date Strings to Different Data Types?

Implicit Conversion of Dates in SSIS Sources

It's often necessary to convert dates between different formats when working with data. In SSIS, the behavior of date conversion can sometimes be unexpected. This article examines the implicit conversion of date strings to different SSIS data types and provides a reference chart of supported formats.

Default Datetime Formats for SSIS DataTypes

The following table outlines the default formats of datetimes datatypes when converting from a string:

Data Type	Default 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 Experiments

To test the implicit conversion of datetime strings, experiments were conducted using both SSIS and SQL Server.

SSIS Implicit Datetime Conversion

An SSIS package with a Dataflowtask was created. A Script Component (as a Source) and a Flat File Destination were used. The Script Component had an output column of type DT_DbTimeStamp. Within the script, a series of experiments were conducted:

1. A Date variable was defined and initialized to a specific date.
2. A loop was created to iterate through all culture info and get all datetime formats related to each culture.
3. Inside the loop, the Date variable was attempted to be converted to a formatted string and assigned to the output column.
4. Any conversion errors were logged.

The results of these experiments revealed that a wide range of datetime formats are implicitly converted to the DT_DBTIMESTAMP datatype, even those that are not considered canonical.

SQL Server Implicit Datetime Conversion

A SQL Server query was used to test whether certain string formats would be implicitly converted to datetime. The following query was executed:

SELECT CONVERT(DATETIME, '" + dtdate.ToString(strFormat) + '")

The results showed that only two datetime string formats are interpreted correctly with any language setting:

1. yyyyMMdd
2. yyyy-MM-ddTHH:mm:ss (ISO8601)

The above is the detailed content of How Does SSIS Implicitly Convert Date Strings to Different Data Types?. For more information, please follow other related articles on the PHP Chinese website!