Search code examples
ssissql-server-2019

Convert date/time using SSIS

I need to load a txt file into a sql table using SSIS. The column 'Action_date' has dates formatted like this: Feb 24 2023 9:35AM. The destination column is data type datetime. It fails with the error: Data conversion failed. The data conversion for column "Action_date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I tried using the Data Conversion component but get the same result.

enter image description here

The job fails at the first step before it even gets to the Data Conversion.

enter image description here

In the Connection Manager I've tried setting the Data Type to string [DT_STR] and various Date types. How can I get this to load to something with a format like this: 2022-12-19 09:57:01.243?

Solution

  • TRY_CAST('Feb 24 2023 9:35AM' as datetime) results in a valid datetime value

    To use TRY_CAST() you will need a Derived Column:

    1. Add a Derived Column transformation to the Data Flow task.
    2. Click on the Derived Column transformation to open the editor.
    3. In the Expression field include TRY_CAST([columnname] AS DATETIME).
    4. Specify the desired output column name.
    5. Set the data type of the output column.
    6. Save the changes.