Content

• Video Tutorial
• Step-By-Step
• Filter output using JSONPath expression
• Properties
• Setting UI
• Examples
• See Also
• References

Video Tutorial

Coming soon...

Step-By-Step

In this tutorial we will see how to Create, Insert and Get Collection using MongoDB ExecuteSQL Task, ZappySys provides high performance drag and drop connectors for MongoDB Integration. MongoDB doesn’t have SQL Like query language so sometime you may feel odd…But has native JSON like Query language. No worry if you stumble upon how to query various things in MongoDB.. Its different but not too hard to learn.

1. First of all, MongoDB installed for MongoDB ExecuteSQL Task.
2. Download and Install SSIS PowerPack from here (Skip this if you already did).
3. Open Visual Studio and create new SSIS Package Project.
4. In visual studio just Drag and Drop ZS MongoDB ExecuteSQL Task in design panel.
   
5. Now, we need MongoDB Connection.
6. Right click on Connection Managers Panel to Create MongoDB Connection, and Context Menu will appear, Select New Connection from the Context Menu.
   
7. Select ZS-MongoDB from the Connection Managers list and Click on Add Button.
   
8. Now, in Connection Manager you will get by default Host name if you have already installed MondoDB, just give your User Name, Password and Database Name and hit on Test Connection and then click OK.
   

How to Create Collection(Table) using MongoDB ExecuteSQL Task.

1. Double click on MongoDB ExecuteSQL Task to configure it.
2. Select MongoDB Connection, Click on View Examples and Select Collection [Create]: Create New Collection, Replace Database and Table Name.

   {
    scope: 'table',
    db: 'admin',
    table: 'NewCollectionName',
    command: 'create'
   }
   

   
3. Click on Test / Preview to see Data Preview.
4. Click on OK button to save configure setting UI.

How to Insert Document(Data) using MongoDB ExecuteSQL Task.

1. Double click on MongoDB ExecuteSQL Task to configure it.
2. Furthermore, you can Insert Document using MongoDB ExecuteSQL Task with few Steps. Click here for examples.
   
3. Click on Test / Preview to run or execute.
4. Click on OK button to save configure setting UI.

How to get table list using MongoDB ExecuteSQL Task.

1. Double click on MongoDB ExecuteSQL Task to configure it.
2. Here, you can get table list.

   {
    scope: 'database',
    db: 'local',
    command: 'getCollectionNames'
   }
   

   
3. Click on Test / Preview to run or execute.
4. Click on OK button to save configure setting UI.

How to get Document(Data) using MongoDB ExecuteSQL Task.

1. Double click on MongoDB ExecuteSQL Task to configure it.
2. If you wish to get Documents like Where condition then you may use below code. Calling EVAL command requires special permission so make sure you setup permission so MongoDB user can call db.runCommand ( {EVAL command} ).
   

How to get Document(Data) with JSON Filter Expression using MongoDB ExecuteSQL Task.

1. Double click on MongoDB ExecuteSQL Task to configure it.
2. Now lets see, how to Filter output using JSON Path MongoDB ExecuteSQL Task support supplying filter expression (JSON Path) to extract specific data from output.
   
   For that you must specify filter property in command and Support for saving FullResultset as ADO.net dataset (e.g. select * from mycollection) can be stored as ADO.net recordset (can be used with ForEachLoop container task for looping click here)
   Example for JSON Filter Path.

   {
    scope: 'database',
    command: 'eval',
    filter: '$.result[*].CustomerID',
    args: 
    {
    code: 'function() 
          {
               var varCountry="USA";
               return db.Customers.find({Country: varCountry }).limit(2).toArray()
          }' 
    } 
   }
   

   
3. Click on Test / Preview to see Data Preview.
4. Click on OK button to save configure setting UI.

Properties

