Update or Insert – Upsert MongoDB Array Items using SSIS

Posted on by
Contents hide
1 Introduction
2 Prerequisites
3 MongoDB Array Upsert – Update / Insert using Custom JOIN condition (such as $ne )
3.1 Update Array using Custom Lookup Condition
3.2 Insert into Array using Custom Lookup Condition
4 Conclusion
4.1 More from ZappySys Blog

Introduction

In our previous blog post we saw how to update / delete mongodb array item. Now let’s look at how to Upsert MongoDB Array Items (i.e. Insert Item if not found in Array else Update existing record). Upsert into nested MongoDB Array requires two step process, unfortunately there is no easy way to do in a single step. You may see in below screenshot that first we will do Update Step for Existing MongoDB Array Items and then we will insert missing Records in second step. So let’s get started.

Prerequisites

Before we perform the steps listed in this article, you will need to make sure the following prerequisites are met:
  1. SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
  2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
  3. Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
  4. (Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

MongoDB Array Upsert – Update / Insert using Custom JOIN condition (such as $ne )

By default SSIS Mongodb Destination performs Lookup using $eq condition (Match rows using Equal operator). However there will be a time when you like to lookup and update target rows using custom join criteria (e.g. Use Not Equal condition – $ne ). V2.7.6  and later introduced new property called  EnableCustomLookupQuery . When you enable this setting, you can supply a document which contains Data and Condition. You can map this XML formatted document to   __DOCUMENT__  (see below)

Here is the description how to use custom Join using this new property.

Now lets look at step by step. In below example we have a table called CustomerTest. We will load it with 2 records (With No orders). Then Update array using custom lookup condition, and later step we will insert record in array using custom lookup condition.

SSIS MongoDB Array Upsert Example (Update / Insert Array Items based on custom lookup condition)

SSIS MongoDB Array Upsert Example (Update / Insert Array Items based on custom lookup condition)

Update Array using Custom Lookup Condition

  1. Lets create few sample rows in MongoDB Collection. You can use following command in SSIS MongoDB Execute SQL Task
  2. Now drag Data flow.
  3. Drag ZS CSV Source or any other source. For example purpose we will configure CSV Source using Direct Value option with following Sample data. In the first run of package it will update only orders for CCC customer because only that customer will have records after table creation. one row. But in 2nd run all rows updated.
     
  4. Click OK to save Source UI
  5. Now drag ZS Template Transform to build XML document (Data + Query). Connect Source to Template transform. You can also use ZS XML Generator Transform but for simplicity we will use Template Transform.
  6. Double click to open Template Transform and enter text as below. Notice how we have entered MongoDB Query and Data in two separate XML nodes. First node is custom query for lookup. Second node is Operation we like to perform (e.g. $set in our case to update existing data). You can use Insert Variable and then select Columns option to insert placeholders. Also in some fields we used JSONENCODE function to make sure we escape double quote and slash correctly.
    Using ZS Template Transform to specify custom JOIN condition for MongoDB Update (Array Items)

    Using ZS Template Transform to specify custom JOIN condition for MongoDB Update (Array Items)

  7. Click OK to save Template Transform
  8. Drag ZS MongoDB Destination. Connect Template Transform to Destination
  9. Double click MongoDB Destination to edit. Set Connection, Table name, Operation=Update, EnableCustomLookupQuery=True
    Configure MongoDB Destination for Custom Join Condition (EnableCustomLookupQuery Setting)

    Configure MongoDB Destination for Custom Join Condition (EnableCustomLookupQuery Setting)

  10. Goto Mappings Tab and attach TemplateOutput to __DOCUMENT__
    MongoDB Mappings - Loading Document for Array Update (Custom Join Criteria)

    MongoDB Mappings – Loading Document for Array Update (Custom Join Criteria)

  11. Click OK to Save

 

Insert into Array using Custom Lookup Condition

Now let’s look at slightly modified steps to Insert into Array for new records. In this example we will insert Source records if CustomerID found but OrderID is not found. (See Template Transform Step). We will use Not Equal Condition this time.

  1. Now drag Data flow rename it to something like [Add records to array]
  2. Drag ZS CSV Source or any other source. For example purpose we will configure CSV Source using Direct Value option with following Sample data
     
  3. Click OK to save Source UI
  4. Now drag ZS Template Transform to build XML document (Data + Query). Connect Source to Template transform. You can also use ZS XML Generator Transform but for simplicity we will use Template Transform.
  5. Double click to open Template Transform and enter text as below. Notice how we have entered MongoDB Query and Data in two separate XML nodes. First node is custom query for lookup. Second node is Operation we like to perform (e.g. $addToSet in our case to insert into array )
  6. Click OK to save Template Transform
  7. Drag ZS MongoDB Destination. Connect Template Transform to Destination
  8. Double click MongoDB Destination to edit. Set Connection, Table name, Operation=Update, EnableCustomLookupQuery=True
  9. Goto Mappings Tab and attach TemplateOutput to __DOCUMENT__
  10. Click OK to Save

Download Sample SSIS Package (2012 format) – MongoDB_Upsert_ArrayItem

 

Conclusion

MongoDB integration can be challenging if you are new to NoSQL world. If you are using SSIS as your primary ETL tool then not to worry because SSIS PowerPack can give super power needed to complete your project on time with drag and drop high performance connectors.

Keywords: ssis mongodb upsert array item | ssis mongodb update array item | ssis mongodb update array elements | mongodb update array documents | MongoDB $set operator | MongoDB $addToSet operator | MongoDB update sub document items | MongoDB CRUD operations | MongoDB Bulk Updates | MongoDB bulk updates to array items

 

More from ZappySys Blog

Posted in SSIS MongoDB Destination, SSIS Template Transform and tagged , , .