In this article, we are going to demonstrate how to load data from a Database to an LDAP server, using the "TOOL Ldap Integrator" which is available in the "templates.generic" Template archive.
This tool can load data from a given CSV file to an Ldap server.
So our process will be designed to unload data from the database to a temporary file, and then load this file into the Ldap server.
Target Ldap structure
For this article, we created an Organizational Unit named "ou=customers,ou=support,dc=example,dc=com".
This OU will contain Ldap objects of class "Person".
We are going to use a "description;lang-fr-FR" and a "description;lang-en-US" attribute in order to show how to handle special attribute names.
For example :
A sample LDIF file to create this structure is:
dn: cn=ACME Joe,ou=customers,ou=support,dc=example,dc=com objectClass: person objectClass: top cn: ACME Joe sn: ACME description;lang-fr-FR: Monsieur Joe ACME description;lang-en-US: Mister Joe ACME
Source data
We are going to use the "T_CUSTOMER" table from Stambia DI's tutorials.
Creating the LDAP Metadata
The LDAP metadata defines how to connect to the LDAP server.
As you can see we only define the Provider URL and the Security Principal & Credentials (the password).
We don't need to specify any Ldap structure at this point.
Creating the Database Metadata
Please refer to the Stambia DI tutorials to reverse the "Hotel" metadata.
In this example we only reversed the T_CUSTOMER table.
Creating the File Metadata
This is a classical File metadata. We chose to set the directory to "${/CORE_JAVA_TEMPORARY_FOLDER}$" so that the file is generated in the Runtime's temp directory at execution time.
For this example, the file fields are all String fields with size 100.
The "pathName" field will contain the target Ldap path for creating the desired Person object.
Note that the description fields have the real Ldap name in their Name. The Physical Name of the field must not have any special char because it will be used by Stambia for generating SQL statements.
Unloading data to the File
For this step we just created a mapping.
Here is the mapping expressions:
| Field | Expression | Comment |
|---|---|---|
| pathName | concat('cn=', concat(T_CUSTOMER.CUS_LAST_NAME, ' ', T_CUSTOMER.CUS_FIRST_NAME), ',ou=customers,ou=support') | We build the target Ldap path, by concatenation of source values and Ldap node names. |
| cn | concat(T_CUSTOMER.CUS_LAST_NAME, ' ', T_CUSTOMER.CUS_FIRST_NAME) | We want the cn to be the person's full name |
| sn | T_CUSTOMER.CUS_LAST_NAME | The sn is usually the Last Name |
| description;lang-fr-FR | Case T_CUSTOMER.TIT_CODE When 'Mr' then 'Monsieur' Else 'Madame' End | This expression transforms the source Title Code into the full Title word, in french. |
| description;lang-en-US | Case T_CUSTOMER.TIT_CODE When 'Mr' then 'Mister' Else 'Madam' End | This expression transforms the source Title Code into the full Title word, in english. |
Here is the Template properties :
We can already execute this mapping and see how the File content looks like:
pathName,cn,sn,description;lang-fr-FR,description;lang-en-US "cn=GIBBS Jason,ou=customers,ou=support","GIBBS Jason","GIBBS","Monsieur","Mister" "cn=O'NEAL Michael,ou=customers,ou=support","O'NEAL Michael","O'NEAL","Monsieur","Mister" "cn=JIMENEZ Tony,ou=customers,ou=support","JIMENEZ Tony","JIMENEZ","Monsieur","Mister" "cn=HAMPTON Lionel,ou=customers,ou=support","HAMPTON Lionel","HAMPTON","Monsieur","Mister" "cn=SILL Joel,ou=customers,ou=support","SILL Joel","SILL","Monsieur","Mister" [...]
Loading the file to Ldap
- Create a Process, we named it "Load T_CUSTOMER to Ldap"
- Drag and drop the mapping in it
- Drag and drop the "TOOL Ldap integrator" in the process
- Create a sucessful link between the mapping and the TOOL
- Drag and drop the Ldap metadata on the TOOL
- Drag and drop the File metadata on the TOOL
- Set the TOOL parameters:
| Parameter | Value | Comment |
|---|---|---|
| Path Name | pathName | so that it matches the File's first field name |
| Export Errors | checked | so that an error file is generated in the Runtime's temp directory, if there are errors |
| matchLdapAttributesWith | logical name | because the File field's "logical name" holds the real Ldap names, not the fields' "physical name" |
| Class Filter | Person | This is the Ldap object class we want to use |
Time to execute for the first time!
And that's all! Let's execute the Process and consult the Statistics view:
- SUM(SQL_NB_ROWS)=100 comes from the Unload step which fetched 100 rows from the source table
- SUM(LDAP_INSERT_DONE)=100 confirms that we have inserted 100 objects into LDAP
Here is how it looks from an Ldap Browser:
What if we execute a second time?
Now, if we execute again the same process, the Statistics will show LDAP_UPDATE_DONE, because the objects already exist and the TOOL is configured for "upsert" mode by default.
If the TOOL was set in "insert" integration mode, then we would get 100 LDAP_INSERT_ERROR rows as a result, because the objects already exist.
