Focus On The Family receives donation-related data from multiple sources in different formats including delimited, Excel, and fixed-length files, as well as printed text and PDF reports. The data often contains duplicates, missing, and incorrect information such as inconsistently formatted names and addresses, missing addresses, etc. This data must be cleansed and reconciled with existing donor data before loading into the operational system. The IT team wanted to shorten their testing time and minimize the risk of integrating new, potentially incorrect data. Previously, they had been working with a multi-week to multi-month turnaround when building ad hoc integration solutions and needed an ETL tool that would deliver consistency, flexibility, speed, and accuracy. 

The integration process needed to meet the organization’s requirements was fairly complex and involved a series of steps and transformations that included receiving and decrypting data files, validating, cleansing, transforming, and onboarding data. Additionally, all data had to be reconciled to ensure that no records were inadvertently omitted. In the past, the Company’s IT team had used SQL Server Integration Service (SSIS) jobs and others to attempt to meet their needs. SSIS, decidedly a programmer’s tool, proved difficult to learn and use and did not provide the required flexibility and agility to meet the goals of rapid turnaround time and data quality.