In this article, we will explain how to install MongoDB on the Windows operating system, how to install and configure the ODBC driver and connector that allows us to import data from MongoDB to SQL Server using SSIS.

MongoDB Installation

Before explaining how to import data from MongoDB to SQL Server using SSIS, we will first explain how to install the MongoDB community server (free edition) on Windows.

First, we should open a web browser and navigate to the MongoDB website. Click on “Community server” under the “Software” button in the menu bar.

Figure 1 – Navigating to community server page

On the MongoDB community server page, select the MongoDB version you are looking to download and the operating system you are using (We are using Windows in this tutorial). Then click the “Download” button to download the installer.

Figure 2 – Downloading the community server installer

After the download is complete, open the MongoDB community server installer. The installation wizard will show up. On the first form, click the “Next” button.

Figure 3 – Installation welcome form

The next form is the end-user license agreement form. You should check the “I accept the terms in License Agreement” check box and click the “Next” button.

Figure 4 – End-user license agreement form

The third form is where we should select the installation type that best suits your needs. If we don’t have previous knowledge in MongoDB or are a standard user, it is recommended to select the “Complete” installation.

Figure 5 – Installation type form

Next, we should select if we need to install MongoDB as a Windows service and set up the necessary configurations such as (1) the user to run the service, (2) data and log directories, (3) service name.

Note that this option is available starting MongoDB 4.0 and that the Windows service is started once the installation is done successfully.

Figure 6 – MongoDB Windows service configuration

Next, we should select if we want to install the MongoDB compass the graphical user interface for MongoDB. All we have to do is to check the “Install MongoDB Compass” option.

Figure 7 – MongoDB Compass installation form

Now, we must click on the “Install” button to start the installation.

Figure 8 – Installation form

Figure 9 – Installation progress

Figure 10 – MongoDB installed successfully

After the installation is completed, the MongoDB Compass user interface is opened automatically.

Figure 11 – MongoDB Compass welcome screen

If you need more information about MongoDB installation on Windows operating system, you can refer to the official documentation.

Getting things ready

After installing MongoDB, we must initialize our tutorial environment before importing data from MongoDB to SQL Server using SSIS. We have two steps to do:

1. Connecting to MongoDB server and create a new database
2. Importing sample data into the database

Connecting to MongoDB server and create a new database

Since we installed the MongoDB Compass, we will not use the command prompt. Suppose you are looking to create a new database and insert data. In that case, you can refer to the following previously published article on SQL Shack, Enhanced PolyBase SQL 2019 – MongoDB and external table.

First, we should open the MongoDB Compass interface. Then, in the “New Connection” tab, you should enter the connection parameters; You can enter the whole connection string or choose to enter the connection parts individually.

Figure 12 – MongoDB Compass new connection form

Figure 13 – Passing connection parts individually

Since we need to connect to the local server (default parameters), we can click on the “Connect” button without modifying anything.

After the connection is established, all existing databases appear in a list (By default: admin, config, and local databases are found). To create a new database, we should click on the “Create database” green button located on the list’s top.

Figure 14 – Existing databases

Before creating a new database, it is worth mentioning that MongoDB stores data records as documents gathered in collections. A database stores one or more collections of documents.

To create a new database, we should enter its name and the name of the default collection.

Figure 15 – Create new database dialog

Also, there are two options that we can select:

1. Using Capped collection: As mentioned in the official documentation, Capped collections are fixed-size collections that support high-throughput operations that insert and retrieve documents based on insertion order. Capped collections work like circular buffers: once a collection fills its allocated space, it makes room for new documents by overwriting the oldest documents in the collection
2. Using custom collation: Collation allows users to specify language-specific rules for string comparison, such as rules for letter case and accent marks. Note that collation is specified at the collection level (not the whole database)

Now, the created database is added to the database list.

Figure 16 – Database created successfully

To explore the database content, we can click on the database name within the list.

Figure 17 – Database Collections list

Once we choose a specific database, the interface shows the collections created in this database (you can see the default collection we created in the image above). We can also add other collections by clicking on the “Create Collection” green button located at the top of the collections list. If we click on a collection, we can read the data and objects stored within it.

Figure 18 – Collection content

Importing sample data into the database

Using MongoDB Compass, it is easy to import data to collections. You can insert documents manually or import them from a file. Just click on the “Add data” button located on the top left of the collections form. This tutorial will import data from a CSV file containing yearly statistics and forecasts of life expectancy at birth for both sexes combined from 1955 to 2100 downloaded from the opendatalebanon.org website.

Figure 19 – Importing data from a file

Now, we should select the file (JSON or CSV files are supported). We can change the metadata retrieved automatically by the import wizard. Then we should click on “Import” to start the import operation.

Figure 20 – CSV file metadata

After data is imported, it will be shown in the collection grid.

Figure 21 – Imported data

Importing data to SQL Server using SSIS

There are three options to import data from MongoDB to SQL Server using SSIS:

1. Using third-party connectors: There are different commercial source components provided by companies such as CDATA and ZappySYS
2. Using a Script Component: The users without coding experience may not prefer this solution
3. Using the ODBC connection: This is the approach that we will be covering in the rest of this article

