How to convert JSON string field to date type using SSIS

SSIS PowerPack JSON
, , ,
1

Introduction

In this article, we will show you how to convert JSON string-type field to date type and import it into your SQL Server table.

Prerequisites

Steps

Step 1: Set up the JSON source

  1. Drag and drop a Data Flow Task into your SSIS package.

  2. Drag a JSON Source component inside the Data Flow Task. In our example, we will use the following JSON:

    [
 {
     "name": "John Smith",
     "date_of_birth": "19850515"
 },
 {
     "name": "Emily Johnson",
     "date_of_birth": "19901230"
 },
 {
     "name": "Michael Brown",
     "date_of_birth": "19781122"
 },
 {
     "name": "Jessica Davis",
     "date_of_birth": "19950610"
 }
]

    JSON Source Example
    JSON Source Example828×745 34.2 KB

Step 2: Set up the Derived Column Transformation

  1. Connect the JSON Source component to a Derived Column Transformation component and use the following expression to convert the date_of_birth field into YYYY-MM-DD format:
(DT_DATE)(SUBSTRING([date_of_birth],1,4) + "-" + SUBSTRING([date_of_birth],5,2) + "-" + SUBSTRING([date_of_birth],7,2))

Explanation of the Expression:

  • SUBSTRING([date_of_birth],1,4): Extracts the year (e.g., 1985).
  • SUBSTRING([date_of_birth],5,2): Extracts the month (e.g., 05).
  • SUBSTRING([date_of_birth],7,2): Extracts the day (e.g., 15).
  • The + "-" + concatenation adds hyphens to match the YYYY-MM-DD format.
  • (DT_DATE) converts the resulting string into a valid DATE format.
    Derived Colums configuration
    Derived Colums configuration788×637 17.9 KB

Step 3: Set up the destination component

  1. Connect the Derived Column Transformation component to a Upsert Destination component.

  2. Create or select a database connection, and then create or select a table. In our example, we will create a new table:

    CREATE TABLE Employees (
 Name NVARCHAR(100),
 DateOfBirth DATE
);

    Select or create a table
    Select or create a table958×685 37.5 KB

  3. Choose the desired action (Insert and/or Update), map the columns, and then click OK to save the configuration:

    Mapping the columns
    Mapping the columns972×517 31.4 KB

Step 4: Run the package and check the result

  1. Execute the package to process the JSON data and insert it into the database:

    Final Result
    Final Result878×558 40.4 KB

  2. The final output in the database should now have properly formatted date values:

    image
    image480×254 6.94 KB

Conclusion

In this article, we demonstrated how to transform date value as string type to Date type in SSIS and store it in a database. We accomplished it by leveraging JSON Source, Derived Column Transformation, and Upsert Destination SSIS components.

If you encounter any issues, double-check the expressions used in the Derived Column transformation (it’s very easy to make a mistake there!) and ensure your table schema matches the expected format.

References

Contact us

If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.