Procedural Scripting by Record

Procedural scripts allow complex and layered transformations to be written. Using scripts with variables, data can be manipulated across records as well as on a record-by-record basis. ETL Services step through data supplied in an input query (SELECT statement) and then executes a script for each record.

For example, suppose you want to create a new calculation of Revenue based on order line item data in a given source. Further, suppose that data has only two existing data elements: unit price and quantity sold (in addition to keys). You could simply take each record, calculate the revenue by multiplying those two values, and then save the output record to a new data source. With ETL Services this is accomplished by creating a new data source that is based on a script instead of uploaded data. That script consists of a SELECT statement (the input query) and a script block.

Before creating the script, you first need to add the columns to the data source that would be created as a result of the script. For this example, you would add a column for Revenue.

For this example, the SELECT statement is as follows:

SELECT [Order Details.OrderID], [Order Details.ProductID], [Order Details.UnitPrice], [Order Details.Quantity] FROM [Order Details]

This query selects four fields from the data source Order Details. ETL Services executes this query and then for each record that is returned, it runs the associated script:

[OrderID]=[Order Details.OrderID]
[ProductID]=[Order Details.ProductID]
[Revenue]=[Order Details.UnitPrice]*[Order Details.Quantity]
WriteRecord