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. 

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
Input Document Column

This is the input column that will contain the documents you wish to send to the MongoDB Collection. This property only applies to the Insert and Replace actions.

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.

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.

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. This option is only available for Insert, Replace, and Update. For Insert and Replace the component will fix the _id fields in each row of the Input Document Column. For Update, the component will fix the _id fields for each row within the Update String.

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

Update String

Specify a MongoDB update string to specify the fields to update in matching MongoDB Documents. This property only applies to the Update action.

Projection Mapper... Button 

Launches the Update Mapper that allows you to generate a MongoDB update string from an existing JSON Document and mapping input columns (details below).

Refresh Component Button

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

Advanced Editor

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.

Update Mapper

The Update Mapper allows you to generate a MongoDB update string from an existing JSON Document and mapping input columns.

MongoDB Update Mapper

Click the Import JSON button on the bottom left. You can then map/unmap input columns on the right to the imported fields on the left. Quickly map/unmap fields by clicking the unmap button on the far right. Click the Map Unmapped Fields to map all available fields by name and click the Clear All Mappings button to remove all mappings. When done, click the OK button to return to the MongoDB Destination Component Editor with the autogenerated update string.

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