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.
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.
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.
The Collection specifies the MongoDB Collection you wish to write to.
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.
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.
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.
The Update Mapper allows you to generate a MongoDB update string from an existing JSON Document and mapping input columns.
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.
There are three options available.
- Fail on error
- Redirect rows to error output
- 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.
- _MongoDbResponse - Contains the JSON response from MongoDB
- _MongoDbAffectedDocuments - Contains the number of affected documents