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).
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
- Used OLEDB Source to fetch data from SQL Server. Used query like below
Transact-SQL1select CustomerID, 'ISODate("' + convert(varchar(30),getdate(),126) + '")' as DOB from Customers
- 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)
- 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)
- On Target we mapped __DOCUMENT__ rather than individual columns that way we can load full document.
Configure JSON Generator Transform – Create JSON Document
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.
- Change Operation to UpdateOrInsert
- 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)
- 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 )
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.