DWBI CONCEPTS

Data Services Scripting Language

Details

Category: ETL BODS

Last Updated on Wednesday, 10 October 2012 04:56

Written by Saurav Mitra

Jobs and work flows can use scripts to define detailed steps in the flow of logic. A script can run functions and assign values to variables, which can then be passed to other steps in the flow.

Expressions are a combination of constants, operators, functions, and variables that evaluate to a value of a given data type. Expressions can be used inside script statements or added to data flow objects.

Scripting Guidelines

• Statements in a script object or custom function must end with a semicolon (;)
• Comment lines must start with a # character.
• Use single quotes for string constants.
• Uses the backslash (\) as the escape character.

  E.g. 
  single quote (') -> ' WHERE NAME LIKE \'SAURAV\' '
  backslash (\) -> 'C:\\DS'
  

  Also strings including curly braces or square brackets cause a processing error. We can avoid the error by preceding the braces or brackets with a backslash (\).

• Data Services does not strip trailing blanks from strings that are used in scripts or custom functions. To remove trailing blanks, we can use the rtrim or rtrim_blank function.
• Variable Names must be preceded by a dollar sign ($).
• Global Variables used in a script or expression must be defined at the Job level using the Variables and Parameters window.
• Local Variables used in a script or expression must be defined in the Job or Workflow context that calls the script, using the Variables and Parameters window; Local variables used in a Custom Function must be defined using the Smart Editor.
• The square brackets ([]) indicate that the value of the expression should be substituted.
• The curly braces ({}) indicate that the value of the expression should be quoted with single quotation marks.

Sample Examples

1. #Drop Table if it exists in Database
   sql( 'DataStore_Name', 'IF EXISTS(SELECT NAME FROM DBO.SYSOBJECTS 
    WHERE NAME = \'Table_Name\' AND XTYPE = \'U\') DROP TABLE Table_Name');
   
   #Create Table in Database
   sql( 'DataStore_Name', 'CREATE TABLE Table_Name( Col1 VARCHAR(10), 
    Col2 INT )' );
   print( 'Table Created' );
   

2. #Print Total Records in a Database Table
   $LV_Count = total_rows(DataStore_Name.Owner_Name.Table_Name);
   print( 'Total Records in Table: ' || $LV_Count );
   
   #Alternatively
   print( 'Total Records in Table: 
    [total_rows(DataStore_Name.Owner_Name.Table_Name)]' );
   

3. #Copy Contents of two files into one
   print(exec('cmd', 'copy "C:\\POC\\Header.txt"+"C:\\POC\\Detail.txt" 
    "C:\\POC\\ConvertedFile.txt"', 8));
   

4. #Delete Data from table
   
   $LV_Load_Date = sql('DataStore_Name','SELECT MAX(load_date) 
    FROM Batch_Control_Tbl');
   
   sql( 'DataStore_Name', 'DELETE FROM TABLE_NAME WHERE load_date =
    {$LV_Load_Date}');
   

5. #  Various ways to define select
   sql( 'DataStore_Name', 'SELECT sal FROM emp WHERE EMPNO ='
    || $LV_EMPNO );
   sql( 'DataStore_Name', 'SELECT sal FROM emp WHERE ENAME LIKE
    {$LV_EMPNO}' );
   sql( 'DataStore_Name', 'SELECT sal FROM emp WHERE ENAME LIKE 
    \'' || $LV_EMPNO || '\'' ); 
   

About the Author

Saurav Mitra is a business intelligence professional.
Connect with the author via Google+