Share via

Derived Column Error

Ali Ahad 151 Reputation points
2025-09-09T23:09:42.0333333+00:00

I have created a package where I am using a derived column for date. I have defined the column in my table as below:

	[LAST_FOUND] [date] NULL

The format of the values in excel file are:

Screenshot 2025-09-09 180053 My derived column expression is

(DT_DBDATE)[Last Found]

And I am getting an error message:

[Derived Column [144]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[DERIVED_LAST_FOUND]" 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.

Any help will be appreciated.

Thanks,

Ali.

SQL Server Integration Services
0 comments
Answer accepted by question author
  1. Yitzhak Khabinsky 27,006 Reputation points
    2025-09-14T14:44:33.83+00:00

    hi @Ali Ahad,

    Please try the following solution based on tokenization.

    1. Assuming that data is in the following consistent format: Sat Jul 19 07:58:30 2025
    2. Make sure that SSIS column, let's say [excel_date], is of DT_WSTR data type.
    3. Use Derived Column SSIS Task with the following expression:

    TOKEN([excel_date] ," ",5) + "-" + TOKEN([excel_date] ," ",2) + "-" + TOKEN([excel_date] ," ",3)

    As end result it will be formatted as '2025-Jul-19', and after insertion into a database table column [LAST_FOUND] of DATE type it will hold 2025-07-19 value.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Marcin Policht 63,730 Reputation points MVP Volunteer Moderator
    2025-09-09T23:47:14.78+00:00

    This is a common issue with Excel + SSIS because for several reasons

    1. Excel column typing – SSIS guesses the datatype of Excel columns based on the first 8 rows (by default). If it sees mixed values (dates, blanks, text), it may interpret the column as DT_WSTR.
    2. Invalid/blank values – If the column has empty strings, "N/A", or dates in unexpected format, (DT_DBDATE) conversion will fail.
    3. Regional settings – Excel dates like 09/09/2025 vs 09.09.2025 can break conversion if locale doesn't match.

    To fix this, you can try the following:

    1. Clean conversion in Derived Column with error handling

    Instead of a direct cast:

    (DT_DBDATE)(NULL(DT_WSTR,50) == [Last Found] ? NULL(DT_WSTR,50) : TRIM([Last Found]))

    Or more safely, use a Conditional Split first:

    • Route rows where ISDATE([Last Found]) == TRUE
    • Convert only those.
    • Handle others (null, bad data) separately.
    1. Force Excel source column to be read as string
    • In your Excel Source, set IMEX=1 in the connection string (Extended Properties="Excel 12.0;HDR=YES;IMEX=1";) so SSIS reads everything as text.
    • Then convert with (DT_DBDATE) or (DT_DATE) safely in the data flow.
    1. Use DT_DATE instead of DT_DBDATE

    DT_DBDATE has stricter rules (only date, no time). Try:

    (DT_DATE)[Last Found]

    and then map to SQL date — SSIS will handle the conversion.

    If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

    hth

    Marcin

  2. Ali Ahad 151 Reputation points
    2025-09-13T04:17:55.98+00:00

    Hi Marcin,

    Thanks for explaining this in detail. I am trying to do the Conditional Split but I am getting an error message that 'ISDATE' function is not recognized.

    ISDATE_Error

    I am not able to find the any other function to check for the date.

    Thanks,

    Ali.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.