Content

• Video Tutorial
• Step-By-Step
• Component Properties
• Output Column Properties
• Error Handling
• Filtering
• Native Query Language for MongoDB
• Aggregation in MongoDB (Group By)
• Custom SQL Query Language for MongoDB
• Dynamic Query and Use DateTime Filter
• Settings UI
• FAQ
• See also
• Articles / Tutorials

Video Tutorial

1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.
2. Once you finished first step, Open Visual Studio and Create New SSIS Package Project.
3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox.
   
4. Double click on the Data Flow task to see Data Flow designer surface.
5. From the SSIS toolbox drag and drop MongoDB Source on the dataflow designer surface.
   
6. Now, we need MongoDB Connection. Lets Create it.
7. Right click on Connection Managers Panel to Create MongoDB Connection, and Context Menu will appear, Select New Connection from the Context Menu.
   
8. Select ZS-MongoDB from the Connection Managers list and Click on Add Button
   
9. Now, in Connection Manager you will get by default Host name if you have already installed MondoDB, just give your User Name, Password (You can leave it blank if not generated) and Database Name and hit on Test Connection and then click OK:
   
10. Click OK to save connection information setting.
11. Double click on MongoDB Source to configure it.
12. Lets configure MongoDB Source in Various Access Mode.
    

    How to use MongoDB Source using Table Access Mode.

    

    MongoDB Source Connector - Advanced Filter Options

    

    How to use MongoDB Source using Query (SQL Query).

    Note : If you have table name with special character use square brackets to write table name.
    Example : SELECT * FROM [Employees-OldData]
    
    Note : If you want to use nested array element in condition as column name you must use ArrayName.ElementName for an example LocationDetails.Country
    
    
    Example : You can replace Columns Name, Values and Table Name.

    SELECT * FROM Employees
    WHERE
    (CustomerID = 'BOLID' OR
     CustomerID = 'VAFFE' OR
     CustomerID = 'WANDK')
     AND
    (LocationDetails.Country = 'Denmark' OR
     LocationDetails.Country = 'Germany')
    

    

    How to use MongoDB Source using Query (native MongoDB Query language).

    
    Note : If you want to use nested array element in condition as column name you must use ArrayName.ElementName for an example LocationDetails.Country
    
    Example : You can replace Columns Name, Values and Table Name.

    {Maxrows:2, Table:Employees, Columns:"CustomerID|CustomerName|LocationDetails.Country"}
    {
    	"$and":
    	[
    		{"$or":
    		[{"CustomerID" : "BOLID"},
    		 {"CustomerID" : "VAFFE"},
    		 {"CustomerID" : "WANDK"}
    		 ]},
    		 {"$or":
    		[{"LocationDetails.Country" : "Denmark"},
    		 {"LocationDetails.Country" : "Germany"}
    		 ]}
    	 ]
    }
    

    

    MongoDB Source Connector - Select Columns

    

    SSIS MongoDB Source Connector - Output as JSON, Extract raw JSON from nested array

    
13. Click OK to save settings.
14. Now, Just Drag and Drop Our Free ZS Trash Destination from SSIS Toolbox.
    
15. Now single click on the MongoDB Source, once you see blue arrow from source ... connect it to Trash Destination.
16. Lets Double click on ZS Trash Destination to Configure it.
    
    
17. Execute the package and verify source data in data viewer.
    

• How to execute MongoDB Shell commands in SSIS (e.g. create Collection)
• How to read data from multiple arrays from MongoDB document (i.e. Orders, Products)
• Save result into two SQL Server tables

• Comparison Query Operators
• Logical Query Operators
• Geospatial Query Operators
• Evaluation Query Operators
• Array Query Operators
• Bitwise Query Operators

Syntax for MongoDB native query inside SSIS MongoDB Source:

{
    Table:YourTableName 
    [,Method: Find|Aggregate|Distinct] 
    [,MaxRows: N] 
    [,Columns: column1|column2...columnN] 
    [,AllowCursor: true|false] 
    [,AllowDiskUse: true|false] 
    [,Timeout: N] 
    [,Comment: your_comment_without_comma] 
    [,Hint: indexed_column_name1=1|-1;indexed_column_name2=1|-1...] 
    [,CursorType:NonTailable|Tailable|TailableAwait] 
    [,NoCursorTimeout=true|false] 
    [,AllowPartialResults=true|false]
    [,BypassDocumentValidation=true|false]
}
{
	<MongoDB-Query-OR-Pipeline-Goes-Here>
}
        

Example : MongoDB native query (use of Query operators)

{Table:Customer, MaxRows: 2, Columns:"Age|CustomerID"}
{
  "$and": 
  [
  	 {"$or": 
	  [{"CustomerID": "ALFKI"},
       {"CustomerID": "BOLID"}
      ]},
    {"$or": 
	  [{"Age": 1},
       {"Age": 2},
       {"Age": 3}
      ]}
  ]
}
	

SELECT TOP 2 Age, CustomerID FROM Customer WHERE (CustomerID='ALFKI' OR CustomerID='BOLID') AND (Age=1 OR Age=2 OR Age=3)

{Table:YourTableName 
 ,Method:aggregate 
[,AllowCursor: true|false] 
[,AllowDiskUse: true|false] 
[,Timeout: N] 
[,Comment: your_comment_without_comma] 
[,Hint: column1=1|-1;column2=1|-1...] 

}
{
	 pipeline: [
            [ <pipeline-stage1-more-info>]
            [,<pipeline-stage2-more-info>]
...
...
            [,<pipeline-stageN-more-info>]
	 ]
}
        

Performance tuning / Handling large documents / Buffers

• Make sure you have proper index defined on the necessary column used in query. Example if you searching based on PartNumber make sure its indexed.
• If you have large documents make sure BatchSize is set correctly to avoid 16MB limit error. Each batch cannot exceed

Example : MongoDB native query using aggregation framework (Group By)

{Table: ZipCodes,Method: aggregate}
{
  pipeline: 
  [
    {$match : { status: "A" } } ,
    {$group : {_id :  "$state", TotalPopulation: { $sum: "$pop" }}},
    {$match: { TotalPopulation: { $gt: 25 } } },
    {$project : { StateAbbr: "$_id" , _id: 0, TotalPopulation:1 } },
    {$sort : {TotalPopulation : -1}}
  ]
}

SELECT State as StateAbbr,SUM(population) as TotalPopulation 
FROM ZipCodes 
WHERE status='Active' 
HAVING SUM(population) > 25
Order By TotalPopulation DESC

Example : Getting distinct values in MongoDB Source

{Table: ZipCodes,Method: distinct, Columns:'State'}
{
}
	

SELECT DISTINCT state FROM ZipCodes
	

{Table: ZipCodes,Method: distinct, Columns:'State'}
{
  $and: { status:'A', country:'USA' }
}
	

SELECT DISTINCT state FROM ZipCodes WHERE status='A' AND country='USA'
	

Example : MongoDB native query (use of $where expression)

{Table:Customer}
{ $where: "this.credits == this.debits" } 

SQL Grammar for MongoDB:

<Select Statement> ::= SELECT [{TOP Clause}] {Column List} {From Clause} [{Where Clause}] [{OrderBy Clause}]

<TOP Clause>    ::= TOP IntegerLiteral
<Column List>   ::= * | IDENTIFIER [ [,IDENTIFIER] [,IDENTIFIER] ... ]
<From Clause>   ::= FROM IDENTIFIER
<Where Clause>  ::= WHERE <Expression List>
<OrderBy Clause>::= ORDER BY IDENTIFIER [ASC|DESC] [[,IDENTIFIER [ASC|DESC] [,IDENTIFIER [ASC|DESC] ... ]

! ============================================================================= 
! Expressions
! =============================================================================
! Nesting AND, OR supported using Multiple conditions
<Expression List>  ::= <Expression>
                           | <Expression> AND <Expression>
                           | <Expression> AND (<Expression List>)
                           | <Expression> OR <Expression>
                           | <Expression> OR (<Expression List>)


<Expression> ::= IDENTIFIER = <Value>
<Expression> ::= IDENTIFIER != <Value>
<Expression> ::= IDENTIFIER <> <Value>
<Expression> ::= IDENTIFIER > <Value>
<Expression> ::= IDENTIFIER >= <Value>
<Expression> ::= IDENTIFIER  < <Value>
<Expression> ::= IDENTIFIER <= <Value>
<Expression> ::= IDENTIFIER IN (<Value> [[, <Value>] [, <Value>] ...])
<Expression> ::= IDENTIFIER BETWEEN <Value> AND  <Value> 
<Expression> ::= IDENTIFIER LIKE <Value>
<Expression> ::= IDENTIFIER NOT LIKE <Value>
<Expression> ::= IDENTIFIER NOT LIKE <Value>
<Expression> ::= IDENTIFIER CONTAINS <Value>
<Expression> ::= IDENTIFIER IS NULL
<Expression> ::= IDENTIFIER IS NOT NULL

<Value>      ::= IntegerLiteral | StringLiteral
	

Supported Operators:

• AND
• OR
• IN
• NOT IN
• IS NULL
• IS NOT NULL
• LIKE (Only % allowed)
• NOT LIKE
• BETWEEN
• NOT BETWEEN
• = (Equal)
• !=, <> (Not Equal)
• < (Less than)
• > (Greater than)
• <= (Less than or equal to)
• >= (Greater than or equal to)

Searching for text patterns (Use of Regular Expressions):

select * from mytable where column LIKE 'some-pattern|[i][r][x][m][s]'

select * from customers where password LIKE 'P@ss'

select * from customers where City LIKE 'New%|i'

select * from customers where phone LIKE '\d\d\d|r'

select * from customers where notes LIKE '^ABC\d(.*)|rm'

MongoDB SQL query language examples:

Query ISODate in SQL Query

SELECT * FROM Orders WHERE OrderDate > 'DATE(2015-01-31)'

Using Placeholder to make query dynamic

SELECT * FROM Orders WHERE OrderDate < 'DATE({{User::LastOrderDate}})'

Using ISODate in MongoDB Native Query

{Table:Orders}
{
	"OrderDate" : { "$lt" : new ISODate("2015-01-02T00:16:15.184Z")}
}

• What is MongoDB and how can I download, Install or try it for FREE?
• How can I get MongoDB client tools to query, edit, view and manage MongoDB

• SSIS MongoDB Destination Adapter
• SSIS MongoDB Connection Manager

• 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