Property Name	Description
ResultsetType	ResultSet Type. It can be None, FullResultset or SingleValue. When FullResultset is specified then you have to store result in Object type variable. Full result is ADO.net DataTable. SingleValue is Object datatype Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description None [0] None FullResultset [1] FullResultset SingleValue [2] SingleValue RawResult [4] RawResult
CommandType	Source of SQL Statement. It can be DirectValue or Variable Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description Table [0] Collection Command Database [1] Database Command Server [2] Server Command
SqlStatementSourceType	Source of SQL Statement. It can be DirectValue or Variable Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description DirectValue [0] DirectValue Variable [1] Variable
ResultsetVariable	Variable name which will store Full resultset (ADO.net DataTable) or single value of result. Variable type must be object datatype if storing full resultset
Timeout	Command timeout in second. 0 means no command timeout
SqlStatementSource	If SqlSourceType=DirectValue then this property holds SQL Statement.
SqlStatementVariable	If SqlSourceType=Variable then this property holds Variable name which where SQL statement is stored
Connection	Connection where you want to execute sql command
LoggingMode	Available Options (Use numeric value listed in bracket if you have to define expression on this property (for dynamic behavior). Option Description Normal [0] Normal Medium [1] Medium Detailed [2] Detailed Debugging [3] Debugging
PrefixTimestamp	When you enable this property it will prefix timestamp before Log messages.

Setting UI

Examples

DB [Drop]: Drop database

This example shows how to drop MongoDB database (if database not found then error is raised).

{
 scope: 'database',
 db: 'YourDatabaseName',
 command: 'drop'
}

DB [DropSafe]: Drop database if exists

This example shows how to drop MongoDB database if exists (if database not found then task succeed).

{
 scope: 'database',
 db: 'YourDatabaseName',
 command: 'dropSafe'
}

DB [GetCollectionNames]: Get collection names

This example shows how to get collection names.

{
 scope: 'database',
 db: 'YourDatabaseName',
 command: 'getCollectionNames'
}

DB [GetStats]: Get database statistics

This example shows how to fetch database statistics.

{
 scope: 'database',
 db: 'YourDatabaseName',
 command: 'getStats'
}

DB [Eval]: Evaluate JavaScript

This example shows how to execute simple or complex multiline JavaScript code at server side.

{
 scope: 'database',
 command: 'eval',
 args: 
 {
 code: 'function() 
       {
        var varCountry="USA";
        return db.Customers.find({Country: varCountry }).limit(2).toArray()
       }' 
 } 
}

DB [Eval]: Using custom JSONPath Filter expression

This example shows how to execute any custom function or query and supply JSONPath expression to extract data from JSON Output. To test below you should set ResultType=FullResultset and click Test

{
 scope: 'database',
 command: 'eval',
 filter: '$.result[*].price',
 args: 
 {
 code: 'function() 
       {
        return db.MongodbSSISTest.find().toArray()
       }' 
 } 
}

DB [Eval]: Call function

This example shows how to call simple inbuilt or user defined function. If its user defined function then it must be created before you call in system.js collection (Read more about creating function in mongodb)

{
 scope: 'database',
 command: 'eval',
 args: { code: 'db.hostInfo()' } 
}

Collection [Create]: Create new collection

This example shows how to create new MongoDB collection (e.g. Table). If collection is already found then error is raised.

{
 scope: 'table',
 db: 'YourDatabaseName',
 table: 'YourNewCollectionName',
 command: 'create'
}

Collection [CreateSafe]: Create new collection if not found

This example shows how to create new MongoDB collection if not found. If collection already exists then command is ignored.

{
 scope: 'table',
 db: 'YourDatabaseName',
 table: 'YourNewCollectionName',
 command: 'createSafe'
}

Collection [Drop]: Drop collection

This example shows how to drop MongoDB collection (e.g. Table). If collection is not found then error is raised.

{
 scope: 'table',
 db: 'YourDatabaseName',
 table: 'YourCollectionName',
 command: 'drop'
}

Collection [DropSafe]: Drop collection if exits

This example shows how to drop MongoDB collection if exists. If collection does not exist then command is ignored.

{
 scope: 'table',
 db: 'YourDatabaseName',
 table: 'YourCollectionName',
 command: 'dropSafe'
}

Collection [Count]: Count records

This example shows how to count total records for specific MongoDB collection.

{
 scope: 'table',
 db: 'YourDatabaseName',
 table: 'YourCollectionName',
 command: 'count'
}

Collection [Count]: Count records with condition

This example shows how to count total records with certain condition(s) for specific MongoDB collection.

{
 scope: 'table',
 db: 'YourDatabaseName',
 table: 'YourCollectionName',
 command: 'count',
 args: { 
        query: { $or : [{country:'USA'},{country:'UK'}] } 
    }
}

Collection [Insert]: Insert document

This example shows how to insert new document into specific MongoDB collection.

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'insert',
 args: 
 {
 doc:
    {
        _id : 1,
        CustomerID : 'ALFKI',
        CompanyName : 'Company 1',
        RegistrationDate : ISODate('2001-01-01T00:00:00'),
        ContactName : 'Maria1 Anders',
        'Contact Title' : 'Sales Representative',
        AddressInfo : { Address1: 'Obere Str. 57',City: 'Newyork',State: 'NY' } ,
	    Tags : ['aa','bb','cc']
    }
 } 
}

Collection [Insert]: Insert multiple documents

This example shows how to insert multiple documents into MongoDB collection. This technique can be used to call any MongoDB Shell command

{
  scope : 'database',
  db : 'MyDB',
  command : 'eval',
  args : {
    code : 'db.MyCollection.insert( [ {row:1,name:"AA"}, {row:2,name:"BB"} ] )'
  }
}

Collection [Update]: Update existing document

This example shows how to update document/property of document (e.g. Update AdressInfo of customer document).

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'update',
 args: 
 {
 doc:
    {
        AddressInfo : { Address1: 'Obere Str. 58',City: 'Redmond',State: 'WA' }
    },
 query: {_id : 1}
 } 
}

Collection [Update]: Use of update modifier (Insert to array using $push)

