File Based Data Exchange
This post is part of a 4-article series covering integration styles for Sage 500 ERP (formerly MAS 500). The purpose is to introduce the subject of Integration Architecture, provide an overview of common approaches found today, and discuss how each can be achieved with Sage 500.
Note: while the focus of this series is on integrating to a specific ERP solution, the underlying principles can be applied to a broad range of enterprise integration projects.
In this article, we’re going to take a look at the 1st of 4 integration styles which is File Based Data Exchange. Other posts in this series cover Direct Database Data Exchange, Remote Procedure Invocation, and Messaging.
But first, let’s take a quick top-level look at the “Big 4 Integration Styles.”
The Big Four of Integration Styles
The following chart provides a high-level summary of the common Integration Styles. This is a derivative of the excellent body of work by Martin Fowler that models the styles more closely with real world practices for SMEs.
To summarize the chart, most projects that integrate data into Sage 500 workflows use a pattern that falls under one of the “Big Four” integration styles:
- Files Based Data Exchange
- Direct Database Reads and Writes
- Remote Procedure Calls / Direct Acting Web Services
- Message Based Architecture
Evaluating Each Integration Style
When evaluating the pros and cons of each style of integration, we will consider the following key points:
- Is it easy to implement?
- What tools existing in the Sage 500 ecosystem to support this approach?
- Is it tightly coupled, in other words, how much knowledge must each side of the integration have of the other?
- What are the challenges involved in handling errors in the integration stream?
- Is it easy to maintain over time as the systems on each side of the integration evolve?
- Is it scalable? What is the overall performance and latency profile of the approach?
- Is it easy to secure?
Without further adieu, let’s look at the first integration style which is File Based Data Exchange.
File Based Data Exchange
Let’s examine more closely the File Based Data Exchange integration style against the questions above.
File Based Data Exchange means that we export a file with data from the External System and import it into Sage 500, or conversely we export data into a predefined format from Sage 500 for import into the external system.
- Is it easy to implement? Yes, this is considered the easiest integration style to implement as almost all systems offer the import and export of data in some text based format (CSV, fixed field, or XML for example). While there are specifics to be answered in terms of how the data files get exchanged, the overall mechanism is universally understood.
- What tools are available within Sage 500? Fortunately Sage 500 offers a number of Data Import Utilities for most of the modules, Data Porter for Spreadsheet based data import directly into the application, and the Data Import Manager which allows more sophisticated mapping and processing of data. It is also easy to export data from Sage 500, either via the stock reports and BIE views, or by utilizing SQL Server Integration Services to produce exported data that conforms to a specified text file format.
- Is this a tightly coupled solution? Not really. File exchange tends to be typified as tightly coupled, but in reality the two systems are very disconnected from each other by the intermediary of the text file and its definition as the proxy container of the data. The source and target systems are ultimately abstracted by this, although the level of abstraction depends largely on the sophistication of the import mechanism in the target system.
- What are the error handling challenges? It is very difficult to automate the response to file import errors, and they often block the import process until resolved.
- Is it easy to maintain over time? It depends on how the file import and export routines are designed in the source and target systems. Theoretically it should be, however in practice this tends to be one of the most expensive integration methods to support over time due to inflexible file definitions and difficulty in modifying import routines. In systems that leverage ETL tools like SQL Server Integration Services though, the story of maintainability and extensibility is greatly improved.
- Is it scalable? File exchange can scale to high volumes of transactions, especially when it is done in a batch-oriented manner. Furthermore it can be scheduled to occur during off-peak times depending on the latency of information that tolerable. However, the difficulty in handling row or field level errors and the relatively high latency of data changes limit the ultimate scalability of this approach to specific scenarios.
- Is it easy to secure? No, file-based data exchange is relatively high risk from a security standpoint unless it is happening all within a well-controlled environment. The integral challenge here is that a file can be altered to extract sensitive information or to inject modified data into a workflow.
Sage 500 ERP offers a number of mature tools for handling file based data exchange with external systems. Data Import Utilities in almost all modules, Data Porter for spreadsheet based data import directly into tasks, and the sophisticated Data Import Manager that leverages SQL Server Integration Services are all available and powerful tools for this integration style.
As you can see, there are a number of pros and cons to File Based Data Exchange.
In part 2 of the 4-part series, we’ll examine Direct Database exchange of data as an alternative approach, exploring answers to the same questions to help us contrast the two integration styles.
Need Help with an Integration Project?
If you need help with an ERP integration project, then contact the Custom Software Development and Integration experts at RKL eSolutions.
Our highly-skilled team of ERP system developers and consultants can help to make your project a success.
Related Posts
Sage 500 Integration Styles: Direct Database Data Exchange
Sage 500 Integration Styles: Remote Procedure Invocation
Sage 500 Integration Styles: Messaging