There are four prerequisites to create a MongoDB ODBC data source:

1. Install Microsoft Visual C++ 2015 Redistributable
2. Install MongoDB BI connector
3. Install MongoDB ODBC Driver
4. Generating drdl schema file
5. Start MongoDB BI connector service
6. Define an ODBC data source

Install Microsoft Visual C++ 2015 redistributable

First of all, we need to install the Microsoft Visual C++ 2015 redistributable since it is a prerequisite for the MongoDB BI Connector.

Install MongoDB BI connector

To import data from MongoDB to SQL Server using SSIS, we should first download and install the MongoDB ODBC BI Connector for Windows.

Connecting to MongoDB via ODBC is not established on the database engine service. It requires a service called MongoSQL that is installed within the BI connector.

MongoDB ODBC driver

The third step needed to import data from MongoDB to SQL Server using SSIS is to install the MongoDB BI connector ODBC Driver. The installation executable is a simple wizard where no advanced options should be specified.

Since we are using SSIS and since Visual studio only reads connections for the 32-bit platform, if you are using 64-bit installation, you must install the 32-bit drivers also.

Generating drdl schema file

To import data from MongoDB to SQL Server using SSIS, since MongoDB is a NoSQL database, we should define a relational schema file that allows the ODBC driver to read documents data relational.

Within the BI connector, there is a tool called mongodrdl.exe. We can generate a .drdl file from an existing connection within your database using the following command:

mongodrdl -d <databasename> -c <collection name> -o <output name>

We didn’t specify the host address since this tool takes by default “localhost:27017”.

Figure 22 – Generating drdl schema from DemoCollection1

If we open the generated file using a text editor, it looks like the following:

Figure 23 – Generated drdl schema content

Starting the BI Connector service

After the BI connector is installed, go to the installation directory and create a new file called “mongosqld.config”. We must open this file using a text editor and copy the following lines:

net:
bindIp: “127.0.0.1”
port: 3307
systemLog:
path: ‘C:\Program Files\MongoDB\Connector for BI\2.14\logs\mongosqld.log’
quiet: false
verbosity: 1
logRotate: “rename”
processManagement:
service:
name: “mongosql”
displayName: “MongoSQL Service”
description: “MongoSQL accesses MongoDB data with SQL”
schema:
path: ‘C:\DemoCollection1.drdl’

• Note: Specified directories must exist

Now to install and start this service open a command prompt as administrator and run the following commands after changing the current directory to the BI connector binaries folder:

mongosqld install –config “<BI connector installation directory>\mongosqld.config”
net start mongosql

Figure 24 – Creating mongosql service

Defining an ODBC data source

After installing the ODBC driver, the next step to import data from MongoDB to SQL Server using SSIS is to define a system DSN within Windows. To do that, we should navigate to “Control Panel > Administrative Tasks > ODBC data sources administration.”

In the System DSN tab, click on the “Add” button.

Figure 25 – Adding a System DSN

Select the MongoDB Unicode driver.

Figure 26 – Selecting the MongoDB Unicode driver

We should specify a data source name, enter the mongosql service address (the port specified in the configuration file, not the database engine address), and the database name as shown in the image below:

Figure 27 – Specifying the ODBC connection parameters

Now, we should receive a “Connection successful” message after clicking on the “Test” button.

Figure 28 – Testing connection

If you are using a 64-bit installation, the same steps must be repeated within the 32-bit ODBC data sources since Visual Studio only reads 32-bit ODBC data sources.

Reading data using ODBC Source

Now, let’s open Visual Studio and create a new Integration Services project. In the Connection managers tab, add a new connection, and select ODBC connection from the connection managers list.

Figure 29 – Adding a new connection

Figure 30 – selecting ODBC connection manager

Next, we should add an OLE DB connection manager for the SQL Server destination to specify the destination database name.

Next, we should add a Data Flow Task within the Control flow. And within the Data Flow Task, we should add an ODBC Source to read from MongoDB and an OLE DB destination to select (or create) the destination table.

Figure 31 – Selecting the MongoDB collection within the ODBC Source

Figure 32 – Creating a destination table from the OLE DB destination component

If we run the package, 31 rows must be exported, as shown in the image below.

Figure 33 – Rows imported to SQL Server successfully

Figure 34 – Imported rows

Conclusion

This article explains how to download and install the MongoDB community server and how to create databases and collections. We also explained how to import data into MongoDB collections. Next, we talked about installing and confining the MongoDB ODBC driver and connectors, and finally, we explained how to import data from MongoDB to SQL Server using SSIS.

The last thing worth mentioning is that if you are using SQL Server 2019. We may not need to import data from MongoDB to SQL Server using SSIS since using Polybase is preferable.

Table of contents

• Author
• Recent Posts

Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies.

Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional.

On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques.

Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website.

View all posts by Hadi Fadlallah

Latest posts by Hadi Fadlallah (see all)

• An overview of SQL Server monitoring tools - December 12, 2023
• Different methods for monitoring MongoDB databases - June 14, 2023
• Learn SQL: Insert multiple rows commands - March 6, 2023