How to convert SSIS dates using expressions

SSIS General How To
1

Introduction

In SSIS (SQL Server Integration Services), date transformations are common tasks when building ETL workflows. You might need to reformat dates to meet specific requirements, such as changing from yyyy-MM-dd to MM-dd-yyyy or handling custom date formats. SSIS expressions provide a powerful way to perform these transformations efficiently.

This article explains how to convert and format dates in SSIS using expressions within the Derived Column Transformation or Conditional Split components.

Prerequisites

  • SSIS PowerPack: Download and install the ZappySys SSIS PowerPack from here .

Steps

  1. Start by dragging and dropping a Data Flow Task onto the control flow.

  2. Inside the Data Flow Task, add a source component like our XML source, JSON source, CSV source, etc. with a column with date values

  3. Then connect it to the Derived Column Transformation.

  4. Apply the following expression to extract Specific Date Components
    SUBSTRING((DT_WSTR, 10)[DateColumn], 6, 2) + "-" + SUBSTRING((DT_WSTR, 10)[DateColumn], 9, 2) + "-" + SUBSTRING((DT_WSTR, 10)[DateColumn], 1, 4)

    • Extracts the month: SUBSTRING((DT_WSTR, 10)[DateColumn], 6, 2).
    • Extracts the day: SUBSTRING((DT_WSTR, 10)[DateColumn], 9, 2) x.
    • Extracts the year: SUBSTRING((DT_WSTR, 10)[DateColumn], 1, 4) x.

Then you can create the format you want, like:

  • Fomrat dd-MM-yyyy:
SUBSTRING((DT_WSTR, 10)[DateColumn], 9, 2) + "-" + SUBSTRING((DT_WSTR, 10)[DateColumn], 6, 2) + "-" + SUBSTRING((DT_WSTR, 10)[DateColumn], 1, 4)

  • Add Days or Subtract Days

    • Add 5 days: DATEADD("DAY", 5, [DateColumn])
    • Subtract 7 day: DATEADD("DAY", -7, [DateColumn])

  • Extract Specific Date

    • Year: YEAR([DateColumn])
    • Month: MONTH([DateColumn])
    • Day: DAY([DateColumn])

  1. Here is an example we will use for Derived Column Transformation:

    Derived Column Transformation example
    Derived Column Transformation example786×624 26.1 KB

  2. Connect to a destination component and run the package; you will see the result. Here
    Final Result

Conclusion

SSIS expressions offer a powerful way to transform and format dates within your ETL workflows. Whether reformatting dates, adding days, or extracting components, these expressions simplify the process and eliminate the need for external scripts. For complex transformations, explore combining expressions with other SSIS components to enhance your data processing pipeline.

For more advanced scenarios, refer to the SSIS Documentation or consider using tools like ZappySys SSIS PowerPack for extended capabilities.

Reference