Benjamin Wright-Jones

Exploring data and distributed systems [I also cross-post to]

October, 2010

  • Benjamin Wright-Jones

    Using SQL Server 2008 R2 Best Practice Analyzer against Analysis Services


    SQL Server 2008 R2 BPA is an excellent and often under-used, tool.   The title is a little misleading but it does work against SQL Server 2008 instances, not just R2 instances.

    I recently had the chance to use BPA against SQL Server 2008 Analysis Services.  SQL Browser is disabled by default.

    I entered the server\instance with the port number and ran the BPA tool only to be told that the incorrect SQL Server version was in use.  Very strange. 


    I checked PowerShell 2.0 was installed.   I also had administrative permissions on both the OS and SSAS instance.

    It turns out that the SQL Server Browser service must be running in order to discover the instance.  This is now filed as a bug which should hopefully be fixed in the next release. 

    The output of BPA is really useful and the rules point to knowledge base articles


  • Benjamin Wright-Jones

    Debugging Parameters in the SSIS Data Flow (Script Component)


    I had unfortunately picked up the (bad) habit of debugging my SSIS packages by using at design time.  The SSIS data flow Script component does not allow debugging using this approach so I had to find an alternative.  I discovered that it is possible to output parameters at design time using the FireInformation in the Script component.  This puts the parameters in the progress window.

    // Output parameters into the SSIS progress window
    bool FireAgain = true;
    this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of time is " + Row.ParamValueTime.ToString(), "", 0, ref FireAgain);
    this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of environment is " + Row.ParamValueEnvironment.ToString(), "", 0, ref FireAgain);

    I like this approach although I should probably output the parameters to one of the other console windows.

Page 1 of 1 (2 items)