There are times when you want to move data and vary something in the data flow each time that it runs. You might want to only pull records for a particular range of dates, for a particular department, or any criteria that changes from run to run. DataMigrator lets you use a variable in the data flow to accomplish this.
The most common use of variables is in a filter. When the data flow is executed, the variable must be set to an appropriate value.
A value could also be set by executing a pre-extract stored procedure. For example if the value of the variable is read from a flat file or other external source.
The following data flow extracts data from the DMORD table. The filter contains a variable for date, &ODATE, so that each time the flow is run &ODATE can be passed a different value, and records with different dates can be extracted from the data source and loaded into the data target.
The process flow calls the data flow passing a value for the variable as a parameter.
Refer to the sample data flow DMVAR and the sample process flow DMVARPF for the complete example.
In the DMC:
The Column Selection window opens.
The Filter Calculator opens.
= '&ODATE'
after T1.ORDER_DATE in the Conditions field.
The Filter Calculator should appear as shown in following image.
The Transformations window opens.
In order to test the data flow you need to supply a value for the variable. In the DMC:
The prompting window opens. Enter a value to test, for example 2006/02/14.
The data flow is tested using the supplied value. As shown in the following image, the output from the flow appears in the console log window.
To create the process flow that runs both the stored procedure and the data flow:
The Parameters editor opens
The Drill Down Parameter editor window opens.
The parameter appears in the Parameter Editor.
The parameter appears in the property pane.
iWay Software |