Derived Column, Copy Column and Data Conversion Transformation

Derived Column Transformation:

A widely used transformation, The Derived Column, helps create new column values by applying expressions to transformation input columns where the expression can contain any combination of variables, functions, operators, and columns from the transformation input. The result can be added as a new column or inserted into an existing column as a replacement value.

E.g. Assume a scenario where we need to bring employee data from file and load into a table. There is a column in the Table “FullName” which should be loaded as “FirstName+LastName”, where “FirstName” and “LastName” are attributes coming from the Source file. Using Derive Column, we can create a new column FullName=FirstName+LastName on the fly(in SSIS buffer) and map that to the Destination table.

                           

Copy Column Transformation: 

This transformation is used if we need to create a copy of a column on the fly.

Data Conversion Transformation: 

This transformation is used to convert the DataType of Columns while transferring the data.

Note:

The functionalities achieved by CopyColumn or a Data Conversion transformation, can be implemented through a Derived Column Transformation, but if the requirement is specific i.e. only create a pure copy of the column without any manipulations or just convert the DataType of a column then it is advisable to use CopyColumn or DataConversion transformations for improved performance.

(A sophisticated mobile/car may have multiple features but will come at a cost. The object size of the Derived Column transformation will be more since it supports multiple features, hence if the functionality is a simple one and can achieved by simpler(and single) transformation then from a performance perspective it always desirable to go with the latter).