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 
Alan
I hope you could throw some light!!
The situation is that we maintain a set of users in a corporate application and for each user account, we intend to provide a self-service account in Service Desk Express (SDE). So obviously we need to import the client details from that application into SDE using Integration Package. All is well, so far “conceptually”.
Here is the twist… we cannot take the password from that application and import into SDE because we don’t store the password in any of our application tables and the login to that application is a DB account and so the password is maintained by Oracle, so it NOT retrievable. So importing the client details using an agreed “constant password” (for all users) and ask them to change the password as soon as they login to their self-service.
The problem… because we set a “constant password” (PS: I dont want to call this as a default password because the term “default” in programming means that will be the value unless the variable already has a value), it replaces the passwords changed by the clients. So I thought I could write an “Advanced” mapping something like:
Sub mapData()
targetRow(“First Name”) = sourceRow(“First Name”)
targetRow(“Last Name”) = sourceRow(“Last Name”)
targetRow(“EMail Address”) = sourceRow(“E-mail Address”)
targetRow(“Phone”) = sourceRow(“Telephone Number”)
targetRow(“Client ID”) = sourceRow(“User Logon Name”)
‘if IsEmpty(targetRow(“Self Service Password”))
‘if IsNull(targetRow(“Self Service Password”))
‘if (LEN(targetRow(“Self Service Password”))=0)
if (targetRow(“Self Service Password”) = “”) then
targetRow(“Self Service Password”) = “Constant Password”
end if
End Sub
None of the above methods worked. So I contacted the BMC support in UK with a simple question:
1. is targetRow readable? or write-only?
2. If it is write-only, how can I update the records with a “constant password” only for those records that doesn’t have a password set and leave the ones with password set as they are?
So far, I think they are still figuring out…
I thought this would be one of the common scenarios. But doesn’t look like. Am i missing something? Or do you have any solutions that I can use?
Hope it is clear. Please feel free to ask if you have a question (directly to my email).
Thanks
Kannan
Hi Kannan,
OK so the good news is that your explanation makes perfect sense to me. In 9.2 and below what you are trying to do was easier as that field in the db was NOT encrypted as it is now. I am away from a test system at the moment but as soon as I am back I will take a look. Whilst hopefully post something back over the weekend.
Regards,
Alan