ODBC Source tab configuration

ODBC sources (databases) can be used to update an LDAP directory.

Source Tab: ODBC Connection Information

System DSN: When using an ODBC source you must add a DSN with the proper drivers so that UnitySync can access the data. After you have created this DSN entry, enter it here. (Note: Must use 64-bit ODBC)

ID: The administrative login to the database

Password: The password that corresponds to the ID specified.

Source Tab: SQL Query

SQL Query: UnitySync will execute an SQL query against the DSN. The results of this query will produce the data that will be synced. You must provide a valid SQL statement to produce the necessary dataset. Writing your own SQL statement provides you with the ability to be as broad or specific with the records you sync. For example, “Select * from employees” would provide you a dataset of all employees in your database. “Select * from employees where country = ‘US’ ” would be a more specific and perhaps smaller dataset to sync.

Test SQL: Before you execute your sync, test your query using the Test SQL button. If successful, the Sample Data will show the first few rows of output.

Source Tab: Field Definition

Important NOTE about database column headers that contain spaces: When a column header contains a space, underscore, or any other non-alphanumeric character except for a dash, it will be converted to a dash character for the purpose of attribute definition and mapping. If the source header is “Last Name”, “Last_Name”, or “Last&Name” you must reference this column by specifying “Last-Name”. This applies to Field Definitions on the Source Tab, as well as mapping definitions in your Custom Map file.

Unique Index: The records in the dataset being used as the source must contain a field that holds unique information necessary to produce a unique index in the destination. Typically this is the SMTP address or a User ID.

SMTP Address: Identify the source attribute that contains the SMTP email address. If your custom ODBC map file uses the email address in the destination dn, then the SMTP Address attribute must be populated for all source records.

Group Membership: In order to create groups and apply the appropriate membership, your source must include a column to identify the Groups each object should belong to. Specify the column that contains the Group Membership information. This should only be specified if syncing Groups as Groups. The value(s) specified in this column of your Source data will be used to 1) create the new Group(s) based on the value(s) and 2) add members to the new group.

NOTE: Since the sync is creating these groups dynamically, the new group will NOT contain an email address.

Display Name, Personal Title, First, Middle, last, GQ: There are several ways to define a display name, and individual name parts, in the destination directory from your source ODBC data:

1. If you have a single displayname field in your database that contains first, last, MI, etc., then that field can be specified in the Display Name parameter. Likewise, if you you have individual fields for the individual name parts (First, Last, Middle etc) then each field name can be entered in the appropriate parameter to identify each field containing that data (First, Middle, Last etc). If your source data does not include one of the name parts (i.e. Title, GQ) then leave it blank.
2. If you have a DisplayName, but no individual name parts, you can specify the display name in the Display Name parameter, and set the Parse option to Yes under the Display Name tab. Parsing the DisplayName will generate individual values for First, Last and Middle on your Destination object. The logic the Parse Display Name approach is: Parse the Display Name into it’s parts and populate the appropriate attributes. Then, format the Display Name as defined on the Display Name Options screen (First, Last, Same as source, etc.)
3. If you do not have a single field containing a DisplayName, you have two options for how to generate a Display Name:
   1. If you have individual fields for all name parts, identify the name parts fields in the appropriate parameters (First, Middle, Last etc), set the Parse Option on the Display Name tab to NO and select select a Display Name Format (on the Display Name tab) that uses the individual fields. i.e. Last, First.
   2. Use your SQL Select statement to produce a Display Name by concatenating fields in your database (i.e. Select First, Last, MI as Displayname…).

The Source Data File

The source file can be of any format for which you have an ODBC Data Source.

NOTE: If your file is a simple CSV data file, we recommend the CSV source engine instead of using the ODBC engine outlined in this article.

Custom Mapping for an ODBC Source

Each ODBC source is unique and therefore default mapping is limited. The SMTP address and Name fields will be mapped successfully by default. The rest of the ODBC datasource columns must use custom mapping.

Remember, non-alphanumeric headers will not work for your attribute mapping. When a column header in your ODBC Source contains a space, underscore, or any other non-alphanumeric character except for a dash, it will be converted to a dash character for the purpose of attribute definition and mapping. If the source header is “Last Name”, “Last_Name”, or “Last&Name” you must reference this column by specifying “Last-Name” in the custom object map.

In the Administrator’s Guide, refer to ‘Custom Mapping: ODBC/Oracle Source’ for more help on custom mapping for your ODBC connection, and Create a Custom Object Map file to create your custom object map.

Refer to Importing from a source Database via ODBC Data Source for more Source ODBC setup information.