Around a month ago, I was tasked with evaluating a client’s integrations as they prepared to upgrade from GP 8 to GP 10. Their system used four Integration Manager integrations to transfer employee, payroll, and invoice data from their operational system regularly.

My initial assessment of these integrations revealed relatively straightforward processes. They pulled data from a handful of SQL tables within a staging database housed on the GP SQL Server and used a few simple scripts to call stored procedures for data preparation before the IM import.

This seemed pretty manageable, leading me to document my observations and confidently state that the upgrade shouldn’t impact these integrations.

Fast forward a month and numerous projects later, the specifics of that initial review had faded from memory. In the meantime, the client discovered their old SQL server was underpowered, necessitating the installation of GP 10 on a new SQL server—a detail that unfortunately escaped my attention.

On the appointed day, the partner successfully executed the upgrade to GP 10 on the new server. Everything seemed smooth sailing until the client attempted to run their integrations.

Integration Manager threw up an error message, stating that the source table couldn’t be located. This seemed strange. Keep in mind, at this point, I was unaware of the new SQL server being in the picture.

Upon examining the table list, a collection of seemingly unrelated objects were present, none of which resembled a staging table. Since the integrations utilized a DSN within IM, I investigated further and found the root of the issue. By default, IM-created DSNs lack the database name in their connection strings. While a new DSN had been established to connect with the new SQL server, the staging database hadn’t been included. Adding the “Database=GPImport” parameter to the connection string rectified this, making the correct tables visible in the Data Source table list.

Problem solved? Not quite. IM now indicated an absence of data in the Employee table and showed all invoices in the invoice table as already imported into GP. This was perplexing.

Here’s a fundamental truth about integrations: source data doesn’t just magically materialize. Something or someone has to prepare and make that data accessible to IM. In my initial evaluation, I hadn’t delved deep enough in this direction.

My focus had been on the integration reading from a SQL staging table, which I assumed wouldn’t be affected by the GP 10 upgrade. The stored procedure called in the Before Integration script also seemed fine and impervious to the upgrade.

However, I failed to consider the implications of migrating GP to a new SQL server. At the time, neither I, the partner, nor the client were aware of this server change, so the potential issues it could cause were never discussed.

Needless to say, problems arose, and plenty of them.

If you’re familiar with the concept of a Rube Goldberg machine, you’ll appreciate the intricacies of this particular integration.

As it turned out, multiple static HTML intranet web pages, seemingly from 1994, hosted on a separate web server, were employing IDC and HTX files to trigger SQL stored procedures. These file types, IDC and HTX, were unfamiliar territory for me, as my data-driven web development experience began with ASP 1.0. In essence, the IDC file housed a DSN, login credentials, and a SQL command in the form of a stored procedure. User access to these IDC files via IIS would then execute the stored procedure.

While that sounds relatively straightforward, this stored procedure initiated a cascade, calling upon several other stored procedures. These, in turn, queried the GP database and, through a linked SQL server, retrieved data, populated cursors, and ultimately fed data into staging tables. But it didn’t end there. These stored procedures also took it upon themselves to manually construct and write HTML report files to the SQL server’s local disk. We’re talking hundreds upon hundreds of lines of SQL code spread across more than a dozen stored procedures. And there’s more! They even leveraged the xp_cmdshell extended stored procedure to map network drives back to the intranet web server for the purpose of copying those HTML files. Yes, you read that right, a stored procedure tasked with mapping network drives and handling file transfers!

If this doesn’t send shivers down your spine, you possess an admirable level of stoicism.

Fortunately, the IDC files consistently referenced the same DSN, allowing me to modify it on the intranet server to point to the new GP SQL Server. Naturally, this also meant setting up a linked server on the new GP SQL server. One might assume we were in the clear at this point, but that wasn’t the case.

It came to light that the BeforeIntegration scripts within IM contained hardcoded references to the old GP SQL server and didn’t utilize RetrieveGlobals. Consequently, these scripts needed updating as well (opting for a simple server name change proved more efficient than implementing the GPConnection object in IM 10).

The real challenge lay in unraveling this intricate web without the aid of any documentation. To add another layer of complexity, the integration had undergone extensive development and modifications by at least two different firms over the span of FOURTEEN YEARS, resulting in code fragments scattered throughout the system. The quest for clues became a treasure hunt, leading me through files and settings across three different servers.

As of today, I believe I’ve successfully restored functionality to all integrations. To avoid a potential catastrophic collapse of this delicately balanced system, I refrained from altering any code, instead focusing on locating and updating all database connection settings.

The key takeaway from this experience was the realization that while my initial assessment of the integrations prior to the upgrade wasn’t entirely off the mark, it lacked thoroughness. Several assumptions were made, the most significant of which was that GP 10 would be deployed on the same server.

This experience prompted me to develop an “integration review template.” This template serves as a tool to aid in identifying potential pitfalls in advance. While it’s not an exhaustive solution, it incorporates several key questions that, if addressed, might have provided a clearer understanding of the complexities involved in generating source data for Integration Manager, allowing for better anticipation of the challenges encountered.

For those interested, I’ve made the template available for download here.

Remember, prioritize safety when it comes to integrations.