Creating a Scripted (ETL) Data Source

You can create a scripted ETL data source. You can write complex and layered transformations with scripts. Using scripts with variables, data can be manipulated across records as well as on a record by record basis. ETL Services works by stepping through data supplied in an input query and then executing a script for each record. See ETL Services Overview. For script examples, see Example Script: Rolling 200 and Example Script: Using an Employee Table to Find Average Headcount Over Any Time Span.

Tip: The Script Wizard makes it easy to combine data from multiple sources and builds the select statement and script code for you. See Using the Script Wizard.

To create a scripted data source

Go to Admin - Define Sources - Manage Sources.
Tip: You can also add a new scripted source from the Data Flow page. Click the Add button at the top of the Data Flow page and select Add Script Based Source.

Click the gear icon

at the top of the Data Sources pane and select Add Script.

Type a name for the new scripted source.

To clone an existing source, check the box and select the source from the list.

Click OK. The name of your new scripted source will be added to the list of data sources on the left with a script icon

. By default the scripted source is ignored.

If you want to use the script wizard to build your script, see Using the Script Wizard, otherwise continue with the steps below to manually write your script.

In the Columns tab on the right, add the columns to the data source that will be created as a result of the script.

After you add new columns in the Columns tab, you must click the Save button before adding the columns to your script in the Script tab.

In the Script tab, write the select statement and the script code for the scripted data source. See Birst ETL Services Input Query Reference and Birst ETL Services Script Functions.

Tip: Click the Format Script link above the Script box when you're finished writing the script to format the script. This will first validate, then format the script based on predefined indentation rules. For example, it will apply the proper indentation for IF/THEN/ELSE statements. Formatting is optional and is intended to improve the readability of the script.

Click the Validate button to check the script for errors. Click the Execute button to output the first 10k records of the script.

If you have a Discovery space, and create a script based on a cloned raw source, no records will be returned when the script is executed if the data has not been processed. You must process the data first.

Tip: You can view the data generated by the script by clicking the Raw Data tab.

10.

Click Save at the top of the page when you are done defining your scripted source.
By default, new scripted sources are ignored. To enable your scripted source, select the source on the left side then click the gear icon and select Ignore Source (which will be checked) as shown below.

Tip: You can also add a new scripted source from the Data Flow page. Click the Add button at the top of the Data Flow page and select Add Script Based Source.