Cast VS Ssis Data Flow Implicit Conversion Difference

CAST vs ssis data flow implicit conversion difference

About date serials

The value stored in Oracle (42824) is known as date serial , it is also used in Microsoft Excel.

Date Serial represents the number of Days between the date value and the initial value that is 1899-12-30

You can Read more about Date Serials at:

• Why is 1899-12-30 the zero date in Access / SQL Server instead of 12/31?
• convert Excel Date Serial Number to Regular Date

CAST method

From Microsoft Docs - CAST and CONVERT (Transact-SQL):

Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01

So CAST function consider the value 1900-01-01 as an initial value when casting dates. So we need to subtract 2 days when using it to convert Date Serials

There are 2 ways to convert it to date using SQL Server:

select DATEADD(d,42824,'1899-12-30')

select CAST(36464 - 2 as SmallDateTime)

SSIS Implicit conversion

Also according to this Microsoft docs article

DBTYPE_DATE (This is an automation DATE type. It is internally represented as a double.. The whole part is the number of days since December 30, 1899 and the fractional part is the fraction of a day. This type has an accuracy of 1 second, so has an effective scale of 0.)

So implicit conversion in SSIS consider the value 1899-12-30 as an initial value when casting dates. So there is no need to subtract 2 days when using it to convert Date Serials

SSIS Data flow task implicit conversion automatically

Based on this Microsoft article

• Varchar can be implicitly converted to INT and Float
• Float can be implicitly converted to INT and Varchar
• INT can be Implicitly converted to Float and Varchar

I think that the Data that you are trying to import contains correct INT and Float values in the Varchar Column. On the other hand the data that you are trying to import on the other server contains values that cannot be converted (maybe special characters or additional spaces or alphabetic characters, ...) Make sure that your data is well pre-processed before importing it to the Destination

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 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

Data size difference in SSIS output and SSMS output

Assuming that the files contains the same data. There are many things that can make size different:

• Encoding:
  If using Unicode it may store additional data like The byte order mark (BOM)

• Row Delimiter:
  It's really just about which bytes are stored in a file. CR is a bytecode for carriage return (from the days of typewriters) and LF similarly, for line feed. It just refers to the bytes that are placed as end-of-line markers. CRLF is the combination of the two character.

• Empty Rows:
  Sometimes data exporter may add empty Rows at the End of the file

• Header:
  If one file contains columns header and the other doesn't

Hope it helps

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 Oracle showing data however imports NULL

Since Excel is not a relational database, OLE DB tries to discover the most relevant metadata from the Excel worksheet by investigating the data. This is not the case in Oracle.

Please make sure that your columns are mapped correctly in the OLE DB Destination.

---

Related Topics