Updated March 15, 2023
Introduction to SSIS Derived Column
The SSIS is expanded as an SQL server integration system which can be included as a task in the data flow. The derived column transformation process is used to include a new column in a pipeline of data by executing SSIS queries and expressions. The developer can prefer to include a new column or go with the new derived column to swap the existing column. The SSIS expressions can be compiled using functions, operators, and few literals to make a single value. The expression should be written in a unit value, variable, or a single function. It should not be in a complex conditional statement or mathematical operators. The brief operation of the SSIS-derived column is explained in this article.
What is SSIS derived column?
The SSIS-derived column is built with four individual parts variables and column list, expressions that have in-built functions, the derived column grid, and the error handling section. The SSIS-derived column is composed of simple expressions to include a new column in the middle of the data pipeline or data flow. Therefore, the SSIS expression should be executed as a single variable or function and should not include complex values.
How to use SSIS-derived column Transformation?
The transformation in the derived column creates new values in the column by executing the expression to transform or swap the input columns. The expression comprises an association of operators, functions, variables, and columns to transform the input values. The output can be included as a new column instead of the existing one. It can be replaced with multiple derived columns using multiple expressions and can be any variable.
The following task can be made to execute the derived column transformation.
Concatenation of data should be done from different columns into a unit-derived column. For example, the developer can associate the values from last name and first name and change them into a full name of a single derived column for the newly inserted row. The expression can be “first name” + “Lastname.”
Then retrieve the characters from the string by functions like Substring. The output is saved in the derived column. For example, an initial of a person can be extracted by using the expression of SUBSTRING (first name, 1,1 )
Insert mathematical logic to the number data and save the output in the derived column. For example, the length of the decimal place can be changed to two by using the simple expression ROUND (income tax, 2)
The user can also execute expressions to compare the input variables and columns. The version and the product version can be compared and executed. The date and time value can be extracted from GETDATE to get the current year’s date, and the DATEPART is also made to retrieve the same. DATEPART(“year”, fetch date()).
SSIS-derived column Grid
The grid of the derived column consists of specific configurations like the name of the derived column, expression, scale, precision, data type, code page, and length.
- The name of the derived column should be given in the section of the derived column name.
- Then choose the column to be added new and the column that needs to be replaced in the derived column area.
- The SSIS expression which executes the derived column is compiled in the expression part.
- The expression output is shown in the form of data type, with only a column read-option, which comes under the section data type.
- In the length area, the column is read-only and displays the length of the column depending on the result of the expression.
- The column automatically calibrates the value of scale and numbers based on the data type and is sectioned under the scale area.
- The column has a read-only option in the precision field, which measures the precision value for the given numerical data on the data type.
- The column can be changed and automatically measures the code page for the string data type in the code page.
Hence the grid of the derived column comprises a null line used to include a new derived column. If the user starts to fill the properties of this null-derived column, it can be enabled to include more new columns in the existing columns.
SSIS Derived column errors
The configuration of the output error can be included in the data flow task, and it can be worked as an option. The evaluation error at the time of truncation occurs in the form of ignoring failure, failure component, or redirect failure. The evaluation errors can be created by expressions compiled at runtime, and sometimes it leaves invalid operations, or it may become wrong due to syntax errors and incorrect data values.
Examples:
The derived column and use case for the SSIS expression is given below. It can be acted as an external link if required.
To concatenate the first name of the column and its last name to produce a new full name, use the expression (first name of the column) + “” + (end name of the column)
To retrieve some values in a part of the string, then use SUBSTRING ( [topic], 1, 5)
To change the date to the value of an integer in YYYYMMDD format, use,
(date_14) (date_WSTY, 4), YEAR(datecolumn) + right (“” + ) (date_WSTY, 4), month (datecolumn), 3) + right (“” + ) (date_WSTY, 4), day (datecolumn),4))
To replace the empty or null values, use REPLACENULL ([topic], “valid”)
To neglect trailing space and leading values from string LTRIM (RTRIM[topic]))
Conclusion
Hence these are a few important features available in the SSIS-derived column, which can apply to the data pipelines. However, this is mostly preferred developer as it is effective and time-consuming.
Recommended Articles
This is a guide to SSIS Derived Column. Here we discuss the important features available in the SSIS-derived column, which can apply to the data pipelines. You may also look at the following articles to learn more –
