Copy Column DataFlow Item in SSIS



Copy Column Transformation helps you in creating new columns by copying input columns and adding the new columns to the transformation output. In simple it creates alias columns of the input columns. You can create any number of new columns by copying the same input column. For example using a column named “First Name” you can create any number of copied columns. Once the columns are created then you can apply transformations to the copied columns using other dataflow items. For example once the new column is created using copy column transformation  then we c an convert the copied data to uppercase characters by using the Character Map transformation, or apply aggregations to the new column by using the Aggregate transformation.

PFB the steps involved in configuring Copy Column Transformation.

1) Drag and Drop a Dataflow item in Control Flow designer.

2) Right click and edit on data flow task or simply double click on the data flow item.

3) Select any Data flow source and configure it by providing source info.

4) Select Copy Column Transformation and edit it by right clicking on it.

Configuring Copy Column Transformation
Configuring Copy Column Transformation

5)Select the columns for which you wish to create a copy and give Alias names as shown in the below screenshot.

6) Click on OK and you can test by adding data viewers.

PFB the screenshot of the output of Copy Column Transformation. You can see that the transformation created a Copy of the column “Name”. This transformation has one input and one output and it doesn’t support an error output.

Configuring Copy Column Transformation
Configuring Copy Column Transformation

Note – When you have transformations like derived column, we don’t feel that this Copy column transformation is useful. Simply to say You can ignore this transformation .. 🙂

That’s it .. Happy Coding ..


Roopesh Babu V