Source Tab Configuration: ODBC

Any ODBC Source (database, spreadsheet, text file) can be used as a Source of information to update an LDAP directory.

Create a new connection. On the Connection Creation pop-up, select ODBC as the Source Sync Engine type Source Map Template.

See ODBC Configuration Requirements for details on how to create an ODBC Data Source for use with your UnitySync connection.

ODBC Connection

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.

ID: The administrative login to the database

Password: The password that corresponds to the ID specified.

Multi Value: If any of your Source columns contain multi-valued data, each value will be separated by a special character. If you enter that separator character in the Multi Value parameter, UnitySync will parse out each value so they may be assigned to the Destination attribute individually.

IMPORTANT NOTE: The Destination attribute mapped must be a multi-valued attribute type.

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 data-set. 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 data-set of all employees in your database. “Select * from employees where country = ‘US’ ” would be a more specific and perhaps smaller data-set to sync.

Test SQL: Before you execute your sync, test your query using the Test SQL button.

Source Data

If Test SQL is successful, the Source Data will show the first few rows of output.

Field Definitions

IMPORTANT NOTE: When a column header contains a space character, the space is converted to a dash character for the purpose of attribute definition and mapping. If the Source header is “Last Name” or ?Last_Name? you must reference this column by specifying “Last-Name”, as spaces and underscores are converted to dashes in the ldif.txt file. In fact, the allowed character set on discovery of column header values are now limited to a-z A-Z 0-9 and ‘-’ (dash).

In a Source CSV/ODBC column header, any characters outside of this set are converted to ‘-’ (dash). This applies to field definitions on the Source Tab, as well as mappings in your Custom Object Map or Custom List Map file.

Index: The records in the data-set 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.

IMPORTANT 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 Display Name, 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 Display Name will generate individual values for First, Last and Middle on your Destination object.

3. The logic the Parse Display Name uses is to parse the Display Name into its 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.)

4. If you do not have a single field containing a Display Name, you have two options for how to generate a Display Name:

   • 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.

   • Use your SQL Select statement to produce a Display Name by concatenating fields in your database (i.e. Select First, Last, MI as Displayname…).

Custom Mapping for an ODBC Source

Each ODBC Source is unique and therefore Default Mapping is of limited use. Create a Custom Object Map file as usual. The SMTP address and Name fields will be mapped successfully by default. The rest of the ODBC data Source columns must use custom mapping. See Custom Mapping: ODBC Source for specific information.