Task: Synchronize persons to HelloID via SQL query
Description
The ‘Synchronize persons to HelloID via SQL query’ task can be used for creating persons, based on data from a SQL database.
Task Variables (Synchronize persons to HelloID via SQL query)
Name | Description | Type | Example | Application |
|---|---|---|---|---|
Query | This will be the query which will be used to retrieve data from the SQL database. The query must contain the column name ‘meta.externalId’, this column must contain the identifier for the person. | String | SELECT [person].[Medewerker] as 'person.meta.externalId', [contract].[Functie] as 'employment.contracts[].function' FROM T4E_Persons [person] LEFT JOIN T4E_Contracts [contract] ON [person].[Medewerker] = [contract].[Medewerker] | HelloID |
GenericSystem | Only GenericSystem can be used as a system for this task. Make sure to configure the system at ‘Automation’ > ‘Systems’. | System | GenericSystem | HelloID |
System Variables (Synchronize persons to HelloID via SQL query)
Name | Description | Type | Example | Application |
|---|---|---|---|---|
SQL Username | This is the username to login into SQL. | String | SQLUserName | SQL |
SQL Password | This is the password to login into SQL. | String | SQLPassword | SQL |
SQL TCP connection | This is the SQL server name to which you want to connect. | String | tcp:sqlserver-demo-we.database.windows.net,1433 | SQL |
SQL Database name | This is the SQL database name. | String | SQLDatabaseName | SQL |
How to use
Note: The query needs the column name person.meta.externalId. This will be used as the identifier for the person.
The task synchronize persons to HelloID. The data to build up the persons will be retrieved from a database. The attributes of the person in HelloID are specified in the query, eq. if you want to add the firstname of a person to the person object, you can select a column as the first name. So for example the could look like:
SELECT EmploymentID as 'person.meta.externalId', ContactName as 'person.firstName' FROM T4E_Persons
If you want to add the contract information of a person to the person object, your query could look like:
SELECT EmploymentID as 'person.meta.externalId', Nickname as 'person.firstName', contractStartDate as 'person.contract.startData' FROM T4E_Persons
A person object can contain an array of information. For example, multiple functions for a contract. The query could look like:
SELECT [person].[Medewerker] as 'person.meta.externalId', [contract].[Functie] as 'person.employment.contracts[].function' FROM T4E_Persons [person] LEFT JOIN T4E_Contracts [contract] ON [person].[Medewerker] = [contract].[Medewerker]
When a column name contains [], the data in the column will be used as an array in the person object.