Introduction
In our previous post, we discovered how to read and write MongoDB data using ZappySys MongoDB Source and Destination SSIS connectors. However, it was all about connecting to the on-premises hosted version of MongoDB. In this post, we will focus on connecting specifically to the Replica Set Cluster in MongoDB Atlas (a cloud database service by MongoDB). After you complete the steps listed below, you will be able to get and push data using the same MongoDB SSIS connectors, but this time — in MongoDB Atlas. Let’s not waste our precious time and begin!
Prerequisites
Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:- SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
- (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.
MongoDB Atlas connection setup in SSIS
In this article, we are going to demonstrate how to create the MongoDB Connection Manager, which is used by MongoDB ExecuteSQL Task, MongoDB Source, and MongoDB Destination SSIS connectors. Let’s start by following these simple steps:
- First, download and install SSIS PowerPack.
- Once you installed SSIS PowerPack, open Visual Studio, and create a new SSIS project with a new SSIS package.
- Then open the created package and right-click on the Connection Managers panel to create a new MongoDB connection. When a context menu appears, select the New Connection… menu item:
- Next, select the ZS-MONGODB option from the connection manager list and click Add… button:
- This will open the MongoDB Connection Manager configuration window. For now, let’s leave it open, and first go to your MongoDB Atlas account to get all MongoDB Atlas Cluster instance information for MongoDB Connection Manager configuration.
- Once you are on the MongoDB Atlas landing page, continue by selecting the cluster you want to connect to (make sure you white-list your IP first). After the window appears, select the Compass option:
Connecting to MongoDB Atlas Replica Set Cluster
- Then proceed by simply copying and pasting the configuration information into the MongoDB Connection Manager we left to rest for a while. Move on by filling in your database credentials and the database you are connecting to. Also, don’t forget to enable SSL. Just patiently follow the numbers in orange circles below and you will be done faster than you think!
Configuring MongoDB Atlas connection in SSIS by setting the host, user name, password, and database options
Specifying MongoDB connection string options (i.e. replicaSet and authSource options)
Enabling SSL Connection in MongoDB Connection Manager in SSIS (for encrypted data transfer)
- Finally, press the Test Connection button to test the connection. If the test is successful, you can move on and start performing data operations on the MongoDB collections.
That’s it — you can now use the MongoDB ExecuteSQL Task, MongoDB Source, and MongoDB Destination as described in the below articles:
