API Adapter-based Job Steps

 

The SQL Server API is used to attach to a database and then invoke any selected Stored Procedures and/or Functions through Job Steps without the need to wrap them in T-SQL code. The first step in the process is to configure the API through the ‘Configure’ button in the Service Library object.

 

 

In the figure above, SQL Server is selected as the adapter and the location of the database and database name are also specified. As the tab indicates these are the Connection properties. There are no Binding properties for this adapter. The Security properties consist of two (2) applicable sections for SQL Server: Windows (for Windows Integrated Authentication) and User (for Database Authentication). Once the applicable properties are entered, click the ‘Apply’ button.

 

 

Clicking on the categories under ‘Select a Category’ displays all the Stored Procedures or Functions that are present in the database. For this example, we’ve clicked on Functions-Scalar which reveals two (2) functions present: Base64_to_text and Text_to_Base64. To add this functions as job steps, we select both and click the ‘Add’ button.

 

 

We’ve added both functions as operations. We can remove one or both through the Remove/Remove-All buttons. Finally the checked list of operations are what will become available as Job Steps when this Service Library is referenced. You can remove an operation from becoming a job step by clearing the checkbox.

 

 

If I drag and drop the first job step over to the workflow it will appear as this:

 

 

In this example, the source is a string that will be converted from text to Base64 encoding. Note the icon to the far left indicating that this job step returns a value. In this example, the value returned is a string that contains the Base64 encoded text.

 

 

Note in the ‘Log’ step, the use of the return value.

 

The Oracle API is used to attach to a database and then invoke any selected Stored Procedures and/or Functions through Job Steps without the need to wrap them in PL/SQL. The first step in the process is to configure the API through the ‘Configure’ button in the Service Library object.

 

 

In the figure above, you would enter the name of the Data Source you want to attach. The tabs for this API are the same as for SQL Server. There are no Binding properties for this adapter. The Security properties consist of two (2) applicable sections for Oracle: Windows (for Windows Integrated Authentication) and User (for Database Authentication). Once the applicable properties are entered, click the ‘Apply’ button.

The operations and actions for SQL Server apply to Oracle.

 

The .Net/COM Assembly API is used to load a .NET Assembly or a COM DLL and then extract the various methods within those files so the methods can be abstracted as job steps for ActiveBatch use. Like all APIs you would select the desired API adapter and then click the ‘Configure’ button.

 

 

In the figure above, you would enter the file specification of the .Net/COM Assembly. There are no other properties for this adapter. The information presented post-Configure is similar to that of the other adapters.

 

 

In the above figure the applicable methods have been selected for use within the Jobs Library.

 

The Command-Line API (CL-API) Adapter allows you to create job steps from command-line programs and their parameters and qualifiers. Typically, a Command-Line based program uses a verb or operation followed by one or more parameters and qualifiers. At its simplest, the CL-API reduces these parameters and qualifiers to Job Step parameters and the verb or operation becomes the Job Step method.

The CL-API features a simple to use editor which allows you to create a map of the underlying command-line structure.

 

 

The figure above displays the initial editor dialog for the CL-API Adapter. Using this editor you can define/manage a CL-API schema; import an existing CL definition or export a CL-API definition. For the purposes of this section we’ll use a CL-API definition for the ‘curl’ command. Curl is a free-ware program that allows you to exchange data between two machines using a wide variety of formats. This definition will focus on a small portion of the command that uses the GET and POST operations for HTTP/HTTPS transports.

 

The “Library Details” section indicates the general structure of the schema for defining a command-line program and its operations to ActiveBatch. The “New Category” is the root of the schema. Once defined, the two main branches are: Command and Dictionaries. Commands are analogous to verbs or operations to the command-line program.

 

Command

 

 

In the figure above, the “Category” has becomes “curl”. The commands are HTTP and HTTPS. For each transport a set of parameters and qualifiers may be specified. These parameters and qualifiers represent the data that will be composed for the command-line. Let’s examine the HTTP command and its related properties.

 

Name: This property is the name of the command or operation.

 

Command Location: This property is the complete file specification for the location of the command-line program. Note: Unless the location is fixed for every Execution Agent you should use an environment variable that can be used to refine the actual location at run-time and whose value can be specific to each machine.

 

Description: This property describes the command.

 

Success Code Rule: This property is used to denote what exit code(s) define success. The syntax used is the same for the Success Code Rule property used within an ActiveBatch job: One or more numeric values either comma separated and/or a range of values as well as the keywords LZERO, EVEN, ODD, NT. The default is 0 (zero).

 

Error Code Dictionary Name: This dropdown allows you to specify a dictionary that can be used to interpret an error code into a string for better error reporting.

 

Allow Command Location Override: If enabled, a job step property will be generated to allow an override for the location of the command-line utility.

 

Allow Success Code Rule Override: If enabled, a job step property will be generated to allow an override for the success code rule.

 

Return Std Out: If enabled, any output written to standard out by the command-line program will be available to the job step as a “Return Value” job step execution variable.

 

Return Std Error: If enabled, any output written to standard error by the command-line program will be available to the job step as a “Return Value” job step execution variable.

 

Return Exit Code: If enabled, the exit code of the command-line program will be available to the job step as a “Return Value” job step execution variable.

 

The Command Preview section shows you what the command and its qualifiers would look like as presented to the command-line program.

 

Argument

 

 

This figure provides an example of a command-line parameter. A parameter is defined as a value that is passed without a preceding qualifier (or switch).

 

Name: This property is the name of the argument (which is a parameter or qualifier).

 

Default Value: This optional property is the default value of the argument that is filled in when the job step property is initially displayed.

 

Qualifier: This property is the actual value of the command-line qualifier. If omitted, the argument is a parameter.

 

Description: This property describes the argument.

 

Optional: This property indicates whether the argument is optional and may be omitted.

 

Hidden: This property indicates whether the argument should be hidden and not displayed with the job step.

 

Quoted: This property indicates whether the value entered should be automatically formatted as a quoted string.

 

Flag: This property indicates whether the qualifier itself has no value associated with it.

 

 

The above figure shows an example of a qualifier that also requires a value.

For a qualifier that never has a value, you would specify the “Flag” checkbox which means the qualifier itself is expected to be passed on the command-line.

 

Dictionaries

 

The Error Code Dictionary facility is used to create a table (dictionary) of error exit codes and their respective error strings. The purpose is to provide a more meaningful error message instead of a numeric exit code.

 

Definition of the CL-API Adapter

 

Once the CL-API schema has been fully defined, you would click the OK button to display the more familiar API adapter dialog. For example:

 

 

With a new schema only the top portion titled “Available Operations” would be completed. In this case, our operations HTTP and HTTPS are displayed. To make those operations available as possible ActiveBatch job steps require that you add them to the “Selected Operations” area. You can do that by selecting the available operations and then clicking the Add button. That function will add the selected operations as well as completing the bottom portion which indicates which selected operations should be displayed as job steps. An enabled checkbox indicates the operation is available as a job step.

 

Use of the CL-API Adapter

 

Once the adapter has been created and saved as part of a Service Library, its use is no different than that of any Service Library and job steps. You would associate the Service Library with the job whose job steps you intend to use. In the example below we’re using the curl CL-API adapter we created.

 

 

First, notice that the cURL category of job steps appear on the standard palette of ActiveBatch job steps. To select one of the cURL job steps you simply drag and drop the selected job step to the main area. The properties that appear as the same as previously defined.