Graphical ETL Data Movement with Data Integration Suite | Introduction

Have you ever found that your applications require data from multiple systems in order to get the job done?  Once you have determined the data and its sources, do you have to pull it into a new database for access by the application? If you were moving data from one database to another such as from DB2 to Oracle, you could leverage the bulk load functionality provided with an ETL tool or better yet, leverage Progress DataDirect Connect and Connect64 for ODBC and it's bulk load functionality without the need for coding.

But what happens if you need to include or join data from an EDI document, mainframe output file, or CSV file before inserting it into the new database?  Extracting data from these sources can be a daunting task, involving the implementation of additional applications either through custom development or additional applications.  Adding these additional elements increase the complexity of the process as well as the possibility for error with regards to passing bad data.  In order to better assist you with the complexities of this business problem, the latest release of the Data Integration Suite (DIS) provides a repository of converters for transforming EDI and Flat File documents such as these into XML providing you a structure that you can query and extract information from. A complete list of the data sources you can use for ETL processes can be found at Data Source Support.

Once you convert the source files, the DIS provides a set of functions for moving and manipulating the data through visualization and mapping capabilities resulting in a set of transformations that can be exposed programmatically for deploying reliable, high-performance data integration.  A subset of these functions is aimed at providing you with a graphical way to map common database functions for the insert, delete, and update of information stored in databases such as Microsoft SQL Server and Oracle.

The database functions expose the details of the target database table allowing you to link the elements from one or more data sources to the desired columns within the table quickly and easily.  In addition to direct mapping, you can add functions for shaping and validating the data before linking with it's target column.  These mapping capabilities will allow you to create complex business logic that can be deployed as a data transformation service or your enterprise applications.

In the next post, I will walk through a sample showing how to use the Data Integration Suite to model a simple process for combining a CSV File with data from an Oracle Database and inserting it into a Microsoft SQL Database.  A how-to video can also be viewed on the Nov. 9 blog post, "Graphical ETL Data Management with DIS."