User Guide > Designing and Executing Map > Setting Up Multimode Target Connectors > Setting Up Multimode Target Connectors
Was this helpful?
Setting Up Multimode Target Connectors
You can set up a multimode target connector in a map in the following ways w:
Setting Up Multimode Target Connector Using Multimode Wizard
You can use the Multimode Wizard when you select a multimode connector from the list of connectors on the Targets tab. Once the connector is selected, the Multimode Wizard appears and assists in configuring the connection. If you want to setup the multimode target connector without using the wizard, then close the wizard and perform the steps provided in Setting Up Multimode Target Connector Without Using Multimode Wizard.
Note:  The Multimode Wizard is not available for those Multimode Target connectors that support hierarchy. Also, the target must support tables and should be a single record target (multi-record layouts must be configured manually). The wizard will also be not available if the source has multiple records.
The wizard will assist in performing the following tasks:
• Specifying connection details: Provide information such as server details, user ID and password, and connector properties.
• Selecting target objects: You can write to a new object or one or more existing objects, but cannot perform both.
• Choosing a target schema: You can associate a schema with the target object using one of the available options:
• Use source schema: When creating a new object, you can use this option to apply the schema that has been defined on the source connection, to the new target object.
• Use existing Table(s): When the Existing Table(s) option has been selected in the wizard, this option applies the existing schema(s) for the selected objects.
• Use an external schema: Manually browse and select a saved schema from disk.
• Manually create schema: Create a single record layout with one field. You can manually add more fields to this schema from the Mapping tab.
• Selecting operations, objects, and keys: By default, the operation is Insert. The other operations are update, upsert, and delete. Object is the table or sheet you are writing to. If the operation is not Insert, then the keys (fields) are required.
• Setting auto-mapping and BeforeTransformation Event options:
• You can automatically map the fields from source to target using the Match by Name or Match by Position options. If the source connector is not defined, then you cannot automatically map the fields.
• BeforeTransformation event: You can add a BeforeTransformation event to initialize the objects. When this event occurs, you can either drop and create an object or truncate (replace) the object.
If you do not specify the BeforeTransformation event, then when the map is run, the engine appends records to the target object.
To setup multimode target connector using the Multimode wizard:
1. In the Map Editor, on the Sources tab, select the required source connector, specify the connection information and click Connect.
2. On the Targets tab, select the required multimode connector from the Choose Connector drop-down menu.
The Multimode Target Wizard is displayed.
3. Specify the target session details such as Server, Data Source, User ID, Password, and the connector properties and click Next.
The connection options and the connector properties are displayed based on the selected multimode connector. For information about the connection options, see Specifying Connector, Parts, and Properties section in Setting Up Target Data. For information about the connector properties, see the required connector in Map Connectors.
4. After you have specified all the target session details, click Next.
The Target Sheets/Tables window is displayed.
Note:  If the connector is Excel-based, then it is Target Sheets. For other multimode connectors, it is Target Tables.
5. Select one of the following:
• New Sheet/Table
• Existing Sheet(s)/Table(s)
6. If New Sheet/Table option is selected, then in New Sheet Name/New Table Name, specify a name for the sheet and click Next.
7. If Existing Sheets/Tables option is selected, then in Select Existing Sheet(s)/Table(s) section, select the check box for the required sheets/tables and click Next.
The Target Schema window is displayed.
8. Select one of the following to choose the schema origin:
• Use source schema: Use the schema provided in the source.
• Use existing Sheet(s)/Table(s): Use the existing schema(s) for the selected sheet(s)/table(s).
• Use an external schema - Use an external schema by specifying the file in the External Schema option.
• Manually create schema - Default record and field will be generated by the wizard. The layout will be displayed on the right hand side of the dialog. You can manually edit the schema on the Mapping tab.
The selected schema origin table is displayed on the right-side of the window.
9. Click Next.
The Multimode Operations window displays with All Instance Records table for the target.
10. Specify the following for each record or use the default values:
• Target Operation - Select Insert, Update, Delete, or Upsert.
• Target Object Name - Click within the cell to modify the object name. Also, specify the keys (fields).
11. Click Next.
The Auto Mapping and Before Transformation Event Options window is displayed.
12. To automatically map the fields from source to target, in the Target Field Auto-Mapping Options section, select the Auto map from source to target. The following options are available:
• Auto map field by Name - Map fields from source to target by name automatically.
• Auto map field by Position - Map fields from source to target by position automatically.
If the Auto map from source to target option is not selected, then you must map the fields manually on the Mapping tab.
13. To initialize the sheets/tables, in the Sheet Initialization Options section, select Add BeforeTransformation Map Events. The following options are available:
• Drop/Create Sheet
• Truncate Sheet
If the Add BeforeTransformation Map Events option is not selected, the data is appended to the target.
14. Click Finish.
The Map Editor displays the new target connector with all the required information.
15. (Optional) Click Connect. The connection is established with the target.
You can now run the map.
Setting Up Multimode Target Connector Without Using Multimode Wizard
To set up the multimode target connector without using the multimode wizard:
1. In the Map Editor, on the Source tab, select the required source connector, specify the connection information and click Connect.
2. On the Target tab, and select the required multimode connector from the Choose Connector drop-down menu.
3. Close the displayed Multimode Wizard.
4. Specify the connection information that is required to connect to the required database or file. For example, Target Database Alias, Data Source, User ID, and so on for a database connection and File for Excel (xlsx).
5. Click next to Schema Selector.
The Multimode Table/View Selector dialog is displayed. Select the required table schema. The selected table schema provide the required structure to visually represent the table(s) or file(s) as a record layout (named as selected table name plus "_1") on the Mapping tab. A default Target record layout R1 is automatically created when the target connector is selected. Make sure to select the Replace existing layouts option on the dialog, if you want to use the selected table (file) as a record layout.
If you want to write to a new table or file using a schema that does not exist, then perform step 7 and 8 to add additional record types to your map.
6. Specify the values for the connector properties if the default values are not appropriate. For more information about connector properties, see the connector specific topics available in the Map Connectors section.
7. Click Connect.
The connection is established with the target. You can now map the source fields to the target. If you want to disconnect from the target, click Disconnect.
8. Click the Mapping tab and then do the following:
a. In the Simple view, right-click a target field and select Set Actian Key to set the key field to perform an update or delete operation for the target. You may set more than one key field, which will be in priorities with the orders of these key fields.
b. Click Advanced View ().
In this view, the Mapping tab is split into four quadrants. To complete setting up the tables or sheets, in the Target tree view, navigate to All Record Types node and/or the All Record Instances node:
• All Record Types: Select this node if you want to add one or more record layout(s) to the map (if the table or sheet does not exist) and build the schema manually. See step 17 for creating new record types.
• All Record Instances: Select this node and open the Properties window from the selected instance grid. In the Properties window, specify the Target Object Name (associated with the record instance) and Target Operation (output modes are insert, update, delete, and upsert). You must set these properties for each record type or instance in the map.
9. To add a new record layout to the map, click All Record Types and then click Add Record Type (). A new record type is added with a default Name and Length as 0. You can edit the Name in the target grid (lower right quadrant). The Length is automatically updated after the fields are mapped to this new record type.
10. To map the source fields to this new record instance:
a. In the Targets node (lower left quadrant), expand the new record instances until you see the required record instance (selected table name plus “_1”).
b. Click the required record instance to display the record layout in the lower right quadrant.
c. Navigate to the appropriate view in the Sources node in the upper left quadrant and drag/drop those fields to the new target.
11. To create any tables or sheets that do not exist:
a. Go to the Configuration tab > Map Events section, and click .
The Add Map Events window is displayed.
b. In the Events section, click and select TransformationStarted from the list.
c. On the True Actions tab > Actions section, click and select TableOperation action for the TransformationStarted event.
d. In the Action Parameters section, specify the following:
– operation type - Select the value as Create.
– target record address - Select the appropriate table or sheet name. You may have to add additional operations to drop or truncate this table if the map is run multiple times.
12. After all the Record Types are created, you must set the Target Object Name and Target Operation properties for each record instance. To set these properties:
a. Click All Record Instances node in the Targets quadrant (lower left quadrant).
The lower right hand quadrant displays the grid or table for All Record Instances. The Properties column displays the icon for each entry in the grid.
b. In the Properties column, click for the required entry in the grid.
The Properties dialog box is displayed.
c. Change the values for Target Operation (output mode) and Target Object Name as required.
d. Click OK to save the changes.
Repeat this process for each record instance that appears in the grid.
13. Complete mapping the fields to the record instances/layouts and/or add any required EZscript expressions.
14. Define the appropriate events and actions for your map. For each record instance, a separate event and action must be set. For information on adding events and actions, see Managing Events and Actions.
Last modified date: 02/09/2024