THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rushabh Mehta

MS BI geek talk and the community

Custom Data Flow Components in SSIS for dealing with Delimited Strings

I have never been a .NET developer. Maybe that is why, despite good guidance from and others, I never ventured into the world of custom components. Scripting, on the other hand, I could handle thanks to Donald's book which has always provided a great reference. Well, a couple of weeks back, I finally came across a classic scenario of reuse where I could no longer avoid writing custom data flow components. So I Google’d (Live just does not make a good verb Microsoft!) away for help. Fortunately, I did not have to go too far for help. Three excellent sources of information included this article on by Allan Mitchell, the samples that you can install with SQL Server (usually found at C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples) and finally CodePlex. On CodePlex, I found the Normaliser Component written by Jamie Thomson. There are also a few good books written by a lot of my friends in the SSIS community - but I must confess that I don't always read technical books except on rare occasion. :)

The few references I cited above go a long way towards getting you close to your goals. Beyond that, you still need some .NET skill to better understand the code in relation to what you need to do for your unique situation. That is where Jessica came in. Fortunately for me, she was working on this project with me and is far more proficient in .NET that I ever will be! So, thanks to her help we got the components done. What amazed me was that with the references I mentioned above, it took less than 3 days to understand and write 2 different custom components (one synchronous and one asynchronous) and achieve high degree of reusability.

To give you a quick background on the custom components, both these custom components deal with de-limited strings in a column. The first component, which is a synchronous component, sorts and de-duplicates the data inside each row. So, if you have the following input...

1              Kirk,Donald,Anjan,Ritu,Catherine,Anjan

2              Jamie,Allan,Simon,Erik,Andy

You would end up with the following output

1              Anjan,Catherine,Donald,Kirk,Ritu

2              Andy,Allan,Erik,Jamie,Simon

The second component un-pivots a delimited string. So, in the example

1              Anjan,Catherine,Donald,Kirk,Ritu

2              Andy,Allan,Erik,Jamie,Simon

You would see the following output

1              Anjan,Catherine,Donald,Kirk,Ritu    1              Anjan

1              Anjan,Catherine,Donald,Kirk,Ritu    2              Catherine

1              Anjan,Catherine,Donald,Kirk,Ritu    3              Donald

1              Anjan,Catherine,Donald,Kirk,Ritu    4              Kirk

1              Anjan,Catherine,Donald,Kirk,Ritu    5              Ritu

2              Andy,Allan,Erik,Jamie,Simon           1              Andy

2              Andy,Allan,Erik,Jamie,Simon           2              Allan

2              Andy,Allan,Erik,Jamie,Simon           3              Erik

2              Andy,Allan,Erik,Jamie,Simon           4              Jamie

2              Andy,Allan,Erik,Jamie,Simon           5              Simon

The un-pivot component preserves the order of the data in the string and does not do any sorting or removing of duplicate values. It also adds a numeric column to provide the position id of each value. Both of these components also accept the delimiter string as a custom variable to the component.

So, I am interested in hearing some feedback as to whether these components would be something you see use for?

Published Sunday, April 20, 2008 11:50 AM by RMehta



andyleonard said:

Yes! This is good work Rushabh and Jessica!

I can think of a couple uses for this when combined with the Term Extraction transformation and applied against unstructured text.

Great work!

:{> Andy

April 20, 2008 2:25 PM

jamiet said:

Looks good to me Rushabh. The unpivot one should prove particularly ueful I reckon!


April 20, 2008 3:50 PM

Rushabh Mehta said:

If you recall my earlier post on Custom components , I just wanted to provide you an update that the

May 26, 2008 5:26 AM
New Comments to this post are disabled

About RMehta

Rushabh is an Independent Consultant and a Business Intelligence Mentor for Solid Quality Mentors. He has been the lead architect for many large scale and complex Business Intelligence solutions for large enterprise clients such as Publix, Raymond James Financials, Jackson Hewitt, Checkers and most recently on a multi-terabyte Health Services solution for the US Department of Defense. Additionally, he has been a mentor for a large number of client such as Veterans Health Administration, Intuit,, Cymer, Navy Medical Center, Talbots, FM Global and Sunoco. Rushabh is also a BI trainer and has taught BI courses in countries around the world. Through his work experience, Rushabh has been instrumental in creating multiple best-practices in the implementation of the BI technologies. Rushabh also serves on the Board of The Professional Association for SQL Server (PASS) as the President. Rushabh is also a SQL Server MVP and a frequent speaker at large conferences as well as PASS Chapters around the world.
Privacy Statement