Stored Procedures and User Defined Functions¶

Timextender Data Integration automatically generates the vast majority of the code needed for your data estate, however it is possible to extend the functionality of Timextender Data Integration by writing and executing your own scripts.

Add a Stored Procedure¶

1. Open a data area, and then right-click on the Scripts folder and select Add Stored Procedure.

2. Enter the script for the Stored Procedure. Drag in the necessary objects from the right-hand pane, such as table and field names. Click OK.

3. Right-click on the stored procedure and select Deploy.

4. Once deployed, the stored procedure is visible in the SQL Server Management Studio (SSMS).

Add a User Defined Function¶

1. Open a data area, and then right-click on the Scripts folder and select Add User Defined Function

2. Enter the script for the User Defined Function. Drag in the necessary objects from the right-hand pane, such as table and field names. Click OK.

3. Right-click on the User Defined Function and select Deploy.

4. Once deployed, the User Defined Function is visible in SSMS.

Executing a Stored Procedure¶

1. In a data area, right-click on the Scripts folder and select Add Custom Step.

2. Enter the script for executing the stored procedure. Drag in the necessary objects from the right-hand pane, such as the stored procedure. Click OK.

3. The Stored Procedure needs to be scheduled to run before or after the deployment/execution of a table, which can be done using the Set Pre- and post scripts dialog. To open this, right-click on the table, select Advanced, and then select Set Pre- and post scripts as shown below.

4. In the Set Pre- and post scripts window, select the Custom Step from the dropdown menu for either the Pre-Step or Post Step column, depending on which is appropriate for this custom step related to the deployment/execution. Click OK.

5. Deploy or execute the chosen table, which in turn will run the Custom Step and execute the Stored Procedure.
6. Alternately you can run the stored procedure in a Custom Table Insert as well. It is shown here Add a custom table insert post 4156 and post 4159

​Executing a User Defined Function¶

1. Right-click on the Scripts folder and select Add Custom Step.
2. Enter the script for applying the User Defined Function. In the case below, the User Defined Function is applied via an insert statement. Drag in the necessary objects from the right-hand pane. Click OK

3. The User Defined Function needs to be scheduled to run before or after the deployment/execution of a table, which can be done using the Set Pre- and post scripts dialog. To open this, right-click on the table, select Advanced, and then select Set Pre- and post scripts.
4. In the Set Pre- and post scripts window, select the Custom Step from the dropdown for either the Pre-Step or Post Step column, depending on which is appropriate for this custom step related to the deployment/execution. Click OK.

5. Deploy or execute the chosen table, which in turn will run the Custom Step and execute the User Defined Function. In this example, once the table is executed, the User Defined Function takes the FilePath field from the Documents table

Then it splits this field out into separate records based on the delimiter and inserts these records into the DocumentSplitPath table

Show Script Usage¶

Right-click on the Stored Procedure or User Defined Function and select Script Usage. This will return a list of the Script Actions where the script is used. Select a Script Action and select Go to in order to navigate to the object.

Right-click on a custom step under Script Actions and select Script Usage. This will return a list of the tables where the custom step has been added as a Pre- or post script. Select a table and select Go to in order to navigate to the object.

Downloads¶

• fn_Split_UDF.txt