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.
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.
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 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
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]