File Interface Staging Engine

File Interface Staging Engine
FI$Cal File Interface
Also known as ‘fizzy’, the File Interface Staging Engine is an Extract, Transform and Load (ETL) middleware framework based on Microsoft technologies (SQL Server, SSIS, .NET) which was specifically designed by KMW Tech to accelerate the implementation of file-based interfaces between Enterprise Resource Planning (ERP) systems (such as PeopleSoft, SAP, Maximo etc.) and enterprise applications residing within an organization’s local area network. FISE was originally built to synchronize the CA Department of General Services (DGS) to the Financial Information System for California (FI$Cal) although it can be used in any Microsoft-centric environment to link local applications to external systems using file transfers. With built-in monitoring, validation, tracking, email notifications and secure access to all interface data, it’s the quickest way to get multiple legacy applications interfacing with FI$Cal in a typical State of California I.T. environment.

Accelerated Interface DevelopmentAccelerated Interface Development

No product can magically know your requirements and implement a system to meet them all. FISE is no exception. Instead, FISE is a framework that implements most of the common functionality that many interfaces require. This includes logging, email notifications, process tracking, file transfers, documentation templates and a web application to monitor everything. With the FISE tool-set in place, specific interface development is accelerated, simple and consistent within the enterprise. We provide the building blocks and training to create the simple SSIS packages (for extract and load) and stored procedures (for transforms and additional validation) required to fully implement the interface in your enterprise. This is an invaluable tool for creating interfaces quickly, especially in a department that requires multiple interfaces. They can all be configured and monitored through a single, easily maintainable, system.

Automated InterfacesAutomated Interfaces

Once an interface has been developed and installed into FISE, it is completely automated. Data flows from source to target on the configured schedule. Notifications (if needed) are emailed to the appropriate people at the appropriate times. Interface data remains in the system according to the configured retention schedule for viewing or further extraction. Just set it and forget it. No more manual file uploads. No more copying and pasting in Excel. No more manual manipulation of files.

FISE has no regular maintenance tasks outside of the ‘normal’ IT department stuff (database backups, OS upgrades, patches etc.)

FlexibleFlexible

FISE uses SQL Server Integration Services (SSIS) packages to connect to application databases and can thus connect using many different technologies including ADO, ADO.Net, Excel, Flat Files, ODBC, and OLE DB which allow connections to various data stores such as SQL Server, Oracle, MySQL, PostgreSQL etc. For a complete list, visit the Microsoft SSIS connection manager page. For file transfers, FISE uses the WinSCP .NET Assembly and COM Library which allows for connections via FTP, SFTP, SCP, and WebDAV (more coming). Interfaces can be scheduled independently or in groups and email notifications can be easily set up using the FISE-Mon web application. The entire system is highly customizable to meet the requirements from super-simple to highly complex interface scenarios.

MaintainableMaintainable

FISE was designed and built for the typical Microsoft based enterprise I.T. department. Each component, including each interface module, is installed separately with version tracking. The usual migration pattern of DEV, TEST, PROD etc. environments is easily followed allowing for parallel development of multiple interfaces on different timelines without worrying about dependencies. Security is also configurable on a per-environment basis using Active Directory groups. FISE requires no third-party licenses. Everything is built using the typical Microsoft software and license-free third-party components. If you have SQL Server (2012 or higher) with SSIS and IIS in an Active Directory environment, then you’re good to go. If something goes wrong, the log is easily viewable (with filtering, sorting, exporting) via the FISE-Mon web application. Configuration is also done through FISE-Mon and other simple GUI applications.

Robust

FISE has been in development for over 3 years and in production at the CA Department of General Services since July 2016. During that time, we’ve encountered all kinds of errors. We see each new error as an opportunity to make the system more robust. When we encounter a new error, we first make sure that it is properly reported in the log so that it is easy to detect (if not already). Next, we build something to guard against the error, provide retries, a workaround or whatever is necessary to keep the system operating despite the occasional error. In our experience, for example, SFTP servers are hit and miss. Thus, when FISE encounters a problem with the file server, it logs it and keeps going. Usually the problem will fix itself and FISE will pick up the file(s) on the next go-around. FISE-Mon now has a chart to track SFTP errors because we see them all the time.

SFTP Error chart

 

For more information about FISE and how to implement it in your organization, please contact us directly.

Stats as of 1/17/2018

Production interface files delivered: 8,756
Internal Databases connected to: SQL Server, Oracle, Flat File
Protocols in use: SFTP, WebDAV, Azure File Storage (LAN)
External Systems connected to: PeopleSoft, Maximo Cloud, US Bank, CA Open Data Exchange

FI$Cal Automated Inbound Interfaces

  • INFAR001 – Inbound Customer Receipts
  • INFAR005 – Inbound Billable Charges and Invoice Adjustments
  • INFAR006 – Inbound Interface AR items from external systems
  • INFPC014 – Inbound billing files
  • INFPC013 – Inbound employee time

FI$Cal Automated Outbound Interfaces

  • INFAR011 – Customer ID Extract
  • INFAR021 – Outbound Invoices
  • INFAP022 – Outbound Checks for printing
  • INFPC024 – Outbound project information
  • INFPC025 – Outbound Interface for Project Structure
  • INFPC011 – Outbound Interface from PC for Project Transactions

FI$Cal Web Service Interfaces

  • INFPC008 – Project creation