Paste Special and Replace Table in DI Studio
There are ways in DI Studio to modify a job so that it uses a different table. One simple and convenient way of doing this is to right-click on the table, and choose "Replace". Thus the following little job...
can easily be changed into this one:
The user-written code here is:
This code does not make reference to any individual column name; instead it uses an array CHARVAR which contains all the character columns in the input table, and checks them all for the string "SHAM". The functions VNAME and VVALUE are used to get the names and values of columns when they are required. This produces error messages such as:
This is a somewhat artificial example (though jobs of this kind can sometimes be useful in testing). Usually the job will have mappings, formulas and so on that use column names. Unless the replacement table has exactly the same structure as the original, the replacement is liable to corrupt such mappings. However, to replace the table and then fix up the mappings is still likely to be less effort than to delete the original table from the job and then add the new one.
A fairly common situation is that several jobs have to be written, each doing the same thing to a different table. DI Studio has a facility called Paste Special that enables new jobs to be created by copying an existing one, with variations.
This demonstration job has been written with the knowledge that other jobs are going to be needed which are very similar, but have other tables in the place here occupied by PRODUCTS. A far-sighted programmer has therefore included an "Extract" node which does nothing, but provides a buffer between the PRODUCTS table and the SQL Join. That join (we suppose) includes complicated conditions and formulas, which it would be undesirable to have to re-enter from scratch for each new job.
To create a new job, right-click on the source folder for the existing job, and choose "Copy". Then right-click on a destination folder, and choose "Paste Special". The resulting dialogue is similar to that which occurs when promoting jobs between environments. One part of it looks like this:
This is the point at which a new table - ORDERS, say - can be selected to take the place of PRODUCTS in the new job. ORDERS will probably not have all the columns that PRODUCTS had, so that Extract node will need its mappings fixing up. The SQL Join node, however, will be unscathed. Its source columns are the target columns of the Extract node, and these have not been affected by the table replacement. So all those complicated formulas in the SQL Join remain intact, and will continue to work once the Extract node has been modified appropriately.