Custom Data Flow Components in SSIS for dealing with Delimited Strings

RMehta — Sun, 20 Apr 2008 14:50:00 GMT

I have never been a .NET developer. Maybe that is why, despite good guidance from sqlis.com 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 sqlis.com 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?

Rushabh Mehta : Data Flow Transforms, SSIS

Custom Data Flow Components in SSIS for dealing with Delimited Strings