How to connect to MongoDB Atlas in SSIS (Replica Set)

Introduction

In our previous post we saw how to read / write MongoDB data using ZappySys MongoDB Source and Destination. However it was all about connecting to On Premises hosted version of MongoDB. In this post you will focus on specifically on how to connect to MongoDB Atlas in SSIS (MongoDB Hosted in Cloud). MongoDB Atlas is Replicaset (Cluster setup) behind the scene so you have to tweak few options as explained in this post.

Prerequisites

Before we perform steps listed in this article, you will need to make sure following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it).
  4. Optional (If you want to Deploy and Schedule ) - Deploy and Schedule SSIS Packages

MongoDB Atlas Connection Setup in SSIS

In this tutorial we are going to Create MongoDB Connection. Here you can create multiple connection too.

  1. Before we get started, make sure you have connection details to connect to Atlas Portal. You can also refer to this guide about IP White listing
    MongoDB Atlas Replicaset Cluster (Primary / Secondary Nodes)

    MongoDB Atlas Replica set Cluster (Primary / Secondary Nodes)

  2. Download and Install SSIS PowerPack from here (Skip this if you already did).
  3. Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
  4. Right click on Connection Managers Panel to Create MongoDB Connection,and Context Menu will appear, Select New Connection from the Context Menu.
    SSIS Create New Connection
  5. Select ZS-MongoDB from the Connection Managers list and Click on Add Button.
    MongoDB Connection
  6. Now, in Connection Manager configure like below to connect to MongoDB Atlas
  7. Enter Host Name (Primary-Host[:port],Secondary-Host1[:port], Secondary-Host2[:port]….. )
    Example:
  8. Enter Username, Password and Database name
    Connect to MongoDB Atlas in SSIS (Set Host, Username, Password and Database)

    Connect to MongoDB Atlas in SSIS (Set Host, Username, Password and Database)

  9. On SSL Tab check Use SSL Option
    Enable SSL Connection - Required for MongoDB Atlas (Encrypted Data Transfer)

    Enable SSL Connection – Required for MongoDB Atlas (Encrypted Data Transfer)

  10. On Options tab enter as below (change YOUR_REPLICA_SET_NAME with your own replica set name)
    Specify Replica Set Name

    Specify Replica Set Name

  11. Now click Test see everything is looking good?

Thats it you can now use MongoDB Task / Source and Destination as described on below articles

How to call MongoDB JavaScript using SSIS
How to read MongoDB data from an array (extract nested subdocuments)
How to write MongoDB Aggregation Queries in SSIS (Group By)
How to loading SQL data into MongoDB (Insert, Upsert, Delete, Update)
How to update MongoDB Array Items using SSIS

 

Posted in SSIS MongoDB Connection and tagged , .