Category:ODBC
Last Updated:2011-08-18
Source Directory Tab – ODBC
ODBC source (database, spreadsheet, text file) can be used as a source of information to update an LDAP directory.
Refer to Importing from a source Database via ODBC Data Source for Source ODBC setup information.
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.
ID: The administrative login to the database
Password: The password that corresponds to the ID specified.
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.
Field Definition
Important note about database column headers that contain spaces:
When a column header contains a space character, the space must be converted to an underscore character for the purpose of attribute definition and mapping. If the source header is "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:
A) 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.
B) 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. Create a Custom Map file to define the desired column/attribute mapping. Use your actual ODBC field names to customize your mappings.
In the products PDF help file, refer to 'Custom Mapping: ODBC/Oracle Source' for more help on custom mapping for your ODBC connection.