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.

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 checking Add Join Object if needed in the Data Flow Designer branch of the User Preferences tree from 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: An inner join is created if Automatically add join conditions is checked in the Data Flow Designer branch of the User Preferences tree from 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: If Automatically select all columns is checked in the Data Flow Designer branch of the User Preferences tree from the Options dialog box, you do not need to manually select columns.

  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: If Automatically map all columns is checked in the Data Flow Designer branch of the User Preferences tree from the Options dialog box, you do not need to map columns manually if the source and target column names are the same.

  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