EOL: Bi xPress reached its end of life date on June 15, 2022. See the Solarwinds End of Life Policy for more information.
Introduction
| Icon | Description |
|---|---|
 | The Best Practices Analyzer inspects components (packages, reports, settings, etc.) of SSIS, SQL Server, SSAS, and SSRS. It then displays recommendations based on best practices. |
Feature Highlights
- Checks for adherence to best practices ad hoc and in batch mode.
- Store the results of the best practices analysis within the BI xPress database for later analysis.
- Store the results of the best practices analysis within an XML file using the command line.
- Determine package performance issues and potential bottlenecks when using predefined best practices.
Note: You have to use the SentryOne Workbench to analyze SSIS packages on a machine that only contains SSDT for Visual Studio 2015.
Note: If your selected technology type doesn't display when using the BI xPress Best Practices Analyzer, your machine might not meet the system requirements for the specific provider. Open the System Requirements to make sure your system meets all of the requirements for the provider.
Analyze Best Practices
Note: For SQL Server Data Tools 2015 and above, you can analyze your packages directly from SSDT by right clicking your package or highlighting a number of packages from the solution explorer, and then selecting Analyze for Best Practices. This option opens each package within SSDT and analyzes them.
Select Best Practices in the SentryOne Workbench to open the Best Practices Dashboard. From the dashboard you can Create / Modify Best Practices, or Analyze Best Practices. Select Analyze Best Practices to open the Connection page.
Select the Technology type to Analyze
Select the Technology type you want to analyze from the available technology providers on the left side of the screen. Enter your selected technology credentials, and then select Next to open the Rules page.
Note: If the source type you want to use doesn't display, you may not meet the system requirements for that provider. For more information about system requirements, see the System Requirements article.
Select Rules
Set the Use BI xPress database to load and save Best Practices rules and analysis results toggle to On to store the results of your analysis on your BI xPress database for review at a later time or to compare changes over time.
Note: You can connect to any BI xPress database using either Windows Integrated security or SQL Server Authentication.
Select the rules that you want to run against your package from the rules list. Select Analyze to begin analyzing your package(s).
Note: If you have previously saved rules, select Load and then select the desired load option to load your rules. Select Save and then select the desired save option to save your current rule set.
Best Practices Analysis Results
The package is analyzed using the rule set you created or selected on the rules page. The results of your analysis are displayed in the results interface. Open the Best Practice Analysis Results at any time through the Monitoring Dashboards add-in or by analyzing a new package.
Note: If you did not select to have your results saved in the database, the analysis will not be available after closing BI xPress.
BI xPress Best Practices Analyzer Command Line
Use the BI xPress Best Practices Analyzer command line tool to analyze an SSIS Package against a personal rule set, or the shared rule set specified within the BI xPress Database. The BI xPress Best Practice Analyzer command line tool uses the PWBestPractices.exe located within the SentryOne Workbench directory.
Note: The SentryOne Workbench directory is set to the following location by default:
- C:\Program Files (x86)\Pragmatic Works\Pragmatic Workbench\PWBestPractices.exe
Any framework (such as Scheduled Tasks, TFS Build Server, Microsoft Systems Center) that is capable of starting an application and passing parameters using command line arguments is capable of using the Best Practice Analyzer command line tool to analyze personal or shared rule sets against SSIS Package(s).
PWBestPractices.exe {<Help> | <Items>} [<Rule Set Type>] [<Connection Information>] [<Options>]
Switch Arguments
Use -- followed immediately by the parameter switch or - followed by the short form version of the parameter switch to introduce a parameter argument to the PWBestPractices.exe. If the switch requires a value, use an = (equal) to separate the parameter argument. Use "" (double-quotes) around the parameter value if the value contains a space or special character(s). The following is a list of parameter arguements and their purpose in BI xPress command line:
| Argument Name | Description | Switch | Short Form |
|---|---|---|---|
| Help | Displays the help message that gives you further clarification on using the Best Practice Analyzer's Command Line tool. | --HELP | -H or -? |
| Item Value | The fully qualified location(s) of the package(s). This must include the full package name including the file extension. If specifying multiple targets, all items must be separated using spaces in between values, for example: PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" "C:\SSIS\ChildPackages\ETLPackage.dtsx" | "values" [...n] | "values" [...n] |
| Item Version | Specifies the SSIS Version of all packages you are attempting to analyze. You can specify the following values: | --VERSION="value" | -N="value" |
| Shared Rule Set | Specifies that you want to use the shared rule set when analyzing the targeted SSIS Package. If you are using the Shared rule set switch, connection information switches are required. | --SHARED | -S |
| Personal Rule Set | Specifies that you want to use a personal rule set stored on the file system. If you are using the Personal rule set, the Shared rule set option cannot be specified. | --PERSONAL | -P |
| Connection Information Connection String | Used along side the Shared rule set option to specify a valid SQL Server connection string that connects to the BI xPress database with the shared rule set. | --CONNECTIONSTRING="value" | -C="value" |
| Connection Information Output | Used along side the Shared rule set option to specify that you want to output the results to the BI xPress database specified within the Connection String option. Either the XMLOutput or DBOutput option must be specified. If using the DBOutput option, the XMLOutput option cannot be specified and the Connection String option is required. | --DBOUTPUT | -D |
| Options Password | The package password to apply to all SSIS packages specified. | --PASSWORD="value" | -W="value" |
| Options Verbose | The Verbose switch provides you with additional information within the specified output option. | --VERBOSE | -V |
| Options XML output | Species the file path location where the Best Practices Command Line tool outputs the results. The XMLOutput or DBOutput option must be specified. If using the XMLOutput option, the DBOutput option cannot be specified. | --XMLOUTPUT="value" | -X="value" |
Command Line Examples
This example analyzes the MasterPackage.dtsx file against the Shared rules set within the BI xPress database on localhost and outputs the results to the same database.
PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" --VERSION="2012" --SHARED --CONNECTIONSTRING="Data Source=LOCALHOST;Initial Catalog=BIxPress;Integrated Security=True;" --DBOUTPUT
This example analyzes the MasterPackage.dtsx file against the user's personal rule set and outputs the results to the MasterPackageResults.xml file with verbose output.
PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" --VERSION="2005" -PERSONAL -XMLOUTPUT="C:\SSIS\Analysis\MasterPackageResults.xml" --VERBOSE
This example applies the password PackagePassword to both MasterPackage.dtsx and ETLPackage.dts and then analyzes them against the user's personal rule set and outputs the results to the ETLResults.xml file with verbose output.
PWBestPractices.exe "C:\SSIS\MasterPackage.dtsx" "C:\SSIS\Child\ETLPackage.dtsx" -N="2014" -P -W="PackagePassword" -N="2012" -X="C:\SSIS\Analysis\ETLResults.xml"
Create / Modify Best Practices
Select Best Practices in the SentryOne Workbench to open the Best Practices Dashboard. From the dashboard you can Create / Modify Best Practices, or Analyze Best Practices. Select Create / Modify Best Practices to open the User Defined Best Practices page.
User Defined Best Practices
 |  |
Use the User Defined Best Practices page to manage existing rules, and to create new rules. Select the + button to add a new rule, and complete the following fields:
| Rule Option | Description |
|---|---|
| Rule Name | Enter a name for your rule. Note: When you save your rule for the first time, the rule name will be used as a suggestion for the file name. If the rule name has invalid characters for the file name, you will need to use a different file name. |
| Author | Enter the name of the user or team that created the rule. |
| Reference URL | Enter a link to an external or internal website that includes detailed instructions for resolving the best practice violation. |
| Date Created | The date the rule was created. Note: This field populates automatically. |
| Severity | Select rule severity from the following options:
|
| Message | Enter a message for the rule. The message that displays in the Best Practice Analyzer's rule selection page. |
| Recommendation | Enter a description for correcting the violation. The Recommendation displays as a tool tip on the Best Practice Analyzer's Rule Selection page, and as a column on the Results page. |
| Object Type Filters | Enter the desired filters. The filters that you select display after building a path evaluator. |
| Path Evaluator | Select a path evaluator. Note: Select the Path Builder button to build a path. |
Once you have completed the rule form, select the Save toolbar button to save your rule.
Note: Manage the rules you have created from the User Defined Best Practices page. Deleting a rule from this page removes it from the rules list, but does not delete it from the file system.
If you saved a rule to the Shared Rule set, the rule is saved to your BI xPress database.
Using the Path Builder
The Path Builder creates the actual definition for your best practice. The Path Builder uses the SentryOne Workbench Path language to create a true or false path. The path can be analyzed in any SSIS package and verify that the created rule applies. In the following example, we create a rule that checks to make sure the user did not enter the value "Description" for a connection's Description property:
1. Open the User-Defined Best Practices page (Select Best Practices > Create/Modify Best Practices), and then select Path Builder to open the Path Builder page.
Note: The text box at the top contains the field for your path. You have a list of operators, selectors, and other commands to choose from. To reset your selections, select Reset.
2. Select a technology type from the provider list to open that technology's configuration options. Enter the applicable information to specify your Solution item(s). Select Next to continue.
3. Select the desired Object Type Filter(s) and then select the type of rule you want to create in the Object Browser. 
Note: Making a selection in the Object Browser section populates the middle field with that object's properties and path. In this example, we have selected the package itself from the Object Type Filters and a Flat File Connection from the Object Browser. This populates the middle pane with the path and properties of the connection manager.
4. Select the property that you want to check to generate the path. Drag the path into the Path Text Box and remove the name. 
Note: In this example, we selected the Description property.
5. Select Preview to make sure that your rule validates against the current package.
Note: Your rule should validate true for it to throw a violation of the best practice. For our purposes, our rule looks for connections where the description has a value of Description.
6. Select the back arrow to review your Object Type Filter and Rule Path in the User-Defined Best Practice creator.
