Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

A Palindrome Program in SSIS using Copy Column, Derived Column, Character Map and Conditional Split Transformation

Rate me:
Please Sign up or sign in to vote.
5.00/5 (9 votes)
27 Apr 2011CPOL4 min read 40.9K   378   8   1
This article will discuss about Copy Column, Derived Column, Character Map and Conditional Split Transformation in SSIS with a palindrome example.

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

1.jpg

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.

2.jpg

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.

3.jpg

In the Columns tab, Change the Output Column Names to Original Words:

4.jpg

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.

5.jpg

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.

6.jpg

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.

7.jpg

Next let us make the below configuration changes:

8.jpg

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.

9.jpg

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:

10.jpg

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:

11.jpg

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralMy vote of 5 Pin
Member 1507871611-Jul-22 10:52
Member 1507871611-Jul-22 10:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.