Copy Column Transformation in SSIS simply duplicates the source columns. Just like copying the column data and pasting the data into new column. This is very important transformation because if we want to perform multiple operations on single column then we can create multiple copies and later we can perform one operation on one copy.
For example, we have column name product price and we want to calculate dealer price, distributer price, price including VAT, price plus wastage cost. Now we simply create duplicate columns for the above requirements using Copy Column Transformation and then we will calculate dealer, distributer, VAT etc prices individually.
Copy Column Transformation in SSIS Example
STEP 1: Drag and drop the data flow task from the toolbox to control flow region.
Double click on the data flow task, it will open the data flow tab.
STEP 2: Drag and drop OLE DB Source, Copy Column transformation from toolbox to data flow region. Double click on OLE DB source in the data flow region will open the connection manager settings and provides option to select the table holding the source data.
SQL Command Text we used in this example is:
SELECT [EnglishProductName] ,[StandardCost] ,[Color] ,[ListPrice] ,[DaysToManufacture] FROM [AdventureWorksDW2014].[dbo].[DimProduct] WHERE [StandardCost] IS NOT NULL
STEP 3: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.
STEP 4: Click ok and drag and drop the arrow from OLE DB Source to Copy Column Transformation. Double click on the Copy Column Transformation and select the columns you want to duplicate.
We are just duplicating the [Standard Cost] and color columns and named as Product price and Product Color. By default, The duplicate column are named as Copy of Column Name in Output Alias but you can edit this name as per your requirement.
Click ok and then drag and drop the OLE DB Destination into data flow region.
STEP 5: Now we have to provide 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 Mappings tab to check whether the source columns are exactly mapped to the destination columns.
Click ok to finish our copy column transformation in ssis package design. Let us run the package
Let us open the SQL Management Studio Query window 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 same data as [standard cost] and [color]
Thank you for Visiting Our Blog