COZYROC MongoDB components, part of COZYROC SSIS+ suite since 2018, are third-party plug-ins for Microsoft SSIS that make it easy to integrate or migrate data to and from MongoDB. You can use any application, service or database supported by SQL Server Integration Services (SSIS) or COZYROC’s toolkit as the source or destination for MongoDB. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The MongoDB integration package consists of:
- MongoDB Connection Manager
- Stores settings for connecting to a MongoDB database.
- MongoDB Source component
- Retrieves data from a MongoDB documents collection.
- Deduces the collection schema by analyzing sample of documents.
- Supports nested documents in array(s) by offering separate outputs for the array items.
- Supports selective retrieval of documents via a query filter.
- Optimized retrieval of only the fields used in downstream components.
- MongoDB Destination component
- Adds, updates or deletes MongoDB documents.
- Deduces the collection schema by analyzing sample of documents.
- Supports 'Create', 'Replace', 'Update' and 'Delete' operations on MongoDB documents.
- Supports nested documents in array(s) by offering separate inputs for 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.
MongoDB Source
In this section we will show you how to set up a MongoDB Source component.
- In the SSIS Toolbox, locate the MongoDB Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, select a pre-configured MongoDB Connection Manager from the Connection drop-down list or create a new one.
- Select Collection.
- Enter Filter if you need to query a subset of the documents in the MongoDB collection.
- The default values of Batch size and Sample size usually don’t need to be changed.
- Select Timezone - UTC or LocalTime.
- When clicking on Columns tab the component would prepare the outputs and external columns by analyzing the existing data in the collection. Please note that the MongoDB Source can have multiple outputs (see the article about composite records), which columns you can see. The data in these outputs can be processed by downstream transformation and destination components(e.g. multiple OLE DB Destinations can store the data in SQL Server database).
- Click OK to close the component editor.
Congratulations! You have successfully configured the MongoDB Source component.
MongoDB Destination
In this section we will show you how to set up a MongoDB Destination component.
- In the SSIS Toolbox, locate the MongoDB Destination component and drag it onto the Data Flow canvas.
- 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.
- Once the component editor opens, select a pre-configured MongoDB Connection Manager from the Connection drop-down list or create a new one.
- Choose the desired Action (Create).
- Select Collection.
- The default value of Sample size usually doesn’t need to be changed.
- Select Timezone - UTC or LocalTime.
- 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.
MongoDB Connection
Overview
MongoDB Connection Manager is SSIS Connection Manager for establishing MongoDB connections.
Demonstration
Parameters
General
Use the General page of the MongoDB Connection Manager dialog to specify properties for connecting to the MongoDB database
The host name(s) and an optional port (default is 27017) using the syntax
host[:port](e.g.localhost:27017). When connecting to a replica set, its members should be enumerated like this:host1[:port],host2[:port]...,hostN[:port].The name of the MongoDB database to connect to. Can be skipped if it is the same as the Authentication Database (available only in Basic authentication) or if it is the only one accessible database by the user.
The authentication method:
Value Description Anonymous Don't send any authentication information. Basic Authenticate by username and password. X.509 Authenticate via X.509 certificate (the settings are available in the SSL tab). LDAP LDAP authentication (MongoDB Enterprise only). Kerberos Kerberos authentication (MongoDB Enterprise only). The username to authenticate with (Basic, LDAP and Kerberos mode)
The password to authenticate with (Basic, LDAP and Kerberos mode)
The database against which the user would be authenticated (Basic mode)
The timeout to attempt a connection before timing out.
SSL
Use the SSL page of the MongoDB Connection Manager dialog to specify SSL/TLS settings
Specify whether SSL encryption is enabled/disabled
Specify whether to allow self-signed SSL certificates
Specify whether to check for certificate revocation
The path to the client certificate file (usually *.PFX)
The passphrase for the client certificate
Advanced
Use the Advanced page of the MongoDB Connection Manager dialog to specify advanced options
Specify additional connection options, when necessary, via the syntax:
name=value1[&name2=value2]...[&nameN=valueN]. Refer to MongoDB documentation for detailed explanation of each option.
Knowledge Base
- Error Message: Unable to connect to a member of the replica set matching Secondary Preferred
- When to connect to MongoDB replica set?
- Where can I find the documentation for the MongoDB Connection?
- I got an error "The GuidRepresentation for the reader is CSharpLegacy, which requires the binary sub type to be UuidLegacy, not UuidStandard."
- How to connect to a MongoDB replica set?
What's New
- New: Updated to use 2.13.3 version driver.
- New: Support for TLS 1.1 protocol and above.
- New: Introduced connection.
Related documentation
Overview
MongoDB Source Component is SSIS Data Flow Component for retrieving data from MongoDB document database that supports multiple outputs via the composite records pattern.
- Besides the main output that contains the fields from the deduced schema of a collection, there are separate outputs (a.k.a. composite outputs), setup for retrieving the field value for array items (those items are basically nested documents within the main MongoDB document).
- An additional standalone output with a single '$JSON' column enables retrieving the entire document contents as JSON string.
- An error output can be setup to redirect problematic records (in case of a failure processing a MongoDB document field value).
Demonstration
Quick Start
In this section we will show you how to set up a MongoDB Source component.
- In the SSIS Toolbox, locate the MongoDB Source component and drag it onto the Data Flow canvas.
- Double-click on the component on the canvas.
- Once the component editor opens, select a pre-configured MongoDB Connection Manager from the Connection drop-down list or create a new one.
- Select Collection.
- Enter Filter if you need to query a subset of the documents in the MongoDB collection.
- The default values of Batch size and Sample size usually don’t need to be changed.
- Select Timezone - UTC or LocalTime.
- When clicking on Columns tab the component would prepare the outputs and external columns by analyzing the existing data in the collection. Please note that the MongoDB Source can have multiple outputs (see the article about composite records), which columns you can see. The data in these outputs can be processed by downstream transformation and destination components(e.g. multiple OLE DB Destinations can store the data in SQL Server database).
- Click OK to close the component editor.
Congratulations! You have successfully configured the MongoDB Source component.
Parameters
Configuration
Use the parameters below to configure the component.
Select an existing MongoDB connection manager.
The number of documents returned per batch. To let MongoDB engine decide what's the optimal batch size, just leave the default value of 0. To disable batching, use a value of 1.
The MongoDB collection from which documents would be retrieved
The optional filter (i.e. MongoDB query) to apply on the selected source collection. Uses the standard MongoDB JSON syntax for query definitions.
An example of a simple query to find a document by its identifier is:
{ "_id" : ObjectId("5a57388f6f8dff702f254717") }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.
Knowledge Base
- Where can I find the documentation for the MongoDB Source?
- MongoDB Source: How to modify the filter property dynamically at runtime
- How to work with composite records handling in a MongoDB source component.
- After modifying the default Data Types in an ETL source, my modifications are lost the next time I open the Source component
What's New
- New: Support reading from views.
- New: Add a custom editor for improved design-time experience.
- New: Improved support for UUID data.
- New: Introduced component.
Related documentation
Overview
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
ObjectIdidentifier for added, updated or deleted documents.
Demonstration
Quick Start
In this section we will show you how to set up a MongoDB Destination component.
- In the SSIS Toolbox, locate the MongoDB Destination component and drag it onto the Data Flow canvas.
- 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.
- Once the component editor opens, select a pre-configured MongoDB Connection Manager from the Connection drop-down list or create a new one.
- Choose the desired Action (Create).
- Select Collection.
- The default value of Sample size usually doesn’t need to be changed.
- Select Timezone - UTC or LocalTime.
- 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.
Parameters
Configuration
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.
Results
Since version 2.2, the component is providing result information in the Result Output. All successfully processed rows are copied to the Result Output. Additionally, the row id and optional status text are returned. Since version 2.2, the Id column is removed from the Error Output, now only error rows are output in the Error Output.
Knowledge Base
- Where can I find the documentation for the MongoDB Destination?
- How to work with composite records handling in a MongoDB destination component.
- How to find which documents failed/succeeded in the MongoDB destination component.
What's New
- Fixed: Regression when using Update/Upsert/Delete actions (Thank you, John!).
- Fixed: Failed to process input columns of type
DT_CY. - Fixed: Modified to preserve the order of the columns from the input.
- Fixed: Failed with error "Command 'listCollections' failed: command not found..." when using MongoDB Atlas (Thank you, Raphael).
- New: Introduced component.
Related documentation
MongoDB Connection
- New: Updated to use 2.13.3 version driver.
- New: Support for TLS 1.1 protocol and above.
- New: Introduced connection.
MongoDB Destination
- Fixed: Regression when using Update/Upsert/Delete actions (Thank you, John!).
- Fixed: Failed to process input columns of type
DT_CY. - Fixed: Modified to preserve the order of the columns from the input.
- Fixed: Failed with error "Command 'listCollections' failed: command not found..." when using MongoDB Atlas (Thank you, Raphael).
- New: Introduced component.
MongoDB Source
- New: Support reading from views.
- New: Add a custom editor for improved design-time experience.
- New: Improved support for UUID data.
- New: Introduced component.
Knowledge Base
- Error Message: Unable to connect to a member of the replica set matching Secondary Preferred
- When to connect to MongoDB replica set?
- Where can I find the documentation for the MongoDB Connection?
- Where can I find the documentation for the MongoDB Destination?
- Where can I find the documentation for the MongoDB Source?
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.