How Does SSIS Implicitly Convert Datetime Formats in Data Sources?

Implicit Conversion of Datetime Formats in SSIS Sources

Introduction

When importing data into SSIS, it's crucial to understand how different datetime formats are interpreted by various datatypes. This article provides a detailed analysis of the implicit conversion rules for datetime formats in SSIS sources.

Supported Formats

By default, the following datetime datatypes in SSIS support specific formats:

• 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

When importing data into an SSIS source using a specific datetime datatype, the source attempts to implicitly convert strings to the appropriate datetime format. For example, if a string value of "1-Jan" is imported into a DT_DBTIMESTAMP column, it is implicitly converted to "1/1/2017" instead of causing a datatype conversion error.

Experiments

To demonstrate implicit conversion, a sample SSIS package was created with a Script Component source and a Flat File Destination. The Script Component iteratively converted a date value to various formats and attempted to assign it to a DT_DbTimeStamp output column. Formats that were accepted were considered implicitly convertible.

The results, documented in the attached text file, show that a wide range of formats are implicitly convertible, depending on the current culture info.

SQL Server Implicit Conversion

Although SSIS sources can implicitly convert certain datetime formats, SQL Server has stricter rules. Only the following two datetime string formats are interpreted correctly with any language setting:

• yyyyMMdd
• yyyy-MM-ddTHH:mm:ss (ISO8601)

Therefore, when using SQL Server connections, it's recommended to adhere to these standard formats for maximum compatibility.

The above is the detailed content of How Does SSIS Implicitly Convert Datetime Formats in Data Sources?. For more information, please follow other related articles on the PHP Chinese website!