Category Archives: SSIS

Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

image With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.

Configure the Excel Power Refresh Task in SSIS

Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.

First create a Connection Continue reading Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

My Top Four Books for the MS Business Intelligence Professional

As a Business Intelligence Consultant, I do a decent amount of speaking, interacting with the community, and have written and contributed on a few SQL Server books. A question I’m often asked is if I can recommend any good books which brings me to this blog post. I wanted to make you aware of four books for learning data warehousing and  other MS BI technologies that I’ve found incredibly helpful over the years I’ve spent designing and implementing enterprise data warehouse and business intelligence solutions. Continue reading My Top Four Books for the MS Business Intelligence Professional

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 or head here to download the free trial version of BI xPress. Enjoy!

Comparing SSIS Packages with BI Compare

Comparing SSIS packages isn’t always easy, as I’m sure you’ve probably discovered if you’ve ever tried to compare one package to another. Of course SSIS packages are simply XML files underneath the shiny GUI, but scrolling through pages of XML in order to discover the slightest differences between two versions of a package is not how I prefer to spend my afternoons. Thankfully, there’s an easier solution.

Pragmatic Works has developed a tool called BI Compare that allows you to easily compare two SSIS packages and easily view the properties of each package side by side with easy to spot indicators making it very easy to spot differences in the packages. BI Compare is a tool included with Pragmatic Works fabulous BI development and administration tool BI xPress.

With BI Compare we can set up a new comparison scenario, identifying the packages we wish to display on the left and the right.

Once we have executed the comparison between the two package, the package comparison displays a image for component and properties in the two packages that match, a image next to components that do not match, and a image next to components that are missing in the other package.

So its very easy to skim through the comparison results and view the differences between the two packages by simply looking for the X icons or <- icons. You also have the ability to filter which types of objects you wish to compare. For instance, if you don’t need to compare the connection strings simply because you’re comparing a package in development against a package in production, you can exclude the Connection String property from the comparison.

As an SSIS developer and BI Consultant, I’ve found this tool very useful during SSIS package upgrades and migrations. To get more information on BI Compare, head over to Pragmatic Works and download the free trial version of BI xPress.

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.

Are You Using SSIS Best Practices?


Earlier this month, Pragmatic Works released a new tool for their Pragmatic Works Workbench toolbox: The Best Practice Analyzer. The guys and gals over at Pragmatic Works have come up with a tool that analyzes your existing SSIS packages and compares them with a standardized set of best practice guidelines produced by the expert team of consultants and developers you’ve come to know and love over the past several years.

The Best Practice Analyzer combs through the SSIS packages you have selected and produces a report outlining all the best practice violations including violations of varying severities, such as “Warning”, “Error”, “Performance”, and “Informational”.

First, select the packages you’d like to investigate for adherence to best practices.

Next, select the best practices to be included in the report.

The end result is an easy-to-read report identifying any violations of best practices.

Violations could include everything between leaving a component description blank, using a fully block transform in a Data Flow Task, not enabling error logging, plus many more. Head over to to get more information on the SSIS Best Practice Analyzer.

Migrating Away From Informatica To SSIS? You’ll Need Workflow Profiler


If you’ve been paying attention to this blog or, you probably picked up on the fact that Pragmatic Works has put together a new service offering for those companies currently running Informatica as their ETL tool of choice and are sick and tired of paying hundreds of thousands of dollars a year in licensing (and in some cases millions of dollars! o_O!!). Pragmatic Works is now offering to quickly and efficiently migrate their clients from Informatica to SQL Server. One of the ways Pragmatic Works makes this process less painful is by using their tool, Workflow Profiler.

The primary goal of Workflow Profiler is to fully document your Informatica PowerCenter MetaData environment very quickly, thus giving you a better idea of just how much effort could be required during the migration process. This information is displayed in a series of detailed reports viewable in Workflow Profiler.

Reports, such as the Widget Distribution Summary Report, give you a detailed analysis of your Widget distribution.

Reports like the Workflow Detail Report, Mapping Summary Report, Mapping Detail Report, and many others display all types of information regarding your Workflows, including widget usage, what tasks are used in the workflows, which workflows use parameter files and more.

There is also the Workflow Execution Summary Report. This report displays all kind of performance data and metrics including execution status, performance numbers for each workflow and even individual tasks, and success/failed row counts for sources and targets for each session task.

If you’re looking for more insight into your Informatica environment and would like to know more about what kind of performance you’re getting out of your Workflows, I recommend checking out Workflow Profiler. Download the free trial here.