A New Pill for Your SSIS ETL Headaches

If you’re working with SQL Server 2005 Integration Services (SSIS), you might already have discovered one of the problems with its ETL process: SSIS doesn’t provide an easy way to extract, transform, and load data from unstructured or semi-structured data sources such as Microsoft Excel spreadsheets and reports, raw text files, Oracle databases, and ODBC data sources. According to Vassil Kovatchev, chief technology officer of Interactive Edge, the current SSIS solution for bringing unstructured data into the data flow is to write hard-coded, custom scripts—a time-consuming manual process. Interactive Edge provides a more satisfactory solution with its new Visual Studio plug-in component, DataDefractor. In a recent conversation with our editors, Kovatchev gave an example of a real-estate report in Excel that displayed time-period information in both columns and rows (years in columns, months on the rows). Kovatchev explained, “Writing a script to transform a report like this would typically take about five days. With DataDefractor, the transformation takes about ten minutes.”

            This time savings is enough to make most database professionals sit up and take notice. The DataDefractor tool is a custom SSIS data source flow component that’s fact-oriented and rules-based. The wizard-like interface lets you customize dimensions and measures to quickly transform unstructured or semi-structured data into normalized, usable data. How did Microsoft overlook the need for this kind of component in SSIS? As Kovatchev explained, Microsoft is platform-oriented and is happy to rely on ISVs to fill in the gaps in the platforms it creates. Companies like Interactive Edge can then find opportunities to provide useful tools to make database pros’ lives easier. DataDefractor, which is currently in beta, will be officially released March 16.