Ssis Source Format Implicit Conversion for Datetime

SSIS Source Format Implicit Conversion for Datetime

General Info

These are the default formats of datetimes datatypes (when converting from string)

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]

Note: DT_DATE and DT_DBTIMESTAMP has the same SET method

And i think that converting string to date is depending also on your current culture info

More detailed information are found here

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

Experiments :

After reading your comment i didn't find any related article to your question so i made the following experiments:

SSIS Implicit datetime Conversion

i created a SSIS package with a Dataflowtask. in this dataflowtask i created a Script Component (as a Source) and a Flat File Destination. The script has one output column OutDate of type DT_DbTimeStamp Inside the script i used the following code:

Private dtDate As Date = #01/01/2016#

Public Overrides Sub CreateNewOutputRows()

    Output0Buffer.AddRow()


    Using sw As New IO.StreamWriter("D:\Result.txt", False)
        sw.WriteLine("CultureInfo;Date;Format;Accepted")
        sw.Close()
    End Using


    For Each ci As System.Globalization.CultureInfo In System.Globalization.CultureInfo.GetCultures(Globalization.CultureTypes.AllCultures)

        For Each strFormat As String In ci.DateTimeFormat.GetAllDateTimePatterns

            Dim boolResult As Boolean = True
            Try


                Output0Buffer.OutDate = dtDate.ToString(strFormat)

                boolResult = True

            Catch ex As Exception

                boolResult = False



            End Try

            Using sw As New IO.StreamWriter("D:\Result.txt", True)
                sw.WriteLine(ci.Name & ";" & dtDate.ToString(strFormat) & ";" & strFormat & ";" & boolResult.ToString)
                sw.Close()
            End Using

        Next



    Next



End Sub

First i am looping over all culture info and i am Getting all datetime formats related to it and looping over them. Then i am trying to convert the date dtDate declared to a formatted string and assign it to the Output column.

So if assigning string value with specified format to DT_DBTIMESTAMP output column is accepted that means the format is implicit converted

Output0Buffer.OutDate = dtDate.ToString(strFormat)

And Here is the Link of the Result File:

• Result.txt

SQL Server datetime Implicit Conversion

There are two datetime string formats that are interpreted correctly with with any language setting.

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

Also, you can repeat the same experiment But this time by creating an SqlCommand and executing it:

Dim sqlcmd as new SqlCommand("SELECT CONVERT(DATETIME,'" + dtdate.ToString(strFormat) + '")"

sqlCmd.ExecuteReader()

That way you can if sqlcmd throws an exception it means that format cannot be converted.

SSIS match datetime variable to varchar in data flow

The reason is because of implicit conversion by SSIS data source when deciding the type of parameter according to the opposite side of operator. Try this test:

1. Make a valid SSIS data source connection to a table that contains a date(time) column; make a dateTime variable.
2. Switch the data source to SQL command and type the below queries, one by one:

SELECT 1 FROM table WHERE ? > 1

SELECT 1 FROM table WHERE ? > '1'

SELECT 1 FROM table WHERE ? > date_col

3. Execute the data flow.
4. In SSMS, quote the recent sessions:

select top 50 t.text, s.* from sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
where t.text LIKE <filter your session>
order by last_execution_time desc

You may find how the parameter is interpreted:

(@P1 int)SELECT 1 FROM table WHERE ? > 1

(@P1 varchar(8))SELECT 1 FROM table WHERE ? > '1'

(@P1 datetime)SELECT 1 FROM table WHERE ? > date_col

In other words, the SQL command will interpret the type of incoming parameter regardless of what type it original was.

So in my problem, the dateTime parameter was first implicitly converted into varchar with an unknown format than we are attempting to convert it into a specified date type:

SELECT * FROM table where date = CONVERT(varchar(10), 'Jan 01 2019 12:00:00', 112)

Is there a better way to parse [Integer].[Integer] style dates in SSIS?

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE." Read more

From the description above you can see that you can convert these values implicitly when mapping them to a DT_DATE Column after converting it to a 8-byte floating-point number DT_R8.

Use a derived column transformation to convert this column to 8-byte floating-point number:

(DT_R8)[dateColumn]

Then map it to a DT_DATE column

Or cast it twice:

(DT_DATE)(DT_R8)[dateColumn]

Experiments

i created a SSIS package with one DataFlow Task

The DataFlow Task Contains a Script Component (as Source) that generate one output row (one column of type DT_R8) with the value 42825.94097.

The Script Component is linked to a derived column that convert this column into DT_DATE using the following expression

(DT_DATE)[Column]

the output i get is as shown below

Related answers

I have many answers related with this question:

• CAST vs ssis data flow implicit conversion difference
• SSIS Source Format Implicit Conversion for Datetime
• SSIS Error importing Excel Date (truncation error)

SSIS Data Conversion Error despite using Data Conversion and accurate destination Datatype

I think this is a date format issue, check that column does not contains empty strings or NULL values.

Also check that values are similar to yyyy-MM-dd HH:mm:ss date format.

To read more about SSIS data types check the following article:

• Integration Services Data Types

Also when converting string values to datetime, if values are well formated, just map the source column to the destination without Data conversion Transformation and they will be implicitly converted

Importing yyyyMMdd Dates From CSV in SSIS

You setup column [Date_Column] as DT_STR in Connection Manager. What is the length you specified? by default it is 50. In Derived Column, you used its length as 10, which is the error message "could not be converted because of a potential loss of data".

---

Related Topics