To change a re-usable transformation:
- Select Re-Usable Transformations from the Tools menu.
- Choose the transformation from the Transform Name drop-down list.
- Click on the Model Tab.
- Change the SQL as required.
- Click OK.
Example of a change to the Model SQL
In the example used in Creating a New Re-usable Transformation, the SQL was:
TO_DATE(RTRIM(string_column ),'YYYYMMDD')
Change the SQL to allow the format to be specified when the transformation is used by changing YYYYMMDD to format.
Then highlight the word format and click on the Variable button. This makes the word format a variable that can be substituted when the Re-usable Transformation is used.
Now format is green and in italics:
Click OK.
Applying Changes to Dependant Transformations
After changing a Re-usable Transformation, a dialog appears asking to confirm that changes should be applied to individual columns using the transformation, where possible:
If the Re-usable Transformation doesn't have any dependent columns, then the following message is displayed:
If the Re-usable Transformation has been used for dependant columns then this message is displayed:
When an attempt is made to update a dependant Transformation, and the transformation has been modified in such a way as to make it impossible for the changes to the Re-usable Transformation to be applied, the error message above will include a count of the failures. The Results pane will detail the columns (and tables) where the update failures have occurred: