SSIS tutorial: How to import Dropbox data into MongoDB

[daniel]

Introduction

Need to pull data from Dropbox and export it to MongoDB for reporting, storage, or integration? With SQL Server Integration Services (SSIS) and the ZappySys SSIS PowerPack, you can achieve this seamlessly, without writing a single line of code.

In this tutorial, you’ll learn how to use the API Source with the Dropbox Connector to extract data and load it directly into a MongoDB collection using the ZS MongoDB Destination in SSIS.

Prerequisites

• Download and install the ZappySys SSIS PowerPack
• A Dropbox account
• Access to a MongoDB instance (local or cloud)

Steps

Step 1: Create a Dropbox app for API access

1. Log in to your Dropbox account.

2. Visit the Dropbox Developers Console and click Create app.
   

   

3. Choose Scoped access as the API type.

4. Select either Full Dropbox (to access all files and folders) or App folder (to access only a specific app folder).

5. Enter a name for your app and click Create App.

6. Under the Permissions tab, choose the required scopes such as files.metadata.read and files.content.read. If you are working with a team, enable relevant Team Scopes as well.

7. Click Submit to save the scopes.

8. Navigate to the Settings tab and copy your App Key and App Secret for later use.

Step 2: Configure the API Source in SSIS

1. Add a Data Flow Task to the Control Flow.

2. Drag and drop the API Source component inside the Data Flow.

3. Double-click it and create a New Connection, selecting Dropbox Connector.

4. Authenticate via OAuth 2.0 by entering your App Key (Client ID) and App Secret.

5. Generate and save the Access Token.

6. Click Test Connection to ensure everything works.

7. Click OK to confirm.
   

   

8. Back in the API Source, select the required endpoint or table.

9. Preview the data to confirm it loads correctly.
   

   

Step 3: Configure the ZS MongoDB Destination

1. Right-click in the Connection Managers area and select New Connection → ZS-MONGODB.
   

   

2. Enter MongoDB Host, Port, Database, Username, and Password as required.

3. Review other tabs for advanced settings (authentication, SSL, replica sets).

4. Click Test Connection, then OK to save.
   

   

5. Add a MongoDB Destination to the Data Flow and connect it to the API Source.

6. Double-click the destination component and select your MongoDB connection.
   

   

7. Under Component Properties, set Action as Insert and select the table for your desired destination collection.
   

   

8. Ensure fields are correctly mapped in Column Mappings (auto-mapping works if names match).
   

   

9. Run the package to load data.
   

   

10. Use MongoDB Compass or your preferred tool to verify records.
    

    

Updating records in MongoDB

To update existing documents in MongoDB rather than inserting new ones:

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

2. Configure it to wrap all desired fields into a JSON document. Refer to the JSON Generator Transform guide

3. Mark your ID or unique key field with Include Column in the Downstream and Hide from Output
   

   

4. In the MongoDB Destination, in Component Properties, set the Action toUpdate or UpdateOrInsert.

5. In Column Mappings, map the JSON output to the MongoDB document. Also, map the key column (e.g., ID) to the key field.
   

   

6. Run the package to update existing documents and insert new ones.
   

   

7. Verify that records were correctly updated.
   

   

Conclusion

Using SSIS with ZappySys components like API Source, JSON Generator, and MongoDB Destination, you can automate data ingestion from Dropbox to MongoDB without writing any code.

Whether inserting new records or performing updates, these tools offer robust, scalable options for modern ETL workflows.

References

• API Source
• API 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.