Advanced Derived Column Transform
The "Advanced Derive Column Transform" is used to create columns in an SSIS data flow using columns, variables, and expressions.
Located on the left hand side, the column browser window shows you all the available columns, variables and connections that can be used as part of a derived column expression. Clicking on each plus sign next to each group in the tree view will expand that group of items. Any item from the column browser can be dragged into an expression in the column grid below.
Located on the right hand side, he functions browser window lists all of the available functions that can be used in the advanced derived column (as well as any of the expressions based tasks). All functions are grouped into an appropriate group (e.g. Character, Data Cleansing) based on what the function either does or returns. For instance, the character function Left is listed under the "Character" group because it performs operations on a set of characters or a string. Clicking on a function will give you a description, syntax overview, and examples on how to use the function.
Available Functions List
For a list of all available functions, along with syntax, descriptions, and examples, see the Functions List page.
The bottom window which is the columns grid allows you to define the new derived column, replace columns, and setup variable expression columns that can be used in other column expressions.
• Name - The name of the column or expression.
• Action - The action this column will perform.
o Variable Expression - This option will create a reusable expression that can be used in the expression column of the current column or other columns in the grid.
o Add As New Column - This will create a new derived column based on the expression.
o Replace Column - This will replace an input column's data with the result of the expression.
• Expression - This is the expression used to perform the action from the action column.
• Data Type - The data type for the current column.
• Length - The length of the current column.
• Precision - The precision of the current column.
• Scale - The scale of the current column.
• CodePage - The code page of the current column.
• On Truncation - Action to take when a truncation occurs.
• On Error - Action to take when an error occurs.
• Max Errors to Ignore - How any errors to ignore before failing component.
• Max Warnings - Maximum number of warnings you want to show in the execution log when you have errors or data truncation.
"Validate All" Button
The "Validate All" button will evaluate all of the expressions and check if they have errors. An error or warning icon will appear if there are any issues with the expressions.
"Test Expression (Using Placeholder Values)" Button
This button will create temporary values based on the data type and execute the expression that is currently selected in the grid. To test the expression by entering your own values use the expression editor.