SSIS tutorial: How to export Google Drive data into MongoDB

SSIS PowerPack API Connectors
, ,
1

Introduction

Need to transfer collaborative data from Google Drive into MongoDB for analysis, backups, or integration with your systems? With SSIS and the ZappySys SSIS PowerPack, this is easier than ever. In this tutorial, you’ll learn how to extract data from Google Drive using the API Source with Google Drive Connector and load it directly into MongoDB, without writing a single line of code.

Prerequisites

Before getting started:

  • Download and install ZappySys SSIS PowerPack
  • MongoDB database (local or cloud-based)
  • A valid Google Account

Steps

Step 1: Create or select a Google API project

  1. Go to the Google API Console.
  2. Click the project dropdown at the top and either select an existing project or click Create Project.
    Create Project
    Create Project744×230 20.9 KB
  3. Once your project is created, click ENABLE APIS AND SERVICES.
  4. Search for and enable the following APIs: Google Drive API and Google Sheets API.
    Enable Google Drive API
    Enable Google Drive API431×231 13.4 KB
  5. Navigate to the OAuth Consent Screen tab. Please fill out the required fields and click ‘Save’.
  6. Go to the Credentials tab.
  7. Click Create Credentials then OAuth Client ID. Choose Desktop App as the application type.
  8. After creation, you’ll receive a Client ID and Client Secret—you’ll need these for SSIS.

Step 2: Configure the source component in SSIS

  1. Open your SSIS package and drag a Data Flow Task onto the Control Flow canvas.
  2. Inside the Data Flow, drag the ZappySys API Source component.
  3. Double-click it, click New Connection, and choose Google Drive Connector.
  4. Authenticate via OAuth 2.0, then enter your Client ID and Client Secret and select the required scopes.
  5. Generate and save the access token. Click Preview to verify data access.
  6. Click OK to save the connection.
    Google Drive Connection
    Google Drive Connection899×727 37.3 KB
  7. Select the desired table or endpoint in the API Source.
  8. Add any required parameters for filtering.
  9. Click Preview to view the dataset, then press OK to save your configuration.
    API source configuration
    API source configuration888×605 42.3 KB

Step 3: Configure the MongoDB Destination

  1. In the Connection Manager, right-click and choose New Connection, then select ZS-MONGODB.

  2. Configure the connection: Enter the host, username, password, and database.

  3. Optionally configure SSL or replica set settings in the Advanced tab.

  4. Test the connection to confirm it’s working.

    MongoDB Connection
    MongoDB Connection618×580 31.4 KB

  5. Drag a MongoDB Destination component and connect it to the API Source.

  6. Configure the destination: In the Connection Manager tab, select your MongoDB connection.

  7. Under Component Properties, set the Action to Insert and define the collection name.

    MongoDB Action
    MongoDB Action723×664 28.2 KB

  8. In Column Mappings, ensure your columns are mapped (auto-mapping will work if names match).

    Column Mappings
    Column Mappings723×664 28.2 KB

  9. Execute the package and verify data insertion in MongoDB using a tool like MongoDB Compass.

    Data Verified
    Data Verified308×262 10.6 KB

Step 4: Updating MongoDB records

  1. Add a JSON Generator Transform between the API Source and MongoDB Destination.
  2. Configure the desired JSON structure. For guidance, refer to the ZappySys JSON Generator documentation.
  3. Right-click the ID (or key) column, select Edit, and check Include Column in the Downstream to pass it separately.
    Include Key
    Include Key796×641 61.2 KB
  4. In the MongoDB Destination, set action to Update or UpdateOrInsert under Component Properties.
  5. In Column Mappings, map the generated JSON in the document column and the key field (e.g., ID) to the filter column
    Update Mapping
    Update Mapping723×664 24.1 KB
  6. Run the package. Existing records will be updated based on the key match.
    Update Result
    Update Result290×362 16 KB

Conclusion

Exporting data from Google Drive to MongoDB doesn’t require scripting or complex integration pipelines. With ZappySys API Source and MongoDB Destination, you can build a robust, low-code ETL process entirely within SSIS.

Ready to get started? Download the ZappySys SSIS PowerPack and begin building robust data pipelines today.

References

Contact us

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