As part of the project that I am currently architecting, we have a need to log data as it moves through various components and record the number of rows which have been processed. In the days of SOX requirements like this are becoming increasingly more common.
It soon became apparent that the out of the box components like row count could not push this count to a location which could be logged sensibly.
Therefore as my first dive into writing a custom component for SSIS I though this would be an interesting little project.
The code that follows is written in C# and I skip over the stuff that is fairly well documented, just concentrating on the stuff that took me a little while to work out.
Essentially we boiled down our requirements down into a set of “things that our component needs to do”.
1) Count the number of rows which pass through it.
2) Serve as both a transformation component and a destination component
3) Be able to send the row count to a variable.
4) To raise an event to let the package know that the variable has been updated.
5) To also write an event to the package log if required.
Whilst all this sounds fairly simple, in actual fact the documentation for SSIS programmability is either plain wrong in certain areas or not present, so after a lot of trial and error I have managed to cook something up which does all of the above.
Number one was the easiest, and was as simple as counting the number of rows passing through the buffer.Click here for Screenshot
I am quite happy to share this component if you think you would find it useful, just drop me an email at email@example.com
Luckily the second requirement fell straight out of the code, so didn’t have to worry about this at all.
Setting the row count to a variable proved to be one of those things that once you know how is, fairly easy, it’s just a question of wading through the object model to find what you need.
First thing you need to do is to override the ProvideComponentProperties() method.
I do all the usual stuff here, i.e. clone the inputs to the outputs; however in here you also need to create your custom property.Click here for Screenshot
Now when you look at the component properties window you will have somewhere to set the variable.Click here for Screenshot
The theory is that you can map that variable name to any valid User space variable which you need to use. So in our code we need somehow to take this and map this with the output for the count.Click here for Screenshot
I apologise for the scrappy code here, and I plan to fix it (extra parsing etc), however you see what’s happening here I hope. Basically i access the custom collection, get the property by index and then assign this to a string to build up the variable name I want to access. I then lock this, so nothing else can sneak in there, update it and then unlock it.
So now all that remains is that we tell the package that something has changed and it might want to do something with the changed variable.
First thing we need to do is to override the RegisterEvents method and add our own event to the component.Click here for Screenshot
This will cause the event to appear in the drop down list of the data flow component.
Now we want to send a notification to this event when the variable has changed.Click here for Screenshot
This will cause the event in the data flow to be fired, so in there we can put a script component and reference the variable and write away any audit information we want to.
So in the end we have a flow that looks like this for testingClick here for Screenshot
When the event is fired for each component, rather then auditing I have placed a script component on the event with a message box.
First row count outputClick here for Screenshot
Second row count output is belowClick here for Screenshot