One week ago, I was asked by my current company to join a BI project. The whole project involved an ETL process, starting from an Extraction-and-Import stage up to the report creation. In the very first part of that project I needed to trim all columns of about 20 tables. Some of them actually had about 80 columns. You can imagine how frustrating it could be to do this job using only the “derived column” DataFlow component. I was supposed to add many columns and for each one to type the “TRIM” function name, specify the target columns and set “Replace column” by choosing the correct one. This process is long, boring and easy to fail because of typo, for instance). So, at first, I used a Script Task, which used reflection to navigate every string-property of the row passed as input and trim all the textual columns. The component was working very well, but I needed to edit the script task every time after copying-pasting it, because of SSIS validation mechanism. Another bigger problem was that for each Script Component, I needed to set every input column in “ReadWrite” mode. If I missed that, the component wouldn’t fire any error or warning, it would just do nothing. Basically, this is a potential point of failure for a SSIS designer.
I ended up by spending some of my (very rare) spare time to develop a custom DataFlow component which is able to trim all text values, leaving the other ones as they are.
In this article I’ll show how to develop a very basic custom SSIS DataFlow component and, more important, how to integrate it into Visual Studio. More precisely I’ll show how to design a PipelineComponent in a VisualStudio 2010 – MS SQL BISD environment.
Once I created the component I've spent much time to understand how to deploy the component. For this reason I will introduce you how to correctly set up your visual studio to develop the .dll and how to integrate the DLL inside a SSIS designer environment.

Please navigate to find out more about this project:

Component Developing

Component Deploying

Component Performances

Use case

Have a look at the use case of the MassTrimmer component!

Last edited Apr 30, 2014 at 7:22 PM by webking, version 38