John Leininger, Senior Technical Consultant, LexisNexis:
Welcome to the InterAction blog! My name is John Leininger. I’ve worked with firms developing client integrations since I joined LexisNexis in 2007. I also provide other technical services such as reporting and data conversions.
For almost two decades, LexisNexis Consulting Services has been helping our clients add valuable information to their InterAction database by implementing a client integration solution. The idea is to set up system that automatically brings CRM-relevant data from a client billing system into InterAction. Typically, such a solution harvests information like…
Once a client integration solution is in place, it brings in new clients and updates existing ones by running a scheduled job each night. Set it and forget it, right? Well, in the ideal world, someone should keep an eye on things occasionally to ensure that the solution continues to work as designed. In the real world, such diligence isn’t a very high priority.
As a result, we occasionally hear from firms for which we’ve implemented integration projects in the past, saying that the client information isn’t up to date. Just how outdated is it? That depends on why the integration is no longer functioning – and how long it took someone to notice it wasn’t! There are lots of reasons why such a system might fail; here are the top ones we’ve found:
1. InterAction was Migrated… but the Integration Package Wasn’t
The most common reason that we find for integration failures is that the solution didn’t get included in the migration plans when InterAction was upgraded. The type of solution that LNCS uses is a Microsoft SQL Server Integration Services (SSIS) package. Here’s a diagram of the overall workflow in an integration solution:
The package itself is contained in a file with the extension “.dtsx”. Scheduling the package to run usually involves a Windows or InterAction scheduled task that runs a batch file that calls the SSIS command line executable DTEXEC.EXE, pointing to the specific location of the package in the file system. The usual configuration looks like this:
Frequently, the client integration package is installed on the InterAction process manager server. Let’s say the original PM server was named IAPMOldAndBusted, and when InterAction was migrated to a set of new servers, the new one was named IAPMNewHotness. The scheduled process running the job could be broken in several ways in this scenario:
2. A Shared Resource is Locked
Sometimes we’ll find that a client integration has stopped working even though no physical changes such as a system migration have occurred. The part of the integration solution that imports data into InterAction is called Application Collaboration (AC). The AC log files might contain the following error message:
8/23/2018 2:37:03 AM “Transfer failed. A shared resource is locked: INT_DTS_CONTACT. The requested action cannot be performed…”
AC contains a safeguard that prevents multiple import jobs from running concurrently. This is facilitated by updating information in a SQL table named INT_DTS_SHARED_RESOURCE. The column named STATUS_IND indicates whether AC is available to start a new job. When AC is ready to begin a new job, this column contains a zero.
When AC executes a job from the command line, the normal sequence of events is as follows:
If something interrupts AC while it’s running a job (e.g., server reboot, loss of data connection), AC does not complete the final step of resetting the status indicator, thus leaving the “2” in the STATUS_IND column. This leaves the database in a condition that makes AC “think” that a job is running, which then prevents AC from being able to launch new jobs. When this occurs, the “Cannot execute dataset [dataset name] because a shared resource is locked” error message is generated whenever AC tries to run a new job. This essentially leaves Application Collaboration in a disabled state.
To “reset” AC so that it can run, the following SQL commands would need to be executed:
select * from IADB.dbo.INT_DTS_SHARED_RESOURCE
update IADB.dbo.INT_DTS_SHARED_RESOURCE set STATUS_IND = 0 where RESOURCE_ID = -1
3. Something Else is Broken
Sometimes it takes a little investigation to determine the cause of an integration package failure. If your integration isn’t working, and the information in this article doesn’t help disclose the culprit, please contact your LexisNexis representative and ask for your issue to be forwarded to Consulting Services. We will arrange to investigate the problem, and if we can resolve it within an hour there will be no charge. If we determine that the resolution will take more time, we’ll provide an estimate of the expected effort required to fix it.