{"id":811,"date":"2016-10-14T17:31:25","date_gmt":"2016-10-14T17:31:25","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=811"},"modified":"2023-08-04T11:18:30","modified_gmt":"2023-08-04T11:18:30","slug":"call-mongodb-javascript-using-ssis-execute-sql-task","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/","title":{"rendered":"How to call MongoDB JavaScript using SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"SSIS PowerPack<\/a> comes with great features for MongoDB Integration Scenarios<\/a> but in this post we will focus on how to call MongoDB Javascript<\/em> (Server side) using SSIS. For this purpose we will use SSIS MongoDB Execute SQL Task<\/a><\/p>\n

This powerful task not only easy to use but it supports calling any valid MongoDB commands including Server side Java Script<\/em>.<\/p>\n

NOTE:<\/strong> ZappySys could not support $eval function, If you’ve installed MongoDB version higher than 3.0 version as MongoDB have removed support for the $eval function from version 4.2.
\nIf you still want to use $eval function then you need to downgrade MongoDB to a lower than 4.2 version.
\nPlease Refer to MongoDB Release Notes<\/a> for reference. <\/div><\/div>\n

How to call MongoDB JavaScript – Example<\/h2>\n

To call various MongoDB shell commands or\u00a0execute JavaScript in MongoDB<\/em> using SSIS you have Download and Install SSIS PowerPack<\/a> . Once its installed perform following steps.<\/p>\n

    \n
  1. From SSIS Toolbox drag ZS MongoDB ExecuteSQL Task<\/li>\n
  2. Click New to create new MongoDB connection manager<\/li>\n
  3. Once connection is configured click Test and OK to save<\/li>\n
  4. Now you can use {{Examples}} dropdown to see possible scenarios with MongoDB ExecuteSQL task<\/li>\n
  5. Below is sample script to call Aggregate command<\/li>\n<\/ol>\n

    \u00a0NOTE:<\/strong> See how we returned command output using toArray()<\/strong> 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.<\/a> If you don’t use toArray then you may get Cursor with only first batch.<\/p>\n

    ZappySys MongoDB Shell command syntax (i.e. JSON) supports both double quotes and single quotes around the property name or value (i.e. your script). If you wrap your code with single quote then do not use single quote inside script block. <\/div><\/div>\n
    {\r\nscope: 'database',\r\ndb: '{{User::varDB}}',\r\ncommand: 'eval',\r\nargs: \r\n { \r\n  code : 'function()\r\n  {\r\n   var varState=\"GA\"\r\n   return db.zipcodes.aggregate( \r\n   [\r\n   {$match : { state: varState } },\r\n   {$group : {_id :  \"$state\", TotalPopulation: { $sum: \"$pop\" }}},\r\n   {$match: { TotalPopulation: { $gt: 25 } } },\r\n   {$project : { StateAbbr: \"$_id\" , _id: 0, TotalPopulation:1 } },\r\n   {$sort : {TotalPopulation : -1}}\r\n   ]).toArray(); \r\n  }'\r\n }\r\n}<\/pre>\n
    Call MongoDB\u00a0single line commands<\/h2>\n
    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<\/p>\n
    {\r\n scope: 'database',\r\n command: 'eval',\r\n args: { code: 'db.hostInfo()' } \r\n}<\/pre>\n
    Permissions needed to call JavaScript<\/h2>\n
    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<\/a>.<\/p>\n
    Save Result JSON of MongoDB command into SSIS variable<\/h2>\n
    If you want to save MongoDB command output as JSON into SSIS Variable then set Result mode = RawResult<\/p>\n
    \"Call<\/a>
    Call MongoDB JavaScript in SSIS Using Execute SQL Task – Aggregate Command Example – Save raw JSON output into SSIS variable<\/p><\/div>\n
    How to use MongoDB Eval command to call runCommand function<\/h2>\n
    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)<\/p>\n
    {\r\n scope: 'database',\r\n command: 'eval',\r\n args: { code: \"db.getSiblingDB('admin').runCommand( { setParameter: 1, cursorTimeoutMillis: 400000 } )\" } \r\n}<\/pre>\n
    \"Setting<\/a>
    Setting MongoDB Cursor Timeout ( set cursorTimeoutMillis parameter )<\/p><\/div>\n
     <\/p>\n
    Save Result of MongoDB command into SSIS variable as ADO.net Table<\/h2>\n
    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?<\/p>\n
    \"Call<\/a>
    Call MongoDB JavaScript in SSIS – Aggregate Command – Save JSON output as ADO.net Table – Loop Through ForEachLoop<\/p><\/div>\n
    Conclusion<\/h2>\n
    MongoDB is the most popular NoSQL Database engine but integrating that into SSIS can be challenging without right tools. ZappySys SSIS PowerPack comes with\u00a0many MongoDB Tasks\/Components for SSIS<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":1,"featured_media":2115,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[112],"tags":[158,150,6,46,159,12,4],"yoast_head":"\r\nHow to call MongoDB JavaScript using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to call MongoDB JavaScript using SSIS. You can automate and execute any valid MongoDB Shell commands using powerful SSIS Task in few clicks.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to call MongoDB JavaScript using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to call MongoDB JavaScript using SSIS. You can automate and execute any valid MongoDB Shell commands using powerful SSIS Task in few clicks.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2016-10-14T17:31:25+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2023-08-04T11:18:30+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"88\" \/>\r\n\t<meta property=\"og:image:height\" content=\"88\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/\",\"url\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/\",\"name\":\"How to call MongoDB JavaScript using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"datePublished\":\"2016-10-14T17:31:25+00:00\",\"dateModified\":\"2023-08-04T11:18:30+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to call MongoDB JavaScript using SSIS. You can automate and execute any valid MongoDB Shell commands using powerful SSIS Task in few clicks.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"width\":88,\"height\":88},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to call MongoDB JavaScript using SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to call MongoDB JavaScript using SSIS | ZappySys Blog","description":"Learn how to call MongoDB JavaScript using SSIS. You can automate and execute any valid MongoDB Shell commands using powerful SSIS Task in few clicks.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/","og_locale":"en_US","og_type":"article","og_title":"How to call MongoDB JavaScript using SSIS | ZappySys Blog","og_description":"Learn how to call MongoDB JavaScript using SSIS. You can automate and execute any valid MongoDB Shell commands using powerful SSIS Task in few clicks.","og_url":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2016-10-14T17:31:25+00:00","article_modified_time":"2023-08-04T11:18:30+00:00","og_image":[{"width":88,"height":88,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/","url":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/","name":"How to call MongoDB JavaScript using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","datePublished":"2016-10-14T17:31:25+00:00","dateModified":"2023-08-04T11:18:30+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to call MongoDB JavaScript using SSIS. You can automate and execute any valid MongoDB Shell commands using powerful SSIS Task in few clicks.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","width":88,"height":88},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/call-mongodb-javascript-using-ssis-execute-sql-task\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to call MongoDB JavaScript using SSIS"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/811"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=811"}],"version-history":[{"count":16,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/811\/revisions"}],"predecessor-version":[{"id":10190,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/811\/revisions\/10190"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/2115"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=811"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=811"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}