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.

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 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
  • Adjust to Local System Timezone - Any datetime values will be converted to the local system time where the package is run.
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.