Help Manual - MongoDB Source

Documentation on SSIS MongoDB Source component

Using the MongoDB Source Component

The MongoDB Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from MongoDB.

The component includes the following three pages to configure how you want to read data from MongoDB.

  • General
  • Document Designer
  • Column

General Page

The General page of the MongoDB Source Component allows you to specify the general settings of the component.

MongoDB Source

Connection Manager

The MongoDB Source Component requires a MongoDB connection in order to connect with MongoDB. The Connection Manager drop-down will show a list of all MongoDB connection managers that are available to your current SSIS package.

Database
The Database dropdown shows a list of MongoDB Databases available to you. After selecting the Database you wish to read from, the Collection drop-down will be populated with available Collections in the selected Database.
Collection
The Collection specifies the MongoDB Collection you wish to read from. When you choose a collection, the MongoDB Document Importer opens up.

MongoDB Source - Document Importer.png

The Database name and the Collection can be chosen, or left as it was for what was chosen on the general page. Choose the 'Documents to scan" to set how many document records to be scanned to get the design. Set to "0" to read all. And add a query if required. Once done, click "Import" to import the document design, which can be viewed on the "Document designer" page.

Source Type

There are two different types available for retrieving data from MongoDB:

  • Collection: Select the Collection option to read data from MongoDB collections.
  • Change Stream (since v8.0): The Change Stream option allows you to make use of the 'Change Streams' feature in MongoDB 3.6 or later to track transactional data changes for the MongoDB database or collection.
Output as Raw Text

The Output as Raw Text option specifies whether the output should be one single output column that contains the values in Raw Text format for each row returned by MongoDB.

Input Variable Type (since v8.0)

This option is generally used for the Change Stream source type. To start the stream, you would have to choose one of the two options.

  • None (Since v21.2): Choose None to have no input variable set.
  • Operation Time: The input variable is a datetime value that indicates the starting point for the change stream. If the specified starting point is in the past, it must be in the time range of the oplog. For the first run when using this option, the input variable could be any datetime value that you intend to extract data since.
  • Resume Token: The input variable contains a string value of the resume token from the previous execution. If a resume token is provided, the component should attempt resuming change stream events starting after the operation specified in the resume token. For the first run, the input variable should have a string literal value of "None" (without quotes).

In order for this option to work, an Input Variable will be required which is discussed right below.

Input Variable (since v8.0)

Specify an input variable to open a change stream cursor on the collection. The value of the variable can be either a starting datetime point or a resume token, depending on the Input Variable Type option specified above.

Output Variable (since v8.0)

The resume token returned by the MongoDB change stream may be written to a variable and stored for later use (ex. to manage incremental changes) by setting it to the Input Variable for the next execution.

Query

Specify a MongoDB query to filter the documents returned from the MongoDB Collection selected.

Show Variables & Functions Button

Shows the advanced editor window that allows you to create MongoDB filter strings.

The advanced editor allows you to construct MongoDB filter strings from a list of conditions and operators. It also supports injecting variables and parameters into the expression.

MongoDB Advanced Editor

Drag and drop filter string items from the list of available conditional items in the top right tree view. Then modify the expression as needed. You can also drag and drop variables from the top left tree view.

Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve an updated list of Databases and Collections.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic updates of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document that describes the component's metadata including relevant mapping, and so on.

Document Designer Page

The Document Designer page allows you to build the design of the document you are trying to read or import the design from an existing document.

MongoDB Source - Document Designer.png

The Document Designer includes the following two tabs:

  • Details View
  • Additional Settings

In the Details View tab, the top part of the page is used to manually configure the nodes in the design:

  • Add Node: This button will add a new node to your Document design.
  • Remove Nodes: This button will remove a node from your Document design.
  • Direction buttons: These buttons can be used to rearrange the position of the nodes.
  • Rename Nodes: This option allows you to specify how the node name should be represented.
    • Use Qualified Names: When this option is selected, the output/column name will be set to the full qualified node name based on the node location in the document.
    • Use Short Names: When this option is selected, the output/column name will be set to the given Node Name directly.
  • Filter Columns: This option allows you to show or hide certain Columns in the grid.
    • Show Basic Columns: When this option is selected, only basic columns will be shown in the grid.
    • Show All Columns: When this option is selected, all available columns will be shown in the grid.
  • Filter Nodes: This option allows you to filter the list of nodes shown in the grid by typing a keyword in the textbox.

The Details View grid consists of:

  • Node Type: This option allows you to specify the type of the Node in your document design. There are four options available:
    • Array
    • Object
    • Value
    • Raw
  • Node Name: The Name of the Node in the document.
  • Output/Column Name: The name which will be set for the output or the column of a node.
  • Is Repeated: This option allows you to specify if a node is repeated within a document (Available when Show All Columns is selected).
  • Output type: The type of output for a node; available options are:
    • Column
    • Variable
    • Property Name As Column Value
    • Key Value Pivot
  • Output Settings: This option allows you to specify the settings of each output such as the datatype of Value Node Types.

In the Additional Settings tab, you would find the following options:

  • Null Mode: This option allows you to specify the handling of Null values.
  • 'Is Repeated' Text Qualifier: This option allows you to specify the Text Qualifier used in a document when the Is Repeated property is set to True for one or more nodes. There are four options available:
    • Double-quote(“)
    • Single-quote (‘)
    • Tick (`)
    • None
  • 'Is Repeated' Text Delimiter: This option allows you to specify the Text Delimiter used in a document when the Is Repeated property is set to True for one or more nodes. There are seven options available:
    • Newline (\n)
    • Carriage Return (\r)
    • Semicolon (;)
    • Colon (:)
    • Comma (,)
    • Tab (\t)
    • Vertical Bar (|)
  • Output Timezone: The Output Timezone option lets you specify how all datetime fields should be retrieved. Available options are:
    • UTC
    • Local
Import

This option allows you to import the design of your document from one of the following four sources:

  • Designer Settings: Import the design from an existing .designer.settings file.
  • JSON (Local File): Import the design based on a JSON file on your local file system.
  • JSON (Web): Import the design based on the retrieved document from the connection manager.
  • JSON Schema (Local File): Import the design based on a JSON Schema file on your local file system.
Export

Designer Settings: This option allows you to export the current document design to a .designer.settings file which can be used later to import the same design in a different component.

Columns Page

The Columns page of the MongoDB Source Component shows you all available attributes from the table specified on the General page.

MongoDB Columns Page

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns Page grid consists of:

  • Column Name: Column that will be retrieved from MongoDB.
  • Data Type: The data type of this field.

Note: This documentation page applies to the MongoDB source component shipped in SSIS Productivity Pack v21.2 or above. If you are using an older version of the component (v21.1 and prior), please check the legacy documentation page.