Introduction
I was thinking of doing some program using a combination of Copy column, Derived column, Character Map and Conditional Split for some time. So I thought of giving it a try by writing a Palindrome program where I can use all of these components.
Background
Transformation is an integral part of most of the SSIS life cycle. Once the raw data comes to our hand, it is the responsibility of the transformation components to make the needed morphisms and bring the data in the needed format. In this article, we will look into four of the SSIS transformations viz Copy Column, Derived Column, Character Map and Conditional Split and see how they work hand in hand for solving the palindrome problem. Kindly note that it does not have any real life adherence but the example is intended to give a feel of the working of these components to those who are new to SSIS.
Given Input
The source file data looks as under:
Words (Column Header)
Madam
Mam
Phone
Number
Expected Output
Step To Be Carried Out
Step 1
Open Bids. Choose Integration Services Project from the available project type. Drag and drop a Dataflow Task in the control flow designer.
Step 2
Drag a flat file source in the Data flow. In the connection manager, specify the data source and check the Column names in the first data row check box.
In the Columns tab, Change the Output Column Names to Original Words:
Click OK.
Step 3: Configure the Copy column
Let us drag and drop a copy column to the data flow designer and double click on that in order to bring up the Copy column Transformation Editor.
The purpose of the Copy Column component is to copy an input column to a new column.
Click on the Available Input columns checkbox. Make a Copy column of the original input column which will be used for reversing in the subsequent transformation.
Once the configuration is over, let us click on the OK button.
Step 4: Configure the Derived Column
Next let us drag and drop a Derived Column component into the designer and add precedence constraint from the Copy Column component to the Derived Column component. The purpose of this component is to apply expression to the input columns.
Let us double click on the Derived Column component which will bring up the Derived Column Transformation Editor.
Next let us make the below configuration changes:
Let us choose Replace "Reverse Word" from the Derived Column (numbered as 2 in the figure) and for the first column named as Derived Column Name, let us give the Column Name as ReverseWord (numbered as 1 in the figure). From the available functions, lets us pick up the Reverse function that comes in the string function category and drag and drop the same in the Expression Column. The expression will be REVERSE (ReverseWord) (numbered as 3 in the figure). And lastly, let us make sure that the Data Type column is set to string [DT_STR]. Once done, let us click the OK button.
Step 5: Configure the Character Map
Since after reversing the original data casing may change which will give incorrect result at a later stage while comparison, it is better to compare the Original and the Reverse Words in the same case. The Character Map component is a perfect choice for this situation. Let's add a Character Map component to the designer and add the data flow from the Derived Column Component to the Character Map component. Double click on the Character Map component to bring up the Character Map transformation Editor and let us do the below setting as depicted in the figure.
Once the setting is over, let's click the OK button.
Step 6: Configure the Conditional Split
The next step is to add a conditional split component. Its purpose is to split the incoming data and route it to different outputs based on the condition specified. So, after adding the Conditional Split Component in the data flow designer and performing a dataflow from the Character Map component to the Conditional split component, double click on the later to bring up the Conditional Split Transformation Editor and then make the following changes:
Once the settings are made, let's click the OK button.
Step 7
Add two row samplings (one for success and the other for failure) and Add data viewer to the data flow paths. The final package design looks as under:
Run the application and we will get the needed output.
Conclusion
Hope this small experiment has helped us in understanding how to work with Copy Column, Derived Column, Character Map and Conditional Split Transformation components. We have seen how to configure those components, how to use all those together to work.
Thanks for reading.
History
- 27th April, 2011: Initial post
