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 three pages of configuration:

  • General
  • Columns
  • Error Handling

General Page

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

MongoDB Destination Editor

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. 

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
Insert as JSON

If the input column data is in JSON format, enable this option. You can send data as JSON for both Insert and Replace actions.

Limit Matched 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 Replace, Update and Delete actions. Note it is always enabled for the Replace action.

Use Upsert

This enables upsert functionality on the current action (If no Documents match the filter string in the MongoDB Collection, a new one is created). This property only applies to the Replace and Update actions.

Batch Size

This is the number of documents to send to MongoDB at a time. This option is only available for the Insert action.

Format _id as ObjectId

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(...)".

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
  • Adjust to Local System Timezone - Any datetime values will be treated as the local system time where the package is run.
Filter String

Specify a MongoDB filter string to filter the Documents to perform an action on. This property only applies to the Replace, Update, and Delete actions.

Advanced Editor... Button 

Launches the Advanced Editor that allows you to create MongoDB filter strings (details below).

The Advanced 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.

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 and input columns from the top left tree view. When done, click the OK button to return to the MongoDB Destination Component Editor with the newly constructed Filter String.

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 update of the property at run time.

Generate Documentation Icon

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

Columns Page

The Columns page of the MongoDB Destination Component allows you map the columns from upstream components to the MongoDB Fields.

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

MongoDB Columns Page

  • Input Column: You can select an input column from an upstream component here.
  • MongoDB Field: The MongoDB field that you are writing data to.
  • 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.
  • Properties window for the field listed. These value are configurable.
    • Name: Specify the Column name.
    • Data Type: The data type can be changed according.
    • Length: 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: Specify the number of digits in a number.
    • Scale: Specify the number of digits to the right of the decimal point in a number.
    • CodePage: Specify the Code Page of the field.
  • Documents to scan to detect fields: Option allows you to specify the number of rows to scan in order to detect the table metadata, mainly the column information.
  • Import External Column: Option to import the Columns and its properties from a file.
  • Export External Column: Option to save the Columns and its properties to a JSON file later for reuse.
  • + sign: Add a field.
  • - sign: Remove a field.
  • Arrows: Move the fields to a desired location in the file.

Error Handling Page

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

MongoDB Destination Editor

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.

Error Output

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

  • _MongoDbResponse: Contains the JSON response from MongoDB
  • _MongoDbAffectedDocuments: Contains the number of affected documents