How to create MongoDB documents (JSON, BSON) for loading in SSIS

Introduction

In our previous article, we discussed how to load data into MongoDB (Insert, Update, or Upsert Mode) with various options. In this post, we will focus on creating a MongoDB Document for the load process. We will see how to produce BSON (MongoDB-specific JSON that may include functions such as ObjectID and ISODate).

This article assumes you have installed SSIS PowerPack and watched the introduction videos of SSIS JSON Generator Transform and MongoDB Destination Connector

Create MongoDB document Example (Produce JSON, BSON for Load)

Here is the screenshot, which shows how to fetch SQL Server data > create a MongoDB document in JSON / BSON format, and load it into a MongoDB collection

The high-level steps we performed are

1. Used OLEDB Source to fetch data from SQL Server. Used a query like below
   
   Transact-SQL

   select CustomerID, 'ISODate("' + convert(varchar(30),getdate(),126) + '")' as DOB from Customers

   1	select CustomerID, 'ISODate("' + convert(varchar(30),getdate(),126) + '")' as DOB from Customers

   In the above query, you will notice that we are building some BSON fragments, which will be treated as raw values in the JSON generator. Also, we are using the convert function from SQL Server to produce an ISO date format (Style=126 will produce an ISO date)
2. Used the SSIS JSON Generator Transform to produce a JSON document for each customer. Define a custom layout using the drag-and-drop interface. You can control how many documents you want to output using settings on Transform (Single document for all input rows or one document per row in the Main dataset)
3. Load JSON documents coming from upstream into MongoDB using the SSIS MongoDB Destination Connector. We changed Action=Upsert and set ColumnsForLookup to CustomerID (Consider this as Primary Key or your Join Column to find existing document)
4. On Target, we mapped __DOCUMENT__ rather than individual columns, so that we can load the full document.
   

   Sample SSIS Package – Create MongoDB Document in BSON format (JSON with functions). Update or Insert Mode

Configure JSON Generator Transform – Create JSON Document

If you want to learn how to configure the SSIS JSON Generator Transform, then watch the video here. Important thing to remember in the configuration is to check the Treat value of this column as Raw JSON

Create MongoDB Document in JSON or BSON format – Load into Collection (Upsert – Update or Insert)

Configure MongoDB Destination – For Upsert (Update or Insert Mode)

By default, the SSIS MongoDB Destination Connector performs an insert, but if you want to perform an Upsert (i.e., insert if not found, else update), you have to change a few settings.

1. Change Operation to UpdateOrInsert
2. Set ColumnsForLookup (e.g., in our case, it was just one CustomerID). If you have multiple columns for join, then use a comma-separated list (e.g., City, State, Zip)
3. If you want to affect various documents if more than one match is found for the join criteria, then use multi=true as shown in the screenshot below (see LoadOptions )
   

   Configure the SSIS MongoDB destination for the Upsert operation

Conclusion

In this post, you have seen how easy it is to create MongoDB Documents in JSON / BSON format and load them into a MongoDB collection using ZappySys SSIS PowerPack. This innovative drag-and-drop approach can simplify the entire ETL process if you are in a time crunch and don’t know scripting languages.