The Jet Data Manager enables users to integrate "hand-written" code into a project by customizing the data cleansing procedure, transformation view and SSIS package on a given table. This gives the user the flexibility of traditional data warehouse coding together with the immense productivity-boost of Data Warehouse Automation.
Adding Customized Code to a Table
To customize the code on a given table, follow the steps below:
Right click on the table in question, navigate to Advanced and click Customize code . The Customize Code window appears.
Click the Add button to the right of the step to customize. The Choose Editor window appears.
In the Editor Name list there are the following options:
- Standard is the basic built-in editor in the JDM.
- 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 filename extension is .sql. For SSIS packages, the filename extension is .dtsx.
- Any custom editors that have been added (see Managing Custom Editors below).
If adding a SSIS package, the Custom SSIS window appears. Chose Create Default Package to edit the standard package, Create Destination Only to create a package that only contains the destination, or Existing Package to import an existing package from the file system or an SQL Server.
Note: Some tables use multiple SSIS packages. When creating the Default package, the JDM will create the first SSIS package only. Examples of tables that, by default, will have multiple SSIS packages are: Data Warehouse tables that receive data from multiple Staging tables, Data Source tables from a NAV adapter with multiple companies, and any Data Source table when Template Data Sources are used.
If the Standard editor was chosen the Edit window opens. When the user has finished editing the code, click OK to confirm the edits.
If a custom editor was chosen, the JDM will open the code in the editor chosen. When the user has finished editing the code, save the changes and close the editor. Back in the JDM, the Custom Code Editor window is open.
Click Import to import the changes made in the custom editor into the project.
When returned to the Customize Code window the user will notice that they can now click Parameters (if applicable) and Delete . Click Delete to remove the customization and return to having the JDM generate the default code. Click Parameters to decide which parameters are sent to the code on execution.
Click Close to close the window.
Note: When editing the data cleansing procedure or the transformation view, make sure to have a "create procedure" or "create view" declaration in the code with the exact name as the JDM would have given it. This is what is called during execution. To be sure, simply keep the first line of the code generated by the JDM.
Managing Custom Editors
To add, edit or delete a custom editor, click on the Tools tab in the ribbon and then click on General Settings in the Administration group. The General Settings window appears. Click on Custom Editors.
The list of custom editors is displayed. In the Default save location box, the user can type the path to the folder where the custom code files are temporary stored (or click on the folder icon to open a browse dialog).
To edit the settings for a custom editor, select the editor in the list and click Edit .
To remove a custom editor from the list, select the editor and click Delete .
Adding a Custom Editor
Click Add. The Add custom editor window appears.
In the Name box, type a name for the editor.
In the Type list, click on the type of the editor to add. Choose TSQL for use with data cleansing procedures and transformation views and SSIS for use with SSIS packages.
In the SQL Server list, select the SQL Server version that is being used. Currently, this setting is only used for custom editors for SSIS packages. When the user wants to customize the code for a SSIS package, the JDM checks which version of SQL Server the table is stored on. The user will only be able to select editors that are marked compatible with that version of SQL Server.
In the Path box, type the path of the program (or click on the folder icon to open a browse dialog)
In the Parameters box, type any additional parameters for the program.
Optionally, in the Save Location box, type a save location for the editor (or click on the folder icon to open a browse dialog).
Click OK to add the custom editor.