Monday, July 20, 2009

SSIS Performance , SSIS Debugging , SSIS Development , SSIS Tools , SSAS Tools , SSRS Tools , MS BI Tools - BIDS Helper

I'm reading: SSIS Performance , SSIS Debugging , SSIS Development , SSIS Tools , SSAS Tools , SSRS Tools , MS BI Tools - BIDS HelperTweet this !

BIDS Helper is such a great tool that needs no introduction for MS BI Professionals , or rather I would like to used the word "Product" for it to honour its value. I feel that sooner or later, Microsoft should incorporate the functionality that it provides within BIDS or should flag it as an official Microsoft Recommended Add-In and ship it with some service packs.

I would like to present below, some of the very useful functionalities that it provides for the SSIS environment, functionalities that in particular I have found useful. I don't mean to say that rest of the features are not useful, but it means that I have not required to use the features not listed below in my development knowledge.

1) dtsConfig File Formatter: Many of us hate the way config data is written to dtsConfig file and keep wondering why there's no reader or a micro IDE kind of thing for it. It can be read in Visual Studio which would format the excel, but the file formatter in this tool, does it all for the developer.

2) Expression and Configuration Highlighter: This is a super brilliant feature, and is very necessary in large projects where you develop tens of packages every week. Especially this is helpful in setting package configurations in a master - child package kind of scenario where the child packages read their configuration setting from master packages. This feature can work as a proof-reader to have a quick visual check on whether the configuration and expression setting for controls have been implemented or not.

3) Expression List: This is one of the much required report in any package. This feature combined with the previous feature, gives a nice proof-reading (verification) and reporting of the expressions implemented in a package. Also it comes handy to have a list of expressions used in the package, as it gives a better track of points where changes needs to be made.

4) Non-Default Properties Report: This is another brilliant report that becomes almost inevitable while working in a maintenance kind of project, and is also helpful in development projects for documentation and reconciliation purpose. As relevant from the name, it provides a report of all the sensible properties which has non-default values. This helps to verify that any property has not been changed inadvertently.

5) Pipeline Component Performance Breakdown: This is one of the key helpful debugging help, needed in a complex project. Thou from the theory and definition that the product page explains, I am quite convinced that this would help, but still I have skeptic about precision of its report. After all it's just a program and a mechanism of measuring the performance, and can't be more analytical than human brain !! I wish that it was perhaps possible to combine the replay from trace feature of SSIS Log Analyzer with this feature, it would had been a silver bullet in arena of SSIS debugging.

6) Reset GUIDs: This feature is a developer's hammer, and saves quite some time when a package is copied in the same or across environments, where duplication of a package is done for templating purpose.

7) Sortable package properties report: This is another nice report that this tool draws out of the SSIS solution. It's a good report to attach to a build, but it's not comprehensive. It can get better, if it provides options to select properties that are required in the report which becomes useful for a real-time reporting in a complex project. Also I believe this would work for only standard controls that come out of the box of SSIS, but not for custom or third-party controls. To summarize this report is good to attach to any build, thou just for a formality.

8) SSIS Performance Visualization: This is a nice analytical and pictorial representation of execution trees logs available in SSIS. At the first glance, this symbolic language got me confused and thinking about what it means as and requires a thorough reading of how to interpret the meaning of the charts. But it's a swiss knife, if used with a deep understanding. It's not a developer feature, it's a feature that a performance analyst would appreciate and love to use. I like this performance visualization feature 10 times more than what SSIS log analyzer tool shows in the form of pie charts with almost no technical details of how it was derived and the stats behind it.

9) SSIS Variables Windows extensions: This is again a developer's hammer and making your vision more deep and sharper into the package composition. I am not that impressed with the feature that it provides for copying the variables across tasks and containers, compared to the feature where it highlights the icons of the variables impacted by package configuration and expressions.

All in all, I feel that it's one of the must have utility after installing BIDS for any serious SSIS development project, as sooner or later eventually every SSIS developer or performance analyst finds a use of one or more features of this tool.

No comments:

Related Posts with Thumbnails