SSIS Derived Column Transformation is one of the SQL Server Integration Services that can be added within a Data Flow Task, it is used to add a new column to the data pipeline by applying SSIS expressions. Concatenating first name and last name into one column. We can choose whether to add a new derived column or to replace an existing column.
Procedure
- Creating package under Project “doc”.
- Under Solution Explorer we need to rename the package as required. i.e. DERIVED COLUMN.dtsx.
- Drag and Drop the Data Flow Task from the toolbox to the control flow region. Add a name to it of your choice.
- Double-click on the Data Flow Task. Drag and drop the OLEDB source from the toolbox menu.
- Double-click on the OLEDB source a menu will pop up. Enter the Database server name in the connection manager then select the table which contains the data.
- Next go the columns and check whether the displayed columns matched the tables in the selected SQL table. Then click OK.
- Drag and drop the Derived column from the toolbox menu.
- Double click on derived column. Add a new column and Concatenating first_name and last_name into one column
Expression:-[first_name]+” “+[last_name]
- Click on OK.
- Drag and drop the OLEDB Destination from Other Destinations in SSIS Toolbox and drag and drop on the workspace. Map the Derived Column to destination by connecting.
- Double-click on the OLEDB destination a menu will pop up. Create at run time.
- Click on mappings and check the all mappings are mapped or not. Click on OK.
We can now run the package with the run option present in the main menu. When we press the Start button then the package will start running, and you can view the status of your SSIS package.
- Verify the output in the SQL Server. Go to SQL Server
