Pages

Thursday, March 26, 2009

Data Flow Sequence Container

I was engaged in some conversation with Joe Salvatore about my Kimball SCD transform the other day, and one of his prime concerns for the component, as well as every part of his SSIS packages, was performance statistics.
I mentioned to him that he may want to check out DTLoggedExec, as well as some performance profiling features in BIDSHelper (both listed in the excellent SSIS Community Tasks and Components project on CodePlex).
However, this got me to thinking about how it might be possible to construct a "performance profiler" component in SSIS.  On the surface, it sounds like it should be easy, no?  But upon reflection, about the only workable way I could think to go about it (in the current architecture) was to create a component that would "pair" with another instance of itself.  To be clear, you'd place one instance of this component "in front of" the component you wanted to profile, and another instance "behind it" in the data flow, and then inform each of those profiling components about the other one, so they could realize they were measuring the input and output of the same component.  This quickly gets messy as soon as you realize that a lot of the components worth measuring have more than one input and/or more than one output...
So I got to thinking some more - and the more you get to know me the more you will begin to realize that's a dangerous thing.  I thought "wouldn't it be a great idea to have containers in the Data Flow?"  This occurred to me because one of the gripes I have with the Data Flow is how unruly it can quickly become with tens, if not hundreds of components interconnected.  A "sequence container" for the Data Flow would help tidy that up, and add some documenting capability at the same time.  Oh, and that's over and above giving guys like me a vehicle for extending SSIS to get a performance monitoring component!
If you think this is a decent idea, hop over to the Connect Feedback Item and cast your vote, or make a comment!

2 comments:

  1. I think that a performance component is an awesome idea! I want to vote on it, but the link address is incorrect.

    ReplyDelete
  2. Man, was that ever a bad link. Fixed it - so go vote! IMO, the idea has at least the capability of cleaning up the data flow, and making a performance component - and I haven't even stretched my brain to think of anything else yet...

    ReplyDelete