Overview

In some cases you need to achieve a task that is not possible through the standard features in the user interface. In these cases you can create your own stored procedures, user defined functions, or even whole script actions to achieve your tasks.

This document will describe how to set up stored procedures, user defined functions, and script actions when the user interface is not able to achieve the task. Before you start using these features please remember that most tasks can be done in the UI, so think carefully about the features available before implementing your own code in a project.

User Defined Functions

Stored Procedures and Script Actions

When setting up stored procedures, user defined functions, and script actions, it is recommended that you use the mark-up capabilities (parameters) in Jet Data Manager to ensure you do not break the project when an object such as a table or a field is renamed. By using the mark-up capabilities you take advantage of the meta data layer in the Jet Data Manager.

User Defined Functions (UDF)

Using User Defined Functions within the Jet Data Manager

Creating a UDF

Add the UDF to the project
Define the UDF

When you drag and drop an object from the window to the right parameters for the object is automatically created. You can rename the parameter but please make sure the parameter in the script window is renamed accordingly. Otherwise you will get the below message.

Also make sure you name the UDF exactly as in your script and use brackets. If the format is different from [schema].[UDFName] then you will get the warning below because the Jet Data Manager cannot verify whether the UDF name is correct or not.
Deploy the UDF

Once the script is as you want deploy it to make it available for use in transformations. The UDF can of course also be called from stored procedures or script actions should this be needed.
Call the UDF from a field transformation

Before you add the UDF to a transformation you need to ensure that functions are not schema bound.

After you have disabled the bind functions you can add the transformation to the field.

Make sure you select Custom as operator and then click Add
Deploy and execute the table/project to apply the transformation

Stored Procedures (SP) and Script Actions (SA)

As with user defined functions, stored procedures and script actions are created when the task you want to achieve is not possible through the features available in the user interface. A stored procedure cannot be called on a field but is normally called through a script action on a table. Standalone script actions should if possible be avoided for any other purpose than calling a stored procedure.

Creating an SP

Add an SP
Define the SP

As with the UDF please make sure you name the SP exactly as in your script and use brackets. If the format is different from [schema].[SPName] then you will get the same warning as for the UDF because the Jet Data Manager cannot verify whether the SP name is correct or not.

If you drag an object twice into the script window or drag two different objects with the same physical name you will be asked either to map the object or to rename. In most cases you simply want to map the objects.
Deploy the SP

Deploy the stored procedure to make it available to the project
Calling the SP

Once created you need to set up a script action before you can call the stored procedure

Give the script action a telling name and call the SP with the EXECUTE command.
Execute the SP

Once the script action has been defined you can call it on the data warehouse/stage or on an individual table.

On the DWH/Stage object you can set the action to be a pre- or post-step to the execution of the DWH/Stage. On an individual table you can set the script action as pre- or post script to the data transfer or as a pre- or post script to data cleansing.
Deploy and execute the table/project to apply the script to the table/project.