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.
PrerequisitesBefore we perform steps listed in this article, you will need to make sure following prerequisites are met:
- SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft site).
- Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
- Make sure ZappySys SSIS PowerPack is installed (download it).
- 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.
- 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
- Download and Install SSIS PowerPack from here (Skip this if you already did).
- Once you finished above steps, Open Visual Studio and Create New SSIS Package Project.
- Right click on Connection Managers Panel to Create MongoDB Connection,and Context Menu will appear, Select New Connection from the Context Menu.
- Select ZS-MongoDB from the Connection Managers list and Click on Add Button.
- Now, in Connection Manager configure like below to connect to MongoDB Atlas
- Enter Host Name (Primary-Host[:port],Secondary-Host1[:port], Secondary-Host2[:port]….. )
- Enter Username, Password and Database name
- On SSL Tab check Use SSL Option
- On Options tab enter as below (change YOUR_REPLICA_SET_NAME with your own replica set name)
- 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 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