Dataflows: The Cornerstone of Data Integration Examples 1-10

Example 1. Simple dataflow reading source tables using a multi-table query and inserting records to an Oracle database table. The data is transferred ‘as is’ with no additional transformations, using direct mapping between source and destination fields.

Example 2. Complex dataflow. This dataflow reads data from several related tables storing customer data. CustomerAddress source table and Address source table are joined on AddressId field to derive the joined record, which is then sent to a filter object to discard records not meeting the filter criteria. The resulting record set is then joined with the Customer record to create a hierarchical structure with Customer data stored at the parent node and customer address entry stored at the child node’s level. The entire structure is written to an XML file.

Example 3. A simple dataflow merging records from two SQL queries and writing the merged records to a SQL Server 2008 database table.

Example 4. This dataflow performs parallel writes to an XML file and a delimited file. Both destination files are fed by a single multi-table query, although different field maps are used between the source and the two destinations.

 

Example 5. This dataflow transfers data between a source data model and a fixed-length file destination. A record-level log capturing the status of each record is written to a separate log file.

 

Example 6. This dataflow reads a Customer table, filters out records where PostalCode is greater than 79902, and sends the ContactName field to a name parse function (Function1) which parses the name and writes the parts of the name to a delimited file in a comma delimited format.

 

Example 7. This dataflow transfers records between two QuickBooks tables using custom mapping between source and destination fields. Two constant values are also written to ListID and EditSequence fields at the destination. The status of each record is captured by the log object.

 

Example 8.  This dataflow performs parallel writes to a delimited file and fixed-length file from a single Quickbooks source table.  Note that the two destinations receive exactly the same data as they share identical field maps.

 

Example 9. Dataflow reads customer names from a database table, then applies a name parsing function and expression (GetRealName and CombineFullName) to create a full name using a naming standard. The full name is then split into fields using a string split function (Function1) and finally written to an Excel file.

 

Example 10. This dataflow reads a single text stream containing an XML document. XML parser processes the input stream and outputs the elements and attributes in the XML document which are then written to a delimited file and a database table. A record level log is also created to capture the status of each record as it is written to the destination database table.