ssis

varchar to date format in SSIS

Question edited (big thanks billinkc!) in more detail as proposed answer wasn't resolving the problem

I have two SQL tables with one DATE column. Source DATE column is in varchar and destination is in date format. Original rows come from Flat File, process is shown below. enter image description here

The SSIS package (see below) is moving BIRTHDATE column from one staging table into another. Starting from scratch Flat File Source to Staging table: enter image description here

Derived Column for the DATE (vachar) column:

(DT_STR,40,1252)((TRIM(BDAY) == "" ? 0 : 1) == 0 ? ((DT_STR,40,1252)"19000101") : ((DT_STR,40,1252)(SUBSTRING((REPLACE(BDAY,"/","")),5,4) + SUBSTRING((REPLACE(BDAY,"/","")),3,2) + SUBSTRING((REPLACE(BDAY,"/","")),1,2))))

Second package is trying to convert varchar to DATE format but fails: enter image description here enter image description here enter image description here enter image description here Destination format in the second table is DATE

Error msg: [Data Conversion 2] Error: Data conversion failed while converting column "BIRTHDATE" (248) to column "BIRTHDATE" (6). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[Data Conversion 2] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion.Outputs[Data Conversion Output].Columns[BIRTHDATE]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Outputs[Data Conversion Output].Columns[BIRTHDATE]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

I did try using Derived Column with often proposed solutions but it didn't work. Last research solution was to use Data Conversion task but this one returns an error below.

Am I missing something? Thanks for any points guys! :)

Solution

  • Sorry, read it backwards. You can't do a data conversion within SSIS if you have a string date format of yyyymmddd aka ccyymmdd. If it's a flat file source component, then you can use the fast parse option to convert the date. Otherwise, it gets bogged down in the possibilities of regionalism.

    To get the data conversion component to work, you're going to have to provide it a bloody big hint which is putting the separators in.

    Here's my Source query. It generates 50k rows from 19000101 to 2036-11-22

    -- Generate dates from 1900-01-01 to 2036-11-22
-- sans dashes
SELECT
    CONVERT(char(8), dateadd(day, D.number, '1900-01-01'), 112) As BirthDate
FROM
(
    select TOP 50000
        CAST((row_number() OVER (ORDER BY (SELECT NULL)) -1) AS int) AS number
    FROM
        sys.all_columns AS AC
        CROSS APPLY
            sys.all_columns AS AC1
        CROSS APPLY
            sys.all_columns AS AC2

) D(number);

    I use two expressions. The first is for a new column via Derived Column, called DelimitedBirthDate of type DT_WSTR length 10

    SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2)

    The second column I create is DT_DATE, named DateBirthDate. I use the same expression as above but explicitly cast the resulting expression to date.

    (DT_DATE)(SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2))

    Finally, I use a Data Conversion Component to make a new column, DateDelimitedBirthDate which uses DelimitedBirthDate as the source and DT_DATE as the output data type.

    enter image description here

    You can do this, with Biml.

    Profit!

    <Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection
            Name="tempdb"
            ConnectionString="Data Source=.\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;"
            />
    </Connections>
    <Packages>
        <Package Name="so_33226370">
            <Tasks>
                <Dataflow Name="DFT Date conversion">
                    <Transformations>
                        <OleDbSource
                            ConnectionName="tempdb"
                            Name="OLE_SRC Generate dates">
                            <DirectInput>
                                <![CDATA[-- Generate dates from 1900-01-01 to 2036-11-22
-- sans dashes
SELECT
    CONVERT(char(8), dateadd(day, D.number, '1900-01-01'), 112) As BirthDate
FROM
(
    select TOP 50000
        CAST((row_number() OVER (ORDER BY (SELECT NULL)) -1) AS int) AS number
    FROM
        sys.all_columns AS AC
        CROSS APPLY
            sys.all_columns AS AC1
        CROSS APPLY
            sys.all_columns AS AC2

) D(number);
]]>
                            </DirectInput>
                        </OleDbSource>
                        <DerivedColumns Name="DER Generate delimiters">
                            <Columns>
                                <Column DataType="String" Name="DelimitedBirthDate" Length="10">SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2)</Column>
                                <Column DataType="Date" Name="DateBirthDate">(DT_DATE)(SUBSTRING([BirthDate], 1, 4) + "-" + SUBSTRING([BirthDate], 5, 2)+ "-" + SUBSTRING([BirthDate], 7, 2))</Column>
                            </Columns>
                        </DerivedColumns>
                        <DataConversion Name="DC Convert Delimited">
                            <Columns>
                                <Column DataType="Date" SourceColumn="DelimitedBirthDate" TargetColumn="DateDelimitedBirthDate" />
                            </Columns>
                        </DataConversion>

                        <DerivedColumns Name="DER Placeholder"></DerivedColumns>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>