This example shows how to use update modifiers. For example use of $push modifier will insert new value to array inside your existing document. For more information about update modifiers check https://docs.mongodb.org/manual/reference/operator/update/ and to learn more about array modifier check https://docs.mongodb.org/manual/reference/operator/update-array/

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'update',
 args: 
 {
 /*Check this link for more info on $push operator https://docs.mongodb.org/manual/reference/operator/update/push/ */
 doc: { $push: { Tags: 'dd' } },
 query: {RecID : 1}
 } 
}

Collection [Upsert]: Modify existing document with Upsert

This example shows how to If upsert is true and no document matches the query criteria, update() inserts a single document. The update creates the new document with either: •The fields and values of the {update} parameter if the {update} parameter contains only field and value pairs, or •The fields and values of both the {query} and {update} parameters if the {update} parameter contains update operator expressions. The update creates a base document from the equality clauses in the {query} parameter, and then applies the update expressions from the {update} parameter. If upsert is true and there are documents that match the query criteria, update() performs an update.

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'upsert',
 args: 
 {
 doc:
    {
        AddressInfo : { Country:'USA' }
    },
 query: {_id : 1}
 } 
}

Collection [Save]: Save document (Upsert by _id)

If the document contains an _id field, then the save() method is equivalent to an update with the upsert option set to true and the query predicate on the _id field. If _id filed is missing then save() is similar to insert()

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'insert',
 args: 
 {
 doc:
    {
        _id : 1,
        CustomerID : 'ALFKI',
        CompanyName : 'Company 1-Updated',
        RegistrationDate : ISODate('2001-01-01T00:00:00'),
        ContactName : 'Maria1 Anders',
        'Contact Title' : 'Sales Representative',
        AddressInfo : { Address1: 'Obere Str. 57',City: 'Newyork',State: 'NY' } ,
	    Tags : ['aa','bb','cc']
    }
 } 
}

Collection [Find]: Find records (with condition)

This example shows how to count total records with certain condition(s) for specific MongoDB collection.

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'find',
 args: { 
        query: { $or : [{country:'USA'},{country:'UK'}] } 
    }
}

Collection [Find]: Fetch all records

This example shows how to fetch all records for specified MongoDB collection.

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'find'
}

Collection [Custom]: Fetch records using SQL query

This example shows how to use familiar SQL Query language to extract data from MongoDB collection. SQL query is translated to MongoDB Native query automatically so you don't have to learn MongoDB JSON Style query language.

SELECT top 10 * 
FROM Customer 
WHERE Country IN ('USA', 'Germany') AND CompanyName LIKE 'C%' 
ORDER BY CustomerID DESC

Collection [Remove]: Remove records (with condition)

This example shows how to remove records from MongoDB collection based on certain condition(s).

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'remove',
 args: 
 {
    query: { $or : [{country:'USA'},{country:'UK'}] } 
 } 
}

Collection [RemoveAll]: Remove all records (Truncate table)

This example shows how to remove all records for specified MongoDB collection.

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'removeAll'
}

Collection [GetStats]: Get collection statistics

This example shows how to fetch collection statistics.

{
 scope: 'table',
 table: 'YourCollectionName',
 command: 'getStats'
}

See Also

• SSIS JSON Source Connector
• SSIS Export JSON File Task
• SSIS MongoDB Source Adapter
• SSIS Azure Table Storage Source Adapter
• SSIS Amazon S3 Storage Task
• SSIS Amazon Storage Connection Manager

References

• Blog posts/Articles related to MongoDB
• What is MongoDB and how can I download, Install and try for FREE?
• MongoDB Homepage
• Free Cloud Hosted version of MongoDB

Articles / Tutorials

Click here to see all articles for [SSIS MongoDB ExecuteSQL] category

Update MongoDB Array Items using SSIS Introduction In our previous blog post we saw how to perform Read and Write operations in MongoDB using SSIS (i.e. Bulk Update, Delete, Upsert, Insert). In this post we specifically focus on how to update MongoDB Array items / elements using SSIS. To make things simple to follow we have used JSON Source to produce […]

How to call MongoDB JavaScript using SSIS Introduction SSIS PowerPack comes with great features for MongoDB Integration Scenarios but in this post we will focus on how to call MongoDB Javascript (Server side) using SSIS. For this purpose we will use SSIS MongoDB Execute SQL Task This powerful task not only easy to use but it supports calling any valid MongoDB commands […]

How to Insert Multiple Documents into MongoDB using SSIS Introduction In this post you will learn how to use MongoDB ExecuteSQL Task for SSIS to insert multiple documents into MongoDB collection using SSIS. This task can be used to perform any DDL or DML operations for MongoDB natively inside SSIS without using any command line utilities. It gives you flexibility and security of SSIS. […]

How to add / remove MongoDB User in SSIS Introduction In this blog post, you will learn how to call MongoDB Shell Commands from SSIS using MongoDB ExecuteSQL task Some of the use cases of calling MongoDB shell commands are listed here.   How to call MongoDB Shell Commands in SSIS Download and install SSIS PowerPack from here From toolbox of SSIS designer drag  ZS […]