Customized Code and Custom Scripting can be incorporated into your Prepare instance as needed, but should only be used when other TimeXtender Data Integration (TDI) functionality is unable to accomplish your data sourcing and transformation tasks. Depending on how it is used, customized code may impact TDIs ability to perform the following:

1. Managed execution based on object dependencies.
2. Code maintenance.
3. Documentation.
4. Data lineage.

Custom Scripting supports User Defined Functions, Stored Procedures, Script Actions, Code Snippets, and Custom Code. Global Project Variables can also be created for use in custom scripts. The following is an outline of the functionalities that incorporates custom coding and scripts, listed out in the order of those that are relatively simple and easy to deploy, and ending with a more advanced section that explains how to add custom coding to the stored procedures and transformation views that create and comprise a data area table.

Add a Custom View as a Table Insert

Custom views are a means to implement custom code that can be relatively easy to maintain as field mappings can be set and parameters can be used. A Custom view is a better practice than using a Custom Table Insert, which will not maintain data lineage.

1. First create the Custom View.
2. Right-click the view and select Map Custom View fields to set up field mapping, which will maintain data lineage.
3. Drag the custom view onto your data area
4. Provide a Name for the table
5. Deploy and Execute to enact the changes

The following link has more detailed information on creating Custom Views: 

When to use Stored Procedures and User Defined Functions over Script Actions alone

Script Actions are blocks of code that can be saved and run before or after a specific step during Execution.
Stored Procedures (SPs) and User Defined Functions are generally only used in edge cases such as supporting an 3rd party or external components. For example, you may use an SP to make an external call, or setting up special rights and links to a DLL for advanced calculations or program calls.
Stored Procedures are stored in the SQL Server, and compiled the first time that they are run. The execution plan can be reused when running the SP several times, which means faster execution compared to Script Actions. Script Actions are compiled every time they are run, which means that SPs are preferred over Script Actions for processing large amounts of data.

Add a Script Action

1. Select Add Custom Step from Script Actions under Scripts in a data area
2. Provide a Name
3. Enter your SQL script in the textbox, using objects in the right-side pane
4. Click OK

Adding a Script Action to a table as Pre- or Postscript

Prescripts and postscripts are scripted actions that can be configured to run as part of a deployment or an execution, with prescripts being run before the deployment or execution and postscripts being run after.

1. Select Set Pre- and Postscripts under Advanced on a table to use the Script Action
2. Use the dropdown menus Pre Step and Post Step to select when a Script Action or Custom Step should be triggered
3. Click OK.

Adding customized code to a table

TDI autogenerates the stored procedure based on all the settings that have been configured, and will maintain or amend this stored procedure based on new configurations to the data area. If you choose to customize this stored procedure, then TDI will not be able to maintain this stored procedure going forward and any updates to it will need to be done manually. For this reason, customizing stored procedures are generally considered to be the last or final option when setting up a data area.

With those caveats in mind, TDI does provide a way for an instance to integrate "hand-written" code that is used to customize the data cleansing procedure and a transformation view for a given table.

Use the following steps to deploy customized code for a given table:

1. Right click the table > Advanced > Customize code. 

   

2. Click the Add button to the right of the step to be customized, which will open the Choose Editor window.

   

3. In the Editor Name list, the following options are available:
   • Standard is the basic built-in editor.
   • Default File Program is the program that is set to open files of the type in question. For the data cleansing procedure and the transformation view, the file name extension is .sql. 
   • Any custom editors that have been added (see Managing Custom Editors).
4. If the Standard editor is chosen, the Edit window will open. Once the code editing is complete, click OK to confirm the edits. 

5. If a custom editor is chosen, TDI will open the code in the chosen editor. After editing the code, save the changes and close the editor. The Custom Code Editor window will be open in TDI.
   Click Import to import the changes made in the custom editor into your instance.

   

6. When returning to the Customize Code window, note that Parameters (if applicable) and Delete can now be clicked. Click Delete to remove the customization and return to having TDI generate the code. Click Parameters to decide which parameters are sent to the code on execution. 

   

7. Click Close to close the window.