Note: This documentation page only applies to the MongoDB source component shipped in SSIS Productivity Pack v21.1 and prior. If you are using our v21.2 release or later, you should check the new documentation page. In our new versions, we offer many advanced features which were not possible in older versions due to the significant infrastructure updates. Upgrading to the new versions is highly recommended and would provide a lot of benefits.
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.
- 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 the 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 the MongoDB database or collection.
- Database
-
The Database drop-down 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 that contains the values in JSON format for each row returned by MongoDB.
- 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)
-
This option is generally used for the Change Stream source type. To start the stream, you would have to choose one of the two options.
- Operation Time: The input variable is a datetime value which 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. For the first run when using this option, the input variable could be any datetime value that you intend to extract data since.
- Resume Token: The input variable contains a string value of the resume token from the 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. For the first run, the input variable should have a string literal value of "None" (without quotes).
In order for this option to work, an Input Variable will be required which is discussed right below.
- Input Variable (since v8.0)
-
Specify an input variable to open a change stream cursor on the collection. The value of the variable can be either a starting datetime point or a resume token, depending on the Input Variable Type option specified above.
- Output Variable (since v8.0)
-
The resume token returned by the 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.
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 updatea of the property at run time.
- Generate Documentation Icon
-
Click the Generate Documentation icon to generate a Word document that 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.
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: This 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 their properties from a file.
- Export External Columns: Option to save the Columns and their 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.