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 more on how to create MongoDB Document for load process. We will see how to produce BSON (MongoDB Specific JSON which may have functions such as ObjectID, ISODate).

This article assumes you have installed SSIS PowerPack and watched 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 MongoDB document in JSON / BSON format > Load into MongoDB collection

High level steps we performed are

1. Used OLEDB Source to fetch data from SQL Server. Used 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 above query you will notice that we building some BSON fragments which will be treated as raw value in JSON generator. Also we using convert function from sql server to produce ISO date format (Style=126 will produce ISO date)
2. Used SSIS JSON Generator Transform to produce JSON document for each customer. Define custom layout using drag and drop interface. You can control how many documents you want to output using setting on Trasform (Single document for all input rows or one document per row in Main dataset)
3. Load JSON documents coming from upstream into MongoDB using 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 that way we can load 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 SSIS JSON Generator Transform then watch video here. Important thing to remember in the configuration is check 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 SSIS MongoDB Destination Connector perform insert but if you want to perform Upsert (i.e. Insert if not found else Update ) then you have to change 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 comma separated list (e.g. City,State,Zip)
3. If you want to affect multiple documents if more than one match found for join criteria then use multi=true as shown in below screenshot (see LoadOptions )

Configure SSIS MongoDB destination for Upsert operation

Conclusion

In this post you have seen how easy it is to Create MongoDB Documents in JSON / BSON format and Load into MongoDB collection using ZappySys SSIS PowerPack. This innovative drag and drop approach can simplify whole ETL scenario if you are in time crunch and you don’t know script languages.