Copy Column Transformation in SSIS

Copy Column Transformation in SSIS duplicates the source columns. Just like copying the column data and pasting the data into a new column. It is a significant transformation because if we want to perform multiple operations on a single column, then we can create multiple copies. And later, we can perform one action on one copy.

For example, we have column name product price, and we want to calculate dealer price, distributor price, price including VAT, price plus wastage cost. Now we create duplicate columns for the above requirements using SSIS Copy Column Transformation. And then, we will calculate dealer, distributor, VAT, etc., prices individually.

Copy Column Transformation in SSIS Example

STEP 1: Drag and drop the data flow task from the SSIS toolbox to the control flow region.

Copy Column Transformation in SSIS 1

Double click on the data flow task opens the data flow tab.

STEP 2: Drag and drop OLE DB Source, Copy Column transformation from the toolbox to the data flow region. Double click on OLEDB source in the data flow region opens the connection manager settings. It also gives an option to choose the table containing the source data.

Copy Column Transformation in SSIS 2

SQL Command Text we used in this SSIS Copy Column transformation example is:

SELECT [EnglishProductName]
      ,[StandardCost]
      ,[Color]
      ,[ListPrice]
      ,[DaysToManufacture]

FROM [AdventureWorksDW2014].[dbo].[DimProduct]

WHERE [StandardCost] IS NOT NULL

STEP 3: Click on the columns tab to verify the columns. In this tab, we can uncheck the unwanted columns also.

Copy Column Transformation in SSIS 3

Configure SSIS Copy Column Transformation

STEP 4: Click OK and drag and drop the arrow from OLE DB Source to Copy Column Transformation. Double click on the SSIS Copy Column Transformation and select the columns you want to duplicate.

Copy Column Transformation in SSIS 4

We were duplicating the [Standard Cost] and color columns and named as Product price and Product Color. By default, The duplicate column is called Copy of Column Name in Output Alias, but you can edit this name as per your requirement.

Click OK. Next, drag and drop the OLE DB Destination into the data flow region.

STEP 5: Now, we have to provide an OLE DB connection manager and table details of the destination. So double-click on the OLE DB Destination and provide the required information

Copy Column Transformation in SSIS 5

We are storing the output in [Copy Column Transformation in ssis] present in the Adventure Works DW 2014 database.

STEP 6: Click on the SSIS Copy Column Transformation Mappings tab to see the source columns mapped to the destination columns.

Copy Column Transformation in SSIS 6

Click OK to finish the package design of the copy column transformation. Let us run the package

Copy Column Transformation in SSIS 7

Let us open the SQL Management Studio to Check the results

SELECT [EnglishProductName]
      ,[StandardCost]
      ,[Color]
      ,[ListPrice]
      ,[DaysToManufacture]
      ,[Product Price]
      ,[Product Color]

 FROM [Copy Column Transformation]
Copy Column Transformation in SSIS 8

You can observe the extra columns [Product Price] and [Product Color] with the same data as [standard cost] and [color]