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 including Server side Java Script.
If you still want to use $eval function then you need to downgrade MongoDB to a lower than 4.2 version.
Please Refer to MongoDB Release Notes for reference.
How to call MongoDB JavaScript – Example
To call various MongoDB shell commands or execute JavaScript in MongoDB using SSIS you have Download and Install SSIS PowerPack . Once its installed perform following steps.
- From SSIS Toolbox drag ZS MongoDB ExecuteSQL Task
- Click New to create new MongoDB connection manager
- Once connection is configured click Test and OK to save
- Now you can use {{Examples}} dropdown to see possible scenarios with MongoDB ExecuteSQL task
- Below is sample script to call Aggregate command
NOTE: See how we returned command output using toArray() Javascript function at the end? This will give us all records in clean JSON Array format which can be consumed easily using SSIS JSON Source. If you don’t use toArray then you may get Cursor with only first batch.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
{ scope: 'database', db: '{{User::varDB}}', command: 'eval', args: { code : 'function() { var varState="GA" return db.zipcodes.aggregate( [ {$match : { state: varState } }, {$group : {_id : "$state", TotalPopulation: { $sum: "$pop" }}}, {$match: { TotalPopulation: { $gt: 25 } } }, {$project : { StateAbbr: "$_id" , _id: 0, TotalPopulation:1 } }, {$sort : {TotalPopulation : -1}} ]).toArray(); }' } } |
Call MongoDB single line commands
If you wish to call commands which are single line only then you may use below code which is simpler than previous example. Notice it doesn’t require function() keyword like previous one. Make sure your code has to be in single line or try to use \r\n for new line
|
1 2 3 4 5 |
{ scope: 'database', command: 'eval', args: { code: 'db.hostInfo()' } } |
Permissions needed to call JavaScript
Calling eval command requires special permission so make sure you setup permission so MongoDB user can call db.runCommand ( {eval command} ). Here is more information about calling eval command in MongoDB.
Save Result JSON of MongoDB command into SSIS variable
If you want to save MongoDB command output as JSON into SSIS Variable then set Result mode = RawResult
Call MongoDB JavaScript in SSIS Using Execute SQL Task – Aggregate Command Example – Save raw JSON output into SSIS variable
How to use MongoDB Eval command to call runCommand function
Now lets look at how to call some admin functions using runCommand function. In below example we setting Global Timeout setting for Cursor (This helps to avoid timeout for long running cursor query)
|
1 2 3 4 5 |
{ scope: 'database', command: 'eval', args: { code: "db.getSiblingDB('admin').runCommand( { setParameter: 1, cursorTimeoutMillis: 400000 } )" } } |
Setting MongoDB Cursor Timeout ( set cursorTimeoutMillis parameter )
Save Result of MongoDB command into SSIS variable as ADO.net Table
If you want to use ForEach Loop to iterate through certain records from MongoDB result then set Result mode = FullResult this will save result as ADO.net table rather than JSON. Example : Get all collections and loop through them in SSIS using ForEachLoop Task?
Call MongoDB JavaScript in SSIS – Aggregate Command – Save JSON output as ADO.net Table – Loop Through ForEachLoop
Conclusion
MongoDB is the most popular NoSQL Database engine but integrating that into SSIS can be challenging without right tools. ZappySys SSIS PowerPack comes with many MongoDB Tasks/Components for SSIS.