Using the Script Wizard

Infor provides the Script Wizard to assist you with scripted data sources. The script wizard allows you to combine data from two or more sources, and up to six tables and ten joins are allowed. The wizard helps you populate the Select Statement and Script for your scripted source.

You can use the Script Wizard for an existing script by selecting the script and clicking the Wizard button. Make the changes you want, then click Save in the Script Wizard dialog box.

To create a scripted data source using the Script Wizard

1. Go to Admin - Define Sources - Data Flow.
2. Click Add.

Alternatively, from the Manage Sources page click the gear icon at the top of the Data Sources pane and select Add Script.

3. Click Add Script Based Source.
4. Enter a name for the new scripted source in the dialog box that opens.
5. To clone an existing source, check the box and select the source from the dropdown list.
6. Click OK. A new data source for your scripted source will appear on the Data Flow page in light purple, for example:

7. Right-click the new scripted source and select Manage Sources. The Modify Data Source dialog opens.
8. Click the Script tab, then click Wizard . The Script Wizard dialog box opens.

9. If you cloned a source, that source will be selected in the Primary Source list. If you did not clone a source, select the source to use as your primary source from the list. All of the columns in the selected source display at the bottom of the dialog box.

You can select only one Primary Source. If you change the primary source, the columns of the source (and any changes you made to them) will be overwritten by the columns in the newly selected source.
10. Check the Input box of the columns you want to include in the input query (in the Select statement on the Script tab). If there are any columns you do not want to include, uncheck the Input box.
11. Check the Output box to include the column as an output column (in the Script box of the Script tab). The columns marked as output will also be added to the Columns tab of the Modify Data Source dialog box.

You cannot check the Output box unless the Input box is checked.  
12. If you would like to sort on one or more column(s) in your Select statement, click the Sort value and select the type of sort to use for the column from the dropdown. In the Select statement you will see the selected columns listed after an Order By clause. For example:

ORDER BY
  [RevenueForecast.Start Date] ASC,
  [Owner.Owner Role Hierarchy Depth] DESC
13. To create a join definition between the primary source and another source, click Add Join. By default the first table name in alphabetical order will be displayed in the Table Name field under Joins. To change the table, click the name in the Table Name field and click the down arrow to select the table to join from a dropdown list as shown in the following image.



The columns of the table you select will be added to the list of columns in the bottom half of the dialog box after the columns of the primary source.

You can add up to 10 joins.
14. In the Join Condition field, type the join condition to use as in the example below.

15. Click the Join Type field and select Inner, Left Outer, Right Outer, or Full Outer. If you do not specify a join type, it is set to Inner by default.
16. In the bottom half of the dialog box, select the columns from the join table that you want to include in the input and the output.
17. Click Save.
The selections you made in the wizard populate the Select Statement and Script areas of the Script tab. The columns marked as output are added to the Columns tab.

See Also
Creating a Scripted (ETL) Data Source
ETL Services Overview
Ignoring, Emptying or Deleting a Source