Copy Column Transformation in SSIS allow us to create a duplicate columns means it takes an input column and creates a duplicate of it with a new name.
This transformation is very useful when want to perform multiple operations on single column then we can simply create multiple copies and later we can perform one operation on one copy.
As you can see, here we have a source text file named ZonalSales, and file have columns Zone, Executive Name, Quantity and Amount.
Now below is our destination file named ZonalSales_log, this is also a flat file.
You can see, this file does not have data and there are two additional column Zone_1 and Amount_1.
So, the data will be loaded from source file for columns Zone, Executive Name, Amount and Quantity and columns Zone_1 and Amount_1 will have data that will be copied or you can say duplicate data from columns Zone and Amount using Copy Column Transformation, that we will see in this article.
Now open SSDT (SQL Server Data Tool), and take New SSIS project, or you can also create a new SSIS package in existing project.
Lets give a meaningful name to package. Right Click on Package.dtsx then select Rename from context menu.
Note: do not change or remove the .dtsx extension from package name, just change the package name only.
Lets got to Toolbox pane, the select and drag a Data flow task into Control Flow view or just double click on Data flow task, as shown below.
Now, double click on the data flow task which opens the data flow tab.
As our source is text file, lets got to Other Sources and drag a Flat file source into Data flow view.
Now we will assign a connection manager to the flat file source.
Just double click on flat file source or right click then select edit from context menu as shown below.
You will see a Flat File Source Editor window opens, now you can select an existing connection manager or can create a new by clicking on New button.
Lets click on New.. button,
Once you click on New.. button, a Flat file Connection Manager Editor window opens.
Here you can specify the source file properties, file format and so on..
In General tab, once you specified the flat file name, you can also select the row delimiter, skip any row and tick the checkbox, if flat file has column names in first row.
Now in Columns Tab, you can verify and modify row and column delimiter, also can see the data preview.
Next, in Advanced tab, you can check column data type, and size also can add new column, modify the details of an existing column, and delete them if required.
After that in a Preview tab, you can see the preview of source data.
If everything looks fine, just click on OK button.
Once you click on OK button, you get back to Flat file Source Editor window.
Now go to Columns Tab, you can select required output columns also can change the output column names.
After that in Error Output Tab, you can specified the behavior of the component in case of failure, you can specified any one option as follows:
- Ignore Failure: It ignores any failure while reading rows from source and the package will continue executing even any error occurred.
- Redirect Row: It redirects the failed rows to other component which is connected with the error precedence constraints.
- Fail component: It stops the execution of package in case of any failure.
We go with default selected value that is Fail component.
Now select Copy Column transformation and drag into Data Flow View as shown below.
Now we will add the path means connect the Flat File Source component to Copy Column component.
A path connects two components in a data flow by connecting the output of one data flow component to the input of another component. It has a source and a destination
For this select Flat File Source component then drag the arrow and connect it to Copy Column component as shown below.
Once you connect Flat file Source component to Copy Column component, it will look like as shown below.
Now double click on Copy Column transformation and select the columns you want to copy or you can say duplicate.
After that click on OK button.
Now we will take a Flat file destination, as our destination file is text file.
For this just select and drag the Flat file destination component to data flow view as shown below.
After that, drag Copy Column arrow to the flat file destination as shown below.
Now we will assign a connection manager for flat file destination.
Just click on Flat File Destination component. You will see a Flat File Destination Editor window opens.
Here you can select an existing connection or configure new Database connections.
To create a new connection click on New.. button.
A Flat File Format window opens, select the format of the destination file.
Here we select Delimited then click on OK button.
By click on Ok button, another window Flat file Connection Manager Editor opens.
Here you can configure connection manager.
Next, in Columns Tab, you can verify and modify row and column delimiter, also can see the data preview.
Next, in Advanced tab, you can check column data type, size also can add new column, modify the details of an existing column, and delete them if required.
After that in a Preview tab, you can see the preview of source data. As our destination file does not have any data so there will not be any data for preview.
Next, click on OK button. You will get back to the Flat file Source Editor window.
Now in Mappings tab, you can map the appropriate input columns with output columns.
You can see by default source file columns that is input columns and destination table column that is output columns are mapped automatically.
If any columns are not mapped correctly or missed then you can rectify or mapped them manually.
Lets map the source column Copy of Zone and Copy of Amount to destination column Zone_1 and Amount_1. Just select the column and drag and connect them with destination column.
After that, click on Ok button. Once you done with this, your package will look like as shown below.
Now you are ready to execute the package, Just save and build the package.
After that right click on Package and select Execute Package from context menu as shown below.
You can see, package runs and 4 rows has been transferred successfully into destination file.
Lets check the destination file.
You can see the data has been loaded into destination file also you can see the data for additional columns Zone_1 and Amount_1 column has been copied from zone and amount and values are exactly same.
Also Read..
