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 a column named product price, and we want to calculate dealer price, distributor price, price including VAT, and 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.

Double click on the data flow task and open the data flow tab. For more Transformations >> Click Here.

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

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.

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.

We duplicated the [Standard Cost] and color columns and named them 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.

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.

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

Let us open the Management Studio to Check the results

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

 FROM [Copy Column Transformation]

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