Imagine some typical manual business processes:

• You have been running a business for a few years and every morning a fax arrives from a customer. You take that fax and then sit in front a of a computer to key the order into your system. One day you deliver too much product or the incorrect product because of a keying error which isn't picked up by your dispatch process. This causes a costly recall.
• A control system at the end of your production line signals with a flashing light that a pallet of product is ready to put away into a warehouse. The operator goes to a workstation and then enters this information into your warehouse management system. You discover that you are losing track of pallets because the operator is sometimes too busy to enter data or sometimes enters fantom pallets.
• You have a Weighbridge and accompanying software that keeps track of incoming and outgoing loads. When you raise invoices you have to transfer the details manually to your invoicing system. You perform a stock take which reveals you haven't invoiced all your loads there is some confusion as to which load hasn't been invoiced.

Each of the above scenarios are areas where Systems Integration may provide value to a business.

I have integrated several systems without the benefit of 3rd party or middle-ware components. Here are some of the things I considered, and tools I found useful as I went through the process.

Possible Benefits

• Remove Processing delays
• Eliminate Keying errors
• No more missed transactions
• System A and System B are working with "one version of the truth"

Challenges

• Once systems are integrated there are no eyes on the data. For example incorrect pricing data can persist for months until an audit or internal checks pick it up
• Incorrect data in one system can if unchecked traverse to the other
• If two way integration is required complexity arises
• Custom integration can be expensive

When thinking about System Integration we are aiming to find out the total cost to the business of either staying with manual processing or implementing automation. We also wish to accrue the benefits while avoiding the problems. This requires attention to detail and forethought.

Some questions we could ask ourselves before integration.

• How much time will be saved by doing the integration. X minutes per hour, per shift, per year = Y Savings in a year?
• If we choose to integrate. How soon will we see a pay pack on the investment?
• What is the cost to date of errors introduced by manual processing?
• What will ongoing maintenance if any cost? Will the maintenance be in-business or a more expensive 3rd party?
• If the human element can't be removed can we simplify or make data entry easier?
• Will we need real-time, or batch integration (time or times of day)?
• What sort of alerting is needed to alert a human to a problem?
• How much time is spent chasing missing transactions, customer queries regarding incorrect paperwork and other factors at the moment?
• What business rules are required for the integration?
• What is the volume of transactions?
• Whose cheese will we be moving?
• What training will be needed to move to an integrated system?

Is it worth it?

As a first we need to do a cost to benefit analysis of whether it is worth it. Only then, when we have a clear idea of the cost and potential benefits to the business should we go ahead. If you are quoted $70,000 to enable integration between a control system and an ERP package it may sound very expensive but performing the CtoB analysis may give the green light anyway.

Partial or Complete Integration

How far you want to go depends on your budget and the skills available to implement integration.

Look who's talking and listening?

Identify the systems we need to link and where they are located. The method of communication will change depending on what and where things are located. If you are on a protected network and you aren't worried about eaves-dropping then you don't have to encrypt data. But over the internet or between business partners you may need SSL or VPN solutions to create links.

Depending on what we are integrating (PLC, SCADA, Windows, Linux) there may be an industry standard way of enabling communication. Don't re-invent the wheel if you don't have to.

Work to find a common grammar

Decide on a interchange format. What methods are available on the source system to get information out? What format can it output? How will you transfer and receive data?

 

Business Logic

Are there any rules, specific to the process or your company that need to be abided by? For example using our weighbridge as an example a business rule may be "I only want to transfer transactions from System A to System B where the transaction is for an outgoing delivery (sale)."

Is there any checking and validation that needs to be applied? Such as if the data field containing the weight value is zero or missing send an email alert to [email protected]

Can we use the business logic already in the source or target system to make sure data is accurate instead of coding rules into the system integration? The general rule of IT is that duplication of functionality is bad.

Format Translation

Generally regardless of the output and input format that two systems have, there will need to be some sort of translation or mapping done so that the data is in an acceptable format.

There are many "middleware" products that can do this. From EDI solutions to message queuing applications. However "middleware" is a market that is inhabited by many companies and the costs can be significant.

If you have the skills you can code solutions using standard Windows and Open source tools.

I have had good results with:

• VBS - Visual Basic Scripting good for converting text formats, scripting connections to Microsoft databases, file copies between SMB/CIFS shares.
• VBA - Visual Basic for Applications Scripting - Automate Excel spreadsheets and submitting data to a folder or web service for further processing
• Perl - Practical Extraction and Reporting Language - parsing text files, using XML::* to parse and create valid XML.
• PHP - connection to paradox databases, creating the logic behind a webservice

Script your own transfers

Using Windows or Linux shell scripting you can transfer files using a method suitable for the target system. I have used the following successfully.

• PuTTY - use PuTTYs command utilities plink, pscp, psftp and vbs or windows batch files to script transfers
• wget, curl scripting transfers via FTP, HTTP, HTTPS
• PHP has a curl interface so you can SOAP/XML Posts with relative ease.
• If you aren't doing a complete integration and you still have a user interface then within a browser you can use javascript and jquery to perform click of a button AJAX transfers in SOAP or JSON.

How to trigger transfers

For Windows use Task Scheduler.

On Unix/Linux you can use cron.

To avoid using Task Scheduler or Cron investigate whether the sending system can trigger a process when it has something to send.

Warnings and Alerts

If you have to send some email alerts.

On windows I use bmail.exe (google for the download) or VBS CDO mail.

rem -s send to host
rem -t to email address
rem -f from email address
rem -b body
rem -a Subject
bmail.exe -s 192.168.1.2 ^
-t [email protected] ^
-f %COMPUTERNAME%@example.com ^
-b "pskill failed %DATE% %TIME%" ^
-a "Bridgeware DATE% %TIME%"

On Linux command line use mail

echo There has been an interesting alert |\
mail -s "Subject Here" [email protected]