Tag Archives: BI xPress

Adding/Copying SSIS XML Package Configurations

SSIS Package Configurations allow us to modify SSIS package properties from outside of the packages. This gives us a seamless way to dynamically point our packages to our development, QA, and production environments without needing to manually open and modify our connection managers within each SSIS package. Package Configurations can be created using a SQL Server table, environment variable, XML files, registry entry, or parent SSIS package, but this post will focus on creating an package configuration using an XML file.

To create the Package Configuration, open an SSIS package in SSDT or BIDS and right-click in the blank area of the control flow and select Package Configurations.


Check the checkbox to enable package configurations and then click Add to add a package configuration to the package.


In the drop down list next to Configuration type select XML configuration file. Browse to the location where you wish to store the XML configuration file and type in the name which you’d like to give your configuration file and hit enter.


And now time for a quick note on best practices and my thoughts on using XML configuration files. SSIS package configurations are most often used to configure Connection Managers from outside the SSIS packages. With this in mind, I prefer to create one package configuration per Connection Manager. I also prefer to name the configuration file the same name as the Connection Manager. In my experience, the 1:1 ratio of XML configuration files to Connection Managers eases maintenance and management of the XML configuration files. If you’re creating XML configuration files to configure other properties of the package such as package variables or task properties, you may find another solution works better for you. That’s just my .02 cents on the matter.

Now we need to specify the property which we wish to configure. In this example, I’m selecting the ConnectionString property of a Connection Manager. Check the checkbox next to the ConnectionString property under the Connection Manager. Click Next. On the final screen you’ll need to give the Package Configuration a name. I give the same name as the XML Configuration File to help things stay nice and neat.


We can see our package configuration has been created. Click close.


Yay! Now your package has an XML configuration file set up to dynamically configure your connection string of the connection manager you selected at run time. All that is left to do is to copy the XML configuration file to each environment the package will be run on: developer machines, development, QA, and production environments. Then you must also update the XML configuration file with the environment’s connection string. Just open the XML file in note pad and update the Connection String to point to the appropriate server and database.


So we’ve set up the package configuration on a package. But what if we need to copy the package configuration to many other packages? Sure, we could complete the previous steps manually on each package, but that would take a lot of time. We can, however, easily accomplish this in a few clicks with BI xPress. In order to complete the following steps you will need to have BI xPress installed. You can download and install a free trial version of BI xPress here.


Shift select all of the packages within your SSIS project including the package that has the package configuration applied as well as the packages you wish to modify. Then right-click the packages in the Solution Explorer and select Package Configurations (BI xPress) to open the BI xPress Configuration Wizard.


Click Next pass the first screen of the Configuration Wizard.


You should see all of the packages highlighted and in the Select Items window. Click Next.


In the following screen you should see your packages listed and the lone package configuration displayed beneath the SSIS package we added the configuration to.


Right-click the package configuration and select copy or simply highlight the package configuration and hit Ctrl+c to copy the package configuration.


Then select each package in the list individually and hit Ctrl+p to add the package configuration to each package.


Click the disk icon to save your changes and we’re done! Our package configuration has been added to each package with much less work than we initially went though.


Just be aware that if package configurations have not been enabled for the packages you added the package configuration to you will still need to complete that step for each package.

For more information on BI xPress just head over to PragmaticWorks.com or head here to download the free trial version of BI xPress. Enjoy!

Unit Testing Your SSIS Packages

One of the challenges SSIS developers often face is being able to meaningfully test our SSIS packages or components within the packages. Pragmatic Works has released a new component to BI xPress that allows you to easily create a suite of tests to perform on an SSIS package.

Unit Tests can be performed on entire packages or individual components in a package. And those Unit Tests are grouped into Test Suites, so think of a Test Suite as a series of unit tests designed to test the functionality  of a set of components in a package or a series of packages.

Set up steps can be configured to make retesting very easy. If a file needs to be created or a T-SQL command executed before a test can be performed, those steps can be configured within the BI xPress unit testing interface. And these set up steps can be performed once for the Test Suite or once for each Unit Test within the Test Suite.

Once the packages have been added to the Test Suite, creating the Unit Test is as simple as selecting the package or task within a package to execute. Then specify the expected results.

Then its time to create the Unit Test Assertions, which are the steps to check to execution of the packages or package components. An Assertion could be anything from checking a table for a certain number of rows, checking for the existence of a file, or checking a property within the package, among many others.

The entire Test Suite can be executed or an individual Unit Test can be fire. The test results are displayed in an easy to read format.

Take a look at SSIS Unit Tests with BI xPress if you’re looking for a way to streamline testing of your SSIS packages. I’ve personally used this tool during client engagements and its a great way to standardize testing of your SSIS packages.

Import Native SSIS Performance Data With BI xPress

imageOne of the most powerful features of BI xPress is the Auditing Framework, which allows you to apply a standardized and robust auditing framework to multiple packages in just a few clicks. I’ve blogged about the BI xPress Auditing Framework before because its an extremely impressive tool that has saved me and the teams I’ve worked with countless hours. But now that SQL Server 2012 has been released, we have a whole slew of execution and performance data available to us natively within the 2012 SSIS Catalog. Wouldn’t it be great if we could view the native execution and performance data within the BI xPress Monitoring Console even if the BI xPress Auditing Framework has not been applied?

Your prayers have been answered. If you’re running SSIS 2012, you can now easily import the native execution and performance data into the BI xPress database for even more in depth reporting on the execution of your SSIS packages. Of course, this feature only works if you’re running 2012 :).

To import the SSIS Catalog data into the BI xPress database, click the SSIS Catalog Import button located on the Pragmatic Workbench home screen.


Next specify the location of the BI xPress database that is your target for the import. Then add the SSIS Catalog as the source for the import.


imageAnd the really nice part is that you can turn on Auto Importing so that the native performance and execution data will be automatically imported into the BI xPress database!

Now that the data has been imported into the BI xPress database, any native SSIS 2012 package stored in the SSIS Catalog can now monitored and measured in the BI xPress Monitoring Console. Head over to PragmaticWorks.com for more information and a trial download.

SSRS Performance Monitoring With BI xPress 3.5

Earlier this month the latest version of BI xPress was released. There are several new features in this latest release of BI xPress, but one of the coolest is the SSRS Performance Monitoring.

The BI xPress SSRS Performance Monitor has four performance monitoring panels.

  • Average Run Time

    The Average Run Time panel displays the average run times of your report. This is a great place to figure out which reports are taking the longest to execute. This is the place to start if you’re trying to figure out why a report is taking a long time to run. There are three different very helpful metrics displayed in the panel:

    • Avg. Data Retrieval Time: This number shows how long it took to retrieve the data from the source system. If the Avg. Data Retrieval time is the majority of the report run time, take a look at the source query and find ways to optimize it.
    • Avg. Processing Time: This is the time the SSRS server takes to calculate and process the retrieved source data. Any formulas or expressions in the report are calculated during this time.
    • Avg. Rendering Time: This is the amount of time it takes to paint the graphics, charts, text, etc.
  • Longest Running Reports on Average

    This panel provides you a chart that can be read quickly and easily to allow you to see if a report’s execution is taking too long, which could negatively effect the performance of the entire SSRS server.

  • Frequent Reports

    The Frequent Reports panel displays the reports that are most frequently run. If your reports are being executed more than expected, this could lead to scalability issues. Tracking the number of executions is a good way for you to begin to determine if its time to scale up or out.

  • Active Users

    The Active Users panel displays report executions by users.

This newest feature is one I’ve been waiting for a long time since I first saw it during the development phase. To read more about BI xPress and the numerous other features included, head over to PragmaticWorks.com and check it out.

BI xPress Version 3.2.1 Released

Earlier this month on the 14th, version 3.2.1 of BI xPress was released. With this release comes a few nice improvements that increase the ease of use.

One of the major improvements with this release (and to be honest its about time) is the ability to view packages that have been stopped by some external source (i.e. the user, the operating system, etc.). The stopped packages will now appear highlighted in blue in the Package Execution viewer. Previously it was impossible to tell which packages had been stopped by the end user, but with this improvement it’s not possible to tell which package has been manually stopped by the end user.

Packages that have not started execution are white, packages currently executing appear in yellow, packages that have successfully executed appear in green, packages that have failed are red, and packages that have been stopped are now blue.

One of the other nice improvements is the ability to detect invalid file paths for configuration files. If the file path for a configuration file cannot be found, the configuration will appear underlined and highlighted in red in the Configuration Manager.

There’s a host of other improvements and bug fixes in this release. To read the full release notes, go here. To download the trial version of BI xPress, head over to PragmaticWorks.com.

Find SSIS Variable Dependencies with BI xPress

One of the difficult aspects of SSIS package development is keeping track of where a variable has been used in an expression or whether a variable is being used at all. If you’re developing your packages with BI xPress, you can easily discover where your variables are being used if they’re being used at all.

Open your package in BIDS, and go to the Variables windows. Highlight the variables you want to check for dependencies and then click the Scan Variable Dependency icon.

1 Scan for variable dependencies icon

This will open the Variable Scanning Options. You can specify to scan all variables, which could take a considerable amount of time. In my case, I’m going to choose to scan for only the variables I have selected.

2 scan for selected variables

When the Variable Usage windows opens up, click the Scan all objects for variable usage button at the bottom. This is going to actually search our package to discover if and where the variables could be used.

3 View variable dependencies

Once the scan is complete, we can see on the right side of the window the tasks where our variables are used. In my case, my selected variable is used in an Execute SQL Task and a Script Task. If I highlight the Script Task, I can even view the script and see where the variable is used.

Any variables that are not being used in the package will show up under the Unused Variables node. At the bottom of the window you’ll notice that we have the option to either rename or delete these unused variables.

Head to PragmaticWorks.com to checkout BI xPress and download the free trial version.

Creating Named Sets In Your Cube

Named sets are simply MDX expressions defined with an alias that return a set of members. If you find yourself writing complex MDX expressions to return a set of members often or if you have a commonly used expression, consider creating a named set. This will prevent you from having to duplicate your work.

To create a named set, open your SSAS project and head to the Calculations tabs. Find the icon with the curly brackets and click it.

1 Click icon

Give your named set a name. For this example, I’m using the Adventure Works 2008 R2 cube and I’m creating a named set to return the top ten products with the highest Internet Sales Amount.

The MDX to return the top ten products with the highest Internet Sales Amount is very simple. We will use the TopCount function, which accepts three arguments.

The first argument is the dimension attribute we wish to return. The second argument is how many members will be returned in the set, and the last argument is the measure we would like to use to rank the members returned.

If I wanted to create a named set to return the Bottom 10 Products with the lowest Internet Sales Amount, I could write the same expression seen below except in place of the TopCount function I would use the BottomCount function.

1a Create Top 10 Count

When we create our named set, we can specify it as a Dynamic Named Set or a Static Named Set. A Dynamic Named Set respects the context of the subcube and the where clause and is evaluated at the time the query is executed. A Static Named Set is evaluated at the time the cube is processed and will not respect any subcube context and slicers in your where clause.

Here is the calculation script for the named set:

    [Measures].[Internet Sales Amount]

The next time we process our cube, our named set will be available for use in our calculations and reports.

To use our named set in an MDX query, all I have to do is use the Named Set’s alias, [Top 10 Products].

Named Sets are powerful and very useful, but they can sometimes take a long time to write, especially if the expression is complex or if you are new to MDX. That’s where BI xpress steps in. BI xPress is a powerful tool used to speed up and augment SSAS and SSIS development. BI xPress also recently won the Gold Editors Choice Award from SQL Server Magazine.

To open the BI xPress Calculation Builder, open your SSAS project in BIDS, navigate to the Calculations tab and click the BI xPress Calculation Builder icon.

2 Click BI xPress icon

This will open the MDX Calculation Builder Wizard. Select the calculation or named set you would like to build. Scroll to the bottom to find the Sets templates. I’m selecting the Top 10 Count template.

3 Select Top 10 Count BI xPress

The first step is to select the attribute that you would like returned by the set. As before, I’m selecting the Product attribute of the Product dimension.

4 Select attribute

Then select the measure that you want to use to rank the selected attribute. I’m selecting the Internet Sales Amount measure.

4 Select measure

After I click next and give my calculation a name, the named set has been added to my calculation script in my cube.

To download the free trial of BI xPress, head over to PragmaticWorks.com and check out BI xPress. You won’t regret it after you see how much time it will save you with your SSAS and SSIS development.