SSIS Tip: How to import JSON files into SQL Server

SSIS General How To
, ,
1

Introduction

JSON is a widely used format for data exchange with modern sources. SQL Server Integration Services (SSIS) provides a powerful way to import JSON files into SQL Server for structured storage and analysis. This article explains how to configure an SSIS package using the JSON Source and Upsert Destination, both available in the ZappySys SSIS PowerPack.

Prerequisites

Steps

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

  2. Add a JSON Source component inside the data flow.

  3. Select the JSON file you want to use. You can use a wildcard to process multiple files, for example: D:\Zappysys\Test\file*.json.

  4. Use the filter to extract the specific object or array you need. For more details, refer to this guide.

  5. Preview the data to confirm it’s loading correctly, then click OK to save the configuration:

    JSON Preview
    JSON Preview828×743 46.2 KB

  6. Drag and drop a destination component. You can use the OLE DB Destination for simple inserts or the Upsert Destination for insert/update operations. In this example, we use the Upsert Destination.

  7. Connect the JSON Source to the Upsert Destination.

  8. Configure the Upsert Destination:

    • Select or create the SQL Server connection.
    • Choose the target table.
    • Choose the desired action (Insert, Update, or both).
    • Review or manually set the column mappings.
    • Define at least one unique key column.
    • Click OK to save the configuration:
      Upsert Destination
      Upsert Destination961×597 46 KB

  9. Optionally, add extra destination components to log or analyze the number of new vs. updated records.

  10. Run the package and verify the results.

    Package Execution Result
    Package Execution Result439×320 20.5 KB

Conclusion

Importing JSON files into SQL Server using SSIS and the ZappySys JSON Source is straightforward. This approach is ideal for transforming semi-structured data into a relational format for reporting, analytics, or further ETL processing. Depending on your needs, you can use either the OLE DB Destination for simple inserts or the Upsert Destination for scenarios requiring insert and update logic. For recurring tasks, consider scheduling your SSIS package with SQL Server Agent.

For more advanced SSIS file operations, explore the ZappySys SSIS PowerPack.

References

Contact us

If you need further assistance or have specific use-cases, contact our support team via chat or by submitting a ticket.