SSIS tips: How to import JSON files to MongoDB

SSIS General How To
, ,
1

Introduction

As businesses adopt NoSQL databases like MongoDB for flexibility and scalability, integrating JSON data into these platforms becomes increasingly important. SQL Server Integration Services (SSIS), paired with the ZappySys SSIS PowerPack, offers a streamlined way to import JSON files directly into MongoDB. This article explains how to configure an SSIS package using the JSON Source and MongoDB Destination, both available in the ZappySys SSIS PowerPack.

Prerequisites

Steps

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

  2. Inside the Data Flow, add a JSON Source component.

  3. Select the JSON file you want to use. You can use a wildcard to process multiple files, for example:
    D:\Zappysys\Test\JSONs\sub\sub2\newJSON*.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.

    image
    image828×745 51.6 KB

  6. Create a MongoDB connection: In the Connection Manager, right-click and select new connection and select ZS-MONGODB.

  7. Configure the MongoDB connection:

    • Set the host, username, password, and database.
    • Check other tabs for advanced configuration options.
    • Test the connection to ensure it’s working.
      MongoDB Connection
      MongoDB Connection618×580 31.4 KB

  8. Drag and drop a MongoDB Destination component and connect it to the JSON Source.

  9. Configure the MongoDB Destination:

    • Select your MongoDB connection in the Connection Manager tab.
      image
      image713×347 14.2 KB
    • In the Component Properties tab, set Action to Insert and choose your table.
      image
      image723×664 25.4 KB
    • In the Column Mappings tab, map your columns. This should happen automatically if the names match. Press OK to save.
      image
      image723×664 25.4 KB

  10. Run the package and verify the results in MongoDB.

    image
    image289×288 11.3 KB

Updating MongoDB Records

If you want to update records in MongoDB, you must pass the entire JSON in one column and the key(s) (e.g., ID) in another. Here’s how:

  1. Add a JSON Generator Transform between the JSON Source and MongoDB Destination.

  2. Set up the JSON output you want to send. For help, check the JSON Generator Transform article.

  3. Right-click the ID field, choose Edit, and check Include Column in the Downstream. This allows the ID to pass as a separate field.

    image
    image738×625 46.7 KB

  4. In the MongoDB Destination’s Component Properties tab, set the Action to Update or UpdateOrInsert.

  5. In Column Mappings, map your generated JSON to the document column and map the ID field to the key.

    image
    image713×624 21.3 KB

  6. Run the package. All matching documents in MongoDB will be updated.

    image
    image333×384 16.9 KB

Conclusion

Importing JSON files into MongoDB using SSIS and ZappySys components efficiently manages semi-structured data in modern ETL workflows. By leveraging the JSON Source and MongoDB Destination, you can easily automate data flows. Whether inserting new records or updating existing ones, these tools enable robust, flexible integrations.

To get started, download and install the ZappySys SSIS PowerPack. For any assistance, reach out to ZappySys Support.

References

Contact Us

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