Description:-
Derived Column Transformation
in SSIS plays vital role in dealing with expressions in SQL Server Integration Services.
The SSIS Expression Language has powerful built-in functions for string
manipulation, data type conversions, mathematical functions, conditional
expressions and handling Null values.
We can create the required
expression using those built-in functions, Columns, Operators and variables.
Derived Column Transformation provides an option to choose whether you want to
create new columns or replace existing columns with values calculated from
expressions.
Drag and drop the data flow
task from the toolbox to control flow and rename it as Derived Column
Transformation DFT. Double click on it and it will open the data flow tab.
Drag and drop OLE DB Source,
Derived Column Transformation and flat file destination from tool box.
Configure the OLE DB Source
with your database connection. I have configure with my database like below.
I am sure you will not face
any problem in configuration of source control. Now in next step we have to
drag drop Derived column. Once you drag drop derived column you need to
provide output of source assistance to Derived Column Transformation. Now to
configure this use context menu by right clicking and choose Edit option you
will get below screen. Here we have different functions and operators which we
can be utilized with columns and variables and parameters to create derived
column.
If you see above row in image we can add a new column or
can replace any existing column as shown in drop down.
Here I have add 3 (three) new
rows for to add new columns in destination. In the first row I have added item
name in upper case. Second row I have added configuration for the combination
of different columns in single column. In third column I have added
mathematical expression.
I hope above steps are pretty
much clear. Now add a destination file in which we will get derived column. We
have to configure the derived column. As shown in below figure we configured
derived column.
Now, once the destination file is configured run the
package by pressing F5 or clicking run option. To cross verify the output file.
Thanks for comments.....