MongoDB Destination Component is an SSIS Data Flow Component for creating, replacing, updating and deleting documents in a MongoDB database that supports multiple inputs via the composite records pattern.
- Supports 'Create', 'Replace', 'Update' and 'Delete' operations on MongoDB documents.
- Supports nested documents in array(s) by offering separate inputs for populating the array items.
- Supports batch creation of documents.
- Provides information about failures during processing, as well as the `ObjectId` identifier for added, updated or deleted documents.
In this section we will show you how to set up a MongoDB Destination component.
- Double-click on the component on the canvas.
- You first need to generate the structure of MongoDB Destination by selecting a Connection Manager and a collection. You would need to have at least one document in the collection for the component to be able to automatically create the inputs and the corresponding external columns. So double click on the MongoDB Destination component and select "Yes" in the following dialog.
- Connect the blue arrow from the source component or the data flow transformation that you want to immediately precede the MongoDB Destination component. As in MongoDB you can have complex object with nested arrays, MongoDB Destination can have multiple inputs, so you need to select to which input you want to provide data.
To learn more about multiple inputs, please check this article. To load hierarchical data in such inputs, they need to be connected to upstream source or transformation components in a special way (e.g. a Query Transformation can be used to retrieve the necessary data from SQL Server database).
Congratulations! You have successfully configured the MongoDB Destination component.
Use the parameters below to configure the component.
Select an existing MongoDB connection manager.
Select destination object action. This property has the options listed in the following table.
Action Description Create Create new documents in the destination MongoDB collection. Replace Replace the entire contents of existing documents (identified by '_id') in the destination MongoDB collection. Update Updates/sets specified field values in existing documents (identified by '_id') in the destination MongoDB collection. NOTE: Updating fields of array items is not supported. Delete Deletes existing documents (identified by '_id') from the destination MongoDB collection.After changing an action, please use Refresh command in the 'Mapping' tab to reload destination page metadata.
The MongoDB collection where documents would be created/updated/deleted
The number of documents to analyze when deducing the schema of the MongoDB collection (i.e. outputs, columns names and column types).
The time zone for dates processing. Allows converting dates to UTC or local time.
A composite record models an array item in a MongoDB document. The nested document in such an array usually have the same set of fields, thus they are modeled by a separate input (named after the array element path).
For each parent record (e.g. a MongoDB document), the composite input would expect to receive a set of rows (with a row per array item). Those set of rows must be separated with blank row (all NULLs) to denote the end of the array items. The processing of the parent record will not finish until all composite record children have received blank row to indicate completion. Tip: You can use Query transform to prepare parent-child data with the proper blank rows.
For additional information about modelling of composite records in SSIS+, please refer to this article.
The component's Error Output is used for providing both error information and information about the document ObjectId identifier. Check the ErrorCode column:
ErrorCode Description -1 Not an error record. >0 Error record. Check the ErrorDescription column for more details.
You can set up the standard Conditional Split component to filter non-error records.
- Fixed: Failed with error "Command 'listCollections' failed: command not found..." when using MongoDB Atlas (Thank you, Raphael).
- New: Introduced component.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.