Script Extensions at the Data Source level can be have several uses.
For example scripts can be used to perform validation, data cleansing and/or data distribution.
Some typical uses:
Use Script Extensions to load source data.
Use Script Extensions from temp to data warehouse table.
Use Script Extensions for primary keys and indexes.
Scripts can be configured to run as an integral part of a data source tables processing. The script can execute either before, after or in place of normal processing.
The following options are available:
Execute a script before processing.
Execute a script after processing.
Execute a script before a transdate reload.
Execute a script after a transdate reload.
Execute a script before an incremental reload.
Execute a script after an incremental reload.
Execute a script before a custom reload.
Execute a script after a custom reload.
Tags commonly used in scripts:
TABLENAME -> refers to the object name of the data source table in context.
STAGINGTABLENAME -> refers to the staging table name of the data source table in context.
RELOADVALUE -> each table can maintain its own RELOADVALUE tag to allow for loading data by dynamically moving the reloadvalue.
DWCONNECTION -> by using the DWCONNECTION tag you ensure dependencies are saved and that the data warehouse database name is not hard coded.
UpdateTagValue:
RELOADVALUE tag and Solution Tags can be updated using the {{UpdateTagValue}} macro in a script.
An example of using the UpdateTagValue can be:
DECLARE @recID int
SET @recID = (SELECT MAX(RecordID) FROM <>)
{{UpdateTagValue(RELOADVALUE,@recID)}}
EmbedDefaultScript:
The {{EmbedDefaultScript}} macro can be used to inject a collection of functions contained in a data source specific default script.
See also