Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n\r\n \t- SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
- Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
- Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
- (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n
MongoDB Array Upsert – Update \/ Insert using Custom JOIN condition (such as $ne )<\/h2>\n
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\u00a0 and later introduced new property called\u00a0 EnableCustomLookupQuery<\/span>\u00a0. When you enable this setting, you can supply a document which contains Data and Condition. You can map this XML formatted document to\u00a0 \u00a0__DOCUMENT__\u00a0 (see below)<\/p>\nHere is the description how to use custom Join using this new property.<\/p>\n
Enables use of custom lookup query for Update \/ Upsert or Delete Operation. \r\nBy default JOIN condition for target record match is generated automatically based on columns you supply in ColumnsForLookup property. \r\nHowever in some cases you need to supply custom condition for lookup to perform complex operations, in such case enable this option. \r\nWhen you enable this option you must supply map __DOCUMENT__ input column. String you supply to this column should be in this format \r\n\r\n<command>\r\n <query>YOUR_LOOKUP_QUERY<\/query>\r\n <document>YOUR_JSON_DOC<\/document>\r\n<\/command>. \r\n\r\nLookup query in <query> tag can be either Mongo JSON format (e.g. { \\\"CustomerID\\\" : \\\"AAA\\\", \\\"Orders.OrderID\\\" : { \\\"$ne\\\" : \\\"1000\\\" } }) \r\nOR\r\nyou can use ZappySys SQL query (e.g. select * from mytable where CustomerID='AAA' and [Orders.$.OrderID] != '1000' )<\/pre>\nNow 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.<\/p>\n
![\"SSIS](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/ssis-mongodb-array-upsert-example.png\")
<\/a>SSIS MongoDB Array Upsert Example (Update \/ Insert Array Items based on custom lookup condition)<\/p><\/div>\n
Update Array using Custom Lookup Condition<\/h3>\n\n- Lets create few sample rows in MongoDB Collection. You can use following command in SSIS MongoDB Execute SQL Task\n
{\r\n scope: 'database',\r\n db: 'Northwind',\r\n command: 'eval',\r\n args: \r\n {\r\n code: 'db.CustomerTest.insert( [ {CustomerID:\"AAA\", Orders:[]}, {CustomerID:\"BBB\", Orders:[]}, {CustomerID:\"CCC\", Orders:[{OrderID:\"1004\",\"OrderDate\" : \"2008-02-05\",\"Qty\" : \"5\"} ]} ] )' \r\n \r\n } \r\n}\r\n<\/pre>\n<\/li>\n - Now drag Data flow.<\/li>\n
- Drag ZS CSV Source<\/a> 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.\n
CustomerID,Company,OrderID,OrderDate,Qty,Address\r\nAAA,Anthony Inc,1000,2008-01-01,90,Po Box 111\\4612\r\nAAA,Anthony Inc,1001,2010-02-01,91,Po Box 111 \/ 4612\r\nBBB,Bob Inc,1002,2008-02-01,92,Po Box 222 \/ 4612\r\nBBB,Bob Inc,1003,2010-01-01,93,Po Box 222 \/ 4612\r\nCCC,Cindy Inc,1004,2010-01-07,2,Po Box 555 \/ 2345<\/pre>\n <\/li>\n
- Click OK to save Source UI<\/li>\n
- 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.<\/li>\n
- 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.\n
<command>\r\n\t<query><![CDATA[\r\n{\r\n\t\"CustomerID\": \"<%CustomerID%>\", \r\n\t\"Orders.OrderID\": { \"$eq\" : \"<%OrderID%>\"}\r\n}\r\n]]><\/query>\r\n\t<document><![CDATA[\r\n{\r\n $set :\r\n { \"CompanyName\":\"<%Company,JSONENCODE%>\", \r\n \"Address\":\"<%Address,JSONENCODE%>\", \r\n \"Orders.$.OrderID\": \"<%OrderID%>\", \r\n \"Orders.$.OrderDate\": \"<%OrderDate%>\", \r\n \"Orders.$.Qty\": \"<%Qty%>\" \r\n } \r\n }\r\n}\r\n]]><\/document>\r\n<\/command><\/pre>\n![\"Using](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/ssis-mongodb-array-update-custom-lookup-template-transform.png\")
<\/a>Using ZS Template Transform to specify custom JOIN condition for MongoDB Update (Array Items)<\/p><\/div><\/li>\n
- Click OK to save Template Transform<\/li>\n
- Drag ZS MongoDB Destination. Connect Template Transform to Destination<\/li>\n
- Double click MongoDB Destination to edit. Set Connection, Table name, Operation=Update, EnableCustomLookupQuery<\/strong>=True\n
![\"Configure](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/ssis-mongodb-array-update-custom-condition-setting.png\")
<\/a>Configure MongoDB Destination for Custom Join Condition (EnableCustomLookupQuery Setting)<\/p><\/div><\/li>\n
- Goto Mappings Tab and attach TemplateOutput to __DOCUMENT__\n
![\"MongoDB](\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/01\/ssis-mongodb-custom-join-mapping.png\")
<\/a>MongoDB Mappings – Loading Document for Array Update (Custom Join Criteria)<\/p><\/div><\/li>\n
- Click OK to Save<\/li>\n<\/ol>\n
<\/p>\n
Insert into Array using Custom Lookup Condition<\/h3>\n
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<\/strong> this time.<\/p>\n\n- Now drag Data flow rename it to something like [Add records to array]<\/strong><\/li>\n
- Drag ZS CSV Source or any other source. For example purpose we will configure CSV Source using Direct Value option with following Sample data\n
CustomerID,Company,OrderID,OrderDate,Qty\r\nAAA,Anthony Inc,1000,2008-01-01,30\r\nAAA,Anthony Inc,1001,2010-02-01,4\r\nBBB,Bob Inc,1002,2008-02-01,30\r\nBBB,Bob Inc,1003,2010-01-01,4<\/pre>\n <\/li>\n
- Click OK to save Source UI<\/li>\n
- 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.<\/li>\n
- 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 )\n
<command>\r\n<query>\r\n{\"CustomerID\": \"<%CustomerID%>\", \"Orders.OrderID\":{ \"$ne\" : \"<%OrderID%>\"}}\r\n<\/query>\r\n<document>{\r\n $addToSet :\r\n { Orders : {\"OrderID\": \"<%OrderID%>\", \"OrderDate\": \"<%OrderDate%>\", \"Qty\": \"<%Qty%>\" } } \r\n }\r\n<\/document>\r\n<\/command><\/pre>\n<\/li>\n - Click OK to save Template Transform<\/li>\n
- Drag ZS MongoDB Destination. Connect Template Transform to Destination<\/li>\n
- Double click MongoDB Destination to edit. Set Connection, Table name, Operation=Update, EnableCustomLookupQuery<\/strong>=True<\/li>\n
- Goto Mappings Tab and attach TemplateOutput to __DOCUMENT__<\/li>\n
- Click OK to Save<\/li>\n<\/ol>\n
MongoDB Array Upsert – Update \/ Insert using Custom JOIN condition (such as $ne )<\/h2>\n
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\u00a0 and later introduced new property called\u00a0 EnableCustomLookupQuery<\/span>\u00a0. When you enable this setting, you can supply a document which contains Data and Condition. You can map this XML formatted document to\u00a0 \u00a0__DOCUMENT__\u00a0 (see below)<\/p>\n Here is the description how to use custom Join using this new property.<\/p>\n 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.<\/p>\n SSIS MongoDB Array Upsert Example (Update \/ Insert Array Items based on custom lookup condition)<\/p><\/div>\n <\/li>\n Using ZS Template Transform to specify custom JOIN condition for MongoDB Update (Array Items)<\/p><\/div><\/li>\n Configure MongoDB Destination for Custom Join Condition (EnableCustomLookupQuery Setting)<\/p><\/div><\/li>\n MongoDB Mappings – Loading Document for Array Update (Custom Join Criteria)<\/p><\/div><\/li>\n <\/p>\n 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<\/strong> this time.<\/p>\n <\/li>\nEnables use of custom lookup query for Update \/ Upsert or Delete Operation. \r\nBy default JOIN condition for target record match is generated automatically based on columns you supply in ColumnsForLookup property. \r\nHowever in some cases you need to supply custom condition for lookup to perform complex operations, in such case enable this option. \r\nWhen you enable this option you must supply map __DOCUMENT__ input column. String you supply to this column should be in this format \r\n\r\n<command>\r\n <query>YOUR_LOOKUP_QUERY<\/query>\r\n <document>YOUR_JSON_DOC<\/document>\r\n<\/command>. \r\n\r\nLookup query in <query> tag can be either Mongo JSON format (e.g. { \\\"CustomerID\\\" : \\\"AAA\\\", \\\"Orders.OrderID\\\" : { \\\"$ne\\\" : \\\"1000\\\" } }) \r\nOR\r\nyou can use ZappySys SQL query (e.g. select * from mytable where CustomerID='AAA' and [Orders.$.OrderID] != '1000' )<\/pre>\n<\/a>Update Array using Custom Lookup Condition<\/h3>\n
\n
{\r\n scope: 'database',\r\n db: 'Northwind',\r\n command: 'eval',\r\n args: \r\n {\r\n code: 'db.CustomerTest.insert( [ {CustomerID:\"AAA\", Orders:[]}, {CustomerID:\"BBB\", Orders:[]}, {CustomerID:\"CCC\", Orders:[{OrderID:\"1004\",\"OrderDate\" : \"2008-02-05\",\"Qty\" : \"5\"} ]} ] )' \r\n \r\n } \r\n}\r\n<\/pre>\n<\/li>\nCustomerID,Company,OrderID,OrderDate,Qty,Address\r\nAAA,Anthony Inc,1000,2008-01-01,90,Po Box 111\\4612\r\nAAA,Anthony Inc,1001,2010-02-01,91,Po Box 111 \/ 4612\r\nBBB,Bob Inc,1002,2008-02-01,92,Po Box 222 \/ 4612\r\nBBB,Bob Inc,1003,2010-01-01,93,Po Box 222 \/ 4612\r\nCCC,Cindy Inc,1004,2010-01-07,2,Po Box 555 \/ 2345<\/pre>\n
<command>\r\n\t<query><![CDATA[\r\n{\r\n\t\"CustomerID\": \"<%CustomerID%>\", \r\n\t\"Orders.OrderID\": { \"$eq\" : \"<%OrderID%>\"}\r\n}\r\n]]><\/query>\r\n\t<document><![CDATA[\r\n{\r\n $set :\r\n { \"CompanyName\":\"<%Company,JSONENCODE%>\", \r\n \"Address\":\"<%Address,JSONENCODE%>\", \r\n \"Orders.$.OrderID\": \"<%OrderID%>\", \r\n \"Orders.$.OrderDate\": \"<%OrderDate%>\", \r\n \"Orders.$.Qty\": \"<%Qty%>\" \r\n } \r\n }\r\n}\r\n]]><\/document>\r\n<\/command><\/pre>\n<\/a><\/a><\/a>Insert into Array using Custom Lookup Condition<\/h3>\n
\n
CustomerID,Company,OrderID,OrderDate,Qty\r\nAAA,Anthony Inc,1000,2008-01-01,30\r\nAAA,Anthony Inc,1001,2010-02-01,4\r\nBBB,Bob Inc,1002,2008-02-01,30\r\nBBB,Bob Inc,1003,2010-01-01,4<\/pre>\n
<command>\r\n<query>\r\n{\"CustomerID\": \"<%CustomerID%>\", \"Orders.OrderID\":{ \"$ne\" : \"<%OrderID%>\"}}\r\n<\/query>\r\n<document>{\r\n $addToSet :\r\n { Orders : {\"OrderID\": \"<%OrderID%>\", \"OrderDate\": \"<%OrderDate%>\", \"Qty\": \"<%Qty%>\" } } \r\n }\r\n<\/document>\r\n<\/command><\/pre>\n<\/li>\n