SSIS tutorial: How to import JSON files into MongoDB

[daniel]

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

• Download and ifnstall the ZappySys SSIS PowerPack.
• SQL Server Data Tools (SSDT) installed
• MongoDB (locally or remotely accessible)

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\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.
   

   

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.

8. Check other tabs for advanced configuration options.

9. Test the connection to ensure it’s working, and press OK to save the configuration.
   

   

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

11. Configure the MongoDB Destination, select your MongoDB connection in the Connection Manager tab.
    

    

12. In the Component Properties tab, set Action to Insert and choose your table.
    

    

13. In the Column Mappings tab, map your columns. This should happen automatically if the names match. Press OK to save.
    

    

14. Run the package
    

    

15. Verify the results in MongoDB; we use MongoDB Compass to check the result.
    

    

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 and Hide from output. This allows the ID to pass as a separate field and avoid any issue with updating the ID field.
   

   

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.
   

   

6. Run the package with the new data.
   

   

7. All matching documents in MongoDB will be updated.
   

   

Conclusion

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

References

• JSON Source
• JSON Source documentation
• MongoDB Destination
• MongoDB Destination documentation
• Blog articles
• SSIS PowerPack

Contact us

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