Help Manual - MongoDB Destination

Using the MongoDB Destination Component

The MongoDB Destination component is an SSIS data flow pipeline component that can be used to write/send data to MongoDB. You can Insert, Update, Replace, and Delete records using this component. There are four pages of configuration:

• General
• Document Designer
• Columns
• Error Handling

General Page

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

Connection Manager

The MongoDB Destination 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 write to, the Collection drop-down will be populated with Collections in the selected Database.

Collection

The Collection specifies the MongoDB Collection you wish to write to. When you choose a collection, the MongoDB Document Importer opens up.

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 on "Import" to import the document design, which can be viewed on the "Document designer" page.

Action

There are 4 available actions:

• Insert: Send new Documents to the MongoDB Collection
• Replace: Replace entire Documents in the MongoDB Collection based on the Filter String
• Update: Update fields in matching Documents in the MongoDB Collection based on the Filter String and Update String
• Delete: Delete matching Documents in the MongoDB Collection based on the Filter String

Input Timezone

The Input Timezone setting tells the component what format any incoming datetime values are in, and uses this information to write the correct date to MongoDB. There are two options available.

• UTC

Max Degree of Parallelism

This is to set the maximum number of parallelism to MongoDB at a time.

Input as Raw Text

Select this option to specify that the input would be raw text.

Use Upsert

This enables upsert functionality on the current action. This property only applies to the Replace and Update actions.

Limit Matches To One

When this is enabled only one Document can match the Filter String in the MongoDB Collection. Any additional matches are ignored. This property only applies to the Update and Delete actions.

Match By Mode

There are two options to select for Match By Mode.

• Specify Key Fields: This would enable key values to be chosen in the Columns page by selecting the fields to act as matching keys.

• Construct Query: This will expand up the below Match by Query field to construct a query.

Match By Query

The Match By Query editor allows you to construct MongoDB filter strings from a list of conditions and operators. It also supports injecting variables and input columns into the expression.

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 and input columns from the top left tree view.

*Note: The Query by Match option will not be available when using Insert.

Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve an updated list of MongoDB database collection.

Reset Columns Button

Clicking the Reset Columns button will bring up a prompt for you to confirm the reset. After clicking “OK”, it will remove any existing columns and replace them with that of the Input Columns.

Map Unmapped Fields Button

By clicking this button, the component will try to map any unmapped attributes by matching their names with the input columns from upstream components.

Clear All Mappings Button

By clicking this button, the component will reset (clear) all your mappings in the destination component.

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 write to or import the design from an existing document.

The Document Designer includes the following four tabs:

• Details View
• Diagram View
• Preview
• 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.
• Input/Column Name: The name which will be used for the node when writing to the document.
• Is Repeated: This option allows you to specify if a node is repeated within a document. (Available when Show All Columns is selected)
• Format String: This option allows you to specify the target output format for a string when converting datetime/number values to a string. It follows the same .Net formatting function found at this link. (Available when Show All Columns is selected)
• Source type: The type of input for a node, available options are:
  • Column
  • Variable
  • Property Name From Column Value
• Node-Write Settings: This option allows you to specify the settings of each node such as the datatype or the Variable Name based on the selected Source Type.
• 
  • Column Name: The name of the column.
  • Data Type: The data type of the field which can be changed accordingly.
  • Length: This option allows you to specify the Length of the fields. If the data type specified is a string, the length specified here would be the maximum size. If the data type is not a string, the length will be ignored.
  • Precision: This option allows you to specify the number of digits in a number.
  • Scale: This option allows you to specify the number of digits to the right of the decimal point in a number.
  • Code Page: This option allows you to specify the Code Page of the field.
  • Is Object ID(since v22.1): When this option is enabled, the value of the _id field will be wrapped with the ObjectId MongoDB method. It will convert the input value to the form of a string literal "ObjectId(...)".

In the Diagram View tab, you would be able to view the document design as a diagram where you can link different inputs with each other in order to create the hierarchy of your document and establish relationships between the different inputs:

Create New Input

This option allows you to create a new Input and link it to an existing input if required based on the intended document design.

Delete Selected Input

This option allows you to remove existing Inputs from the diagram

Reset Layout

This option allows you to reset the layout of the diagram which would reorganize the location of each input.

In the Preview tab, the component will show a sample of the document which will be created with test values.

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

• Null Mode: This option allows you to specify the handling of Null values. There are 3 options:
  • Don't Show
  • Empty String
  • Null Value
• '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 (|)

Import

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

• Designer Settings: Import the design from an existing .designer.settings file.
• Input (Reset Design): Create the design based on the input columns for the upstream components.
• JSON (Local File): Import the design based on a JSON file on your local file system.
• 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 Destination Component allows you to map the columns from upstream components to the MongoDB Fields.

On the Columns page, you would see a grid that contains four columns as shown below.

• Input Column: You can select an input column from an upstream component here.
• MongoDB Field: The MongoDB field that you are writing data to.
• ObjectId(since v22.1): This column indicates if the field value will be wrapped with the ObjectId MongoDB method.
• Data Type: This column indicates the type of value for the current field.
• Unmap: This column can be used to unmap the field from the upstream input column, or otherwise it can be used to map the field to an upstream input column by matching its name if the field is not currently mapped.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

There are three options available.

1. Fail on error
2. Redirect rows to error output
3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to write to MongoDB will be redirected to the 'Error Output' output of the Destination Component. As indicated in the screenshot below, the green output connection represents rows that were successfully written, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by MongoDB or the component itself.

Note: Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.

Enable Columns for Default Output

Depending on the action chosen, the below would be available.

• _Result.InsertedId
• _Result.IsAcknowledged
• _Result.UpsertedId
• _Result.IsModifiedCountAvailable
• _Result.MatchedCount
• _Result.ModifiedCount
• _Result.DeletedCount

Note: This documentation page applies to the MongoDB destination 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.