Creating a script

Yes, technically we created our first script in the previous recipe, but let's be honest—this is not the most advanced script in the world, and it does not provide us with much knowledge regarding scripting language capabilities in Data Services. Finally, although simplicity is usually a virtue, it would be nice to create a script that would have more than one row in it.

In the following recipe, we will create a script that would do some data manipulation and a little bit of text processing before passing a value to a dataflow input parameter.

How to do it…

Clear the contents of your scr_init script objects and add the following lines. Note that every command or function call should end with a semicolon:

# Script which determines name of the job and 
# prepares it for data flow input parameter

print('INFO: scr_init script has started...');

while ($l_JobName IS NULL)  
  begin
    if ($g_JobName IS NOT NULL) 
    begin
      print('INFO: assigning $g_JobName value'
      ||' of {$g_JobName} to a $l_JobName variable...');
      $l_JobName = $g_JobName;
    end 
    else 
      print('INFO: global variable $g_JobName is empty,'
      ||' calculating value for $l_JobName'
      ||' using Data Services function...');
      $l_JobName = job_name();
      print('INFO: new value assigned to a local '
      ||'variable: $l_JobName = {$l_JobName}!');
  end

print('INFO: scr_init script has successfully completed!');

Try to run a job now and confirm that the row inserted into the target HELLO_WORLD table has a proper job name in the second column.

How it works…

We introduced a couple of new elements of scripting language syntax. The # sign defines the comment section in Data Services scripts.

Note that we also referenced variable values in the text string using curly brackets {$l_JobName}. If you skip them, the Data Services compiler will not recognize variables marked with the $ sign and will use the variable name and dollar sign as part of the string.

Tip

You can also use square brackets [] instead of curly brackets to reference variable/parameter values within a text string. The difference between them is that if you use curly brackets, the compiler will put the variable value in the quoted string `value` instead of using it as it is used in the text string.

Scripting language in Data Services is easy to learn as it does not have much variety in terms of conditional constructs. It has a simple syntax, and all its powers come from functions.

In this particular example, you can see one while loop and one conditional construct. The while loop is the only type of loop supported in the Data Services scripting language and the only conditional supported as well. This is really all you need in most cases.

The while (<condition>) loop expression should include a block of code starting with begin and ending with end. The condition check happens at the beginning of each iteration (even the very first one), so keep it in mind as even your very first loop iteration can be skipped. In our example, the loop runs while the $l_JobName local variable is empty.

The syntax of the if conditional element is the same—each conditional block should be wrapped in begin/end. It supports else if, and you can include multiple conditional statements separated by AND or OR. We can use the conditional to check whether the global variable from which we will be sourcing value for the local variable is empty or not. If it is not empty, we would assign it to a local variable, and if it's empty, we should generate a job name using the job_name() function that returns the name of the job it is executed in.

The print() function is a main logging function in the Data Services scripting language. It allows you to print out messages in the trace log file. Look at the following screenshot. It shows an excerpt from the trace log file displayed in one of the tabs in the main design window after you execute the job.

Note

When you execute the job, Data Services generates three log files: trace log, monitor log, and error log. We will explain these logs in detail in the upcoming recipes and chapters. For now, use the trace log button to see the result of your job execution.

Messages generated by the print() function are marked in the trace log as PRINTFN (see the following screenshot). You can also add your own formatting in the print() function to make the messages more distinguishable from the rest of the log messages (see the INFO word added in the example here):