Complex Data Flow at a Glance

You can define a complex data flow that extracts information from multiple data sources, and copies it into multiple data targets using extract transformations, joins, filters, and sorts to refine the results.

This data flow consists of two data sources, an extract transformation, a Join, a filter, a sort, an SQL object, and two data targets, as shown in the following image.

Complex Data Flow

To create a complex data flow:

  1. Open a new flow.

    The Data Flow tab opens by default, with an SQL object in the center of the workspace.

  2. To add a data source, drag a data source object from the navigation pane into the workspace, to the left of the SQL object.
  3. Add a second data source to the flow.

    The data sources are connected by a join object, which is linked to the SQL object.

    Note: The default setting of the DMC automatically adds a join object to the workspace. If the DMC is not automatically adding join objects, you can change the setting by selecting Add Join Object if needed in the Data Flow Designer user preferences found in the Options dialog box.

  4. Build an expression that creates a new column for extraction in the second data source.
  5. Specify join properties.

    An inner join is the default. If you are using the default setting for the DMC, an inner join is created automatically for columns that are identically named and formatted, if one of them is a key column.

    Note: If an inner join is not automatically created, you can change the setting by selecting Add Join Object if needed in the Data Flow Designer user preferences found in the Options dialog box.

  6. Create an expression that filters the data prior to extraction.
  7. Select the columns you want to extract from the source and copy into the target.

    Tip: You do not need to manually select columns if Automatically select all columns is selected in the Data Flow Designer user preferences found in the Options dialog box.

  8. To add two data target objects to the data flow, drag one from the navigation pane and create the other.
  9. Map the columns from the joined data sources to the existing data target.

    The columns in the new data target are mapped automatically.

    Tip: You do not need to map columns manually if the source and target column names are the same if Automatically map all columns is selected in the Data Flow Designer user preferences found in the Options dialog box,

  10. Save and run the data flow.

    Note: Flow names can be up to 64 characters.

  11. View the log file for data flow details.

Tip: You can create this data flow by completing Exercise 1: Creating the Data Flow. In addition, the exercises in Advanced DataMigrator Tips and Techniques, include several complex data flows that illustrate a range of useful options.


iWay Software