System Integration Using a Two-Way SSIS Case Study

The Client

The client is a consulting services company that works on large-scale engineering projects.

Their team and related IT infrastructure have undergone massive growth in recent years.

The Challenge

Our client was using two separate software systems that required a two-way interface between them. One of the software systems was developed in-house while the other was a hosted SaaS solution provided by a third party.

In order to keep both systems up to date, we needed to create a two-way interface that automatically ran once a day. In addition, a report had to run after each scheduled interface which would inform the administrator of any changes made.

Key Challenges

– Create an SSIS package that automated the process.

– Handle encryption and decryption of interfacing files.

– Create daily reports to indicate system status and transfer results.

– Schedule the system to run autonomously.

The Process

  1. Understand the business process and function.
  2. Create individual procedures to handle each step.
  3. Integrate each step into a single SSIS bundle.
  4. Build the reports.
  5. Set up the scheduled job to run the package autonomously.

How Optimus Helped

We built the SSIS package in two phases:

Phase 1: We pulled content from the third party system via FTP, decrypted it, and imported it into the client’s database all using an SSIS package.

Phase 2: We pulled content from the client’s database, encrypted it, and uploaded it to the third party’s FTP from where it was imported into their SaaS provider’s system all by updating the SSIS package from Phase 1.

To accomplish these tasks we created an SSIS package that executed a series of steps. We deployed the package to the client’s production environment where it was set to run once a day on a fixed schedule.

To maintain a high level of security, we utilized GnuPGP to handle encryption and decryption. This allowed us to setup the SSIS package to automatically encrypt and decrypt the interface files using secure keys.

Despite challenges in scheduling the system to run autonomously, the project was successful. Now the SSIS package is scheduled to run on a daily basis which enables a two-way sync. After each run a report summarizing daily changes is generated and sent to the system administrator.