The Integration Engine that ships with Service Desk Express is really rather cool and building an integration package with it is really very easy – or so I thought.
What I wanted to achieve was a way synchronising my client records with a master HR datasource like SAP or Peoplesoft including identifying who the client’s line managers were and their appropriate departments – all without the source HR system having to provide me anything other than a single view of active clients. Easy I thought. Wrong! It was actually a lot more difficult than I had imagined and as such, I thought it might be helpful to create a little demo to a) highlight some of the complexities and b) show you how to get round them.
So before we begin – a little background. For the purposes of this demo I have recreated an HR database that has a very simple schema as shown below:
HR Schema
The view (vw_Active_Employees) from the HR database ONLY shows active clients . This is important as the last thing your network team want is to pull all employees (both active and inactive) each night as over time this will grow to a potentially massive list.
So I built an integration package initially with one step: Synchronise Clients as shown below:
So this all works perfectly and I am able to change people’s details in the HR system e.g. email address and have that synchronise in Service Desk Express. I am also able to add new users and these appear in Service Desk Express.
The first problem comes when I make an existing user in my HR database InActive. Because they no longer appear in the view (remember the view from HR only includes active employees), Service Desk Express doesn’t do anything with them. The obvious solution is to make all the client records in Service Desk Express InActive first and then update them to active IF they appear in the HR view. So how do you do this using the Integration Engine?
It’s actually quite easy but takes a little getting you head around. You need to create a new step that runs before the Synchronise Clients step that has a source and target of Service Desk Express. The source data comes from a simple script:
SELECT [Sequence] FROM [_SMDBA_].[Clients] ORDER BY [SEQUENCE]
The target is then the Clients module and you simply map Sequence to Sequence and hardcode InActive:= 1 as shown below:
Success! Almost. That sorts out the “Leavers” and automatically inactivates those people who are not in the view anymore. The next problem is the departments – what happens if HR adds a new department or renames one or inactivates one?
This one is easy to solve as well in that all you do is create two new steps BEFORE the two we already have:
- InActivate All Departments
- Synchronise Departments
Suffice to say I wont produce screen shots for every step of this process but the important ones are the source data and the mapping:
Notice the SQL statement in the source:
SELECT DISTINCT [DepartmentID], [DepartmentName] FROM [dbo].[vw_Active_Employees] ORDER BY [DepartmentID]
All we are doing here (and it may not be appropriate for your organisation) is synchronising ONLY those departments where there is an active employee. All other departments, as part of the previous step, get inactivated.
Ok, so we are almost there. The final problem was how to handle the SupervisorIDs. The issue here is simply one of timing. The view from HR could have an employee whose SupervisorID is a new starter and consequently an error will occur as the SupervisorID is not in Service Desk Express before trying to be referenced. So how do we fix this issue, which incidentally would be the same for any import where there is a self-referencing foreign key relationship?
All we need to do is amend the Synchronise Clients step such that we don’t map SupervisorID to Supervisor Client ID as shown below:
That will allow all the employee/clients to be synchronised without updating their SupervisorID’s. Once that step is complete and a success we simply add one final step which is a copy of the Synchronise Clients step except that it just updates the SupervisorID’s as shown below:
You can download the final package here.
And that’s it. I hope this has been of help – as always comments, both positive and negative, are always welcome.
JOATIT 