Using the MongoDB Source Component

The MongoDB Source Component is an SSIS data flow pipeline component that can be used to read/retrieve data from MongoDB. 

The component includes the following two pages to configure how you want to read data from MongoDB.

  • General
  • Column

General Page

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

MongoDB Source

Connection Manager

The MongoDB Source 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.

Source Type

There are two different types available for retrieving data from MongoDB:

  • Collection: Select Collection option to read data from MongoDB collections.
  • Change Stream (since v8.0): The Change Stream option allows you to make use of the 'Change Streams' feature in MongoDB 3.6 or later to track transactional data changes for MongoDB database or collection.
Database

The Database dropdown shows a list of MongoDB Databases available to you. After selecting the Database you wish to read from, the Collection drop down will be populated with available Collections in the selected Database.

Collection

The Collection specifies the MongoDB Collection you wish to read from. 

Output as JSON

The Output as JSON option specifies whether the output should be one single output column which contains the values in JSON format for each row returned by MongoDB.

Convert ObjectId to String

This will extract the hexadecimal string from the ObjectId method in _id fields. For example, MongoDB will return _id fields in the form of a string literal "ObjectId(507f1f77bcf86cd799439011)", enabling this option allows you to get the hexadecimal string of "507f1f77bcf86cd799439011".

Output Timezone

The output timezone setting determines what format any datetime values read from MongoDB should be converted to. There are two options available.

  • UTC
  • Local: Any datetime values will be converted to the local system time where the package is run.
Operation Type (since v8.0)

Specify the operation type to filter the change stream events. This option is only available when Change Stream is selected as the source type. Available values are:

  • Insert
  • Delete
  • Replace
  • Update
  • Invalidate
Input Variable Type (since v8.0)

There are two different types available for Input Variable values:

  • Operation Time: The input variable is a datetime value indicates the starting point for the change stream. If the specified starting point is in the past, it must be in the time range of the oplog.
  • Resume Token: The input variable contains a string value of the resume token from previous execution. If a resume token is provided, the component should attempt resuming change stream events starting after the operation specified in the resume token.
Input Variable (since v8.0)

Specify an input variable to open a change stream cursor on the collection. Input value can be either a starting datetime point or a resume token.

Output Variable (since v8.0)

The resume token returned by MongoDB change stream may be written to a variable and stored for later use (ex. to manage incremental changes) by setting it to the Input Variable for the next execution.

Filter

Specify a MongoDB filter string to filter the documents returned from the MongoDB Collection selected.

Advanced Editor... Button 

Launches the Advanced Editor that allows you to create MongoDB filter strings.

The Advanced Editor allows you to construct MongoDB filter strings from a list of conditions and operators. It also supports injecting variables 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 from the top left tree view. When done, click the OK button to return to the MongoDB Source 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 Databases and Collections.

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 Source Component shows you all available attributes from the table specified on the General page.

MongoDB Columns Page

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

The Columns Page grid consists of:

  • MongoDB Field: Column that will be retrieved from MongoDB.
  • Data Type: The data type of this field.
  • Properties window for the field selected:
    • Name: Specify the column name.
    • Data type: The data type can be changed according.
    • Length: Specify the Length of the fields. 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 Columns: Option to import the Columns and its properties from a file.
  • Export External Columns: 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.