Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Profiling SSIS packages for component level change tracking


By:   |   Last Updated: 2009-12-03   |   Comments   |   Related Tips: More > Integration Services Configuration Options

Problem

It is very cumbersome to tell the difference between two versions of a SQL Server Integration Service (SSIS) packages using a standard tool like Team Foundation Server (TFS) , Visual Source Safe (VSS) or similar tools. When changes between two files are compared the changes are shown in Extended Markup Language (XML) file, where the development of packages is done using a graphical user interface. Also any trivial changes regarding formatting, position, and logic changes are highlighted when comparing two different versions of the same package. In this tip we will look at an add-in that helps you better identify changes between two versions of an SSIS package.

Solution

The BIDS Helper Add-in Reports can be used as a solution by profiling component level changes for each release or version of the package.

BIDS Helper is a Visual Studio .Net Add-In that extends the functionality of Business Intelligence Development Studio (BIDS) for each of the project types that BIDS supports i.e. SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS).

There are three different features available in BIDS Helper which can be used for change tracking.

  • (I) Smart Diff
  • (II) Sortable Package Properties Report and
  • (III) Non-Default Properties Report

Below is the explanation of how each of the features can be helpful in version-control or change tracking.

(I) Smart Diff:

This feature is a more intelligent way to compare the changes between two versions of the package that are version controlled using VSS / TFS. Note that this feature won't work if the version control system is not VSS or TFS Client.

When Smart Diff is used, it shows specifics of the properties changed, and takes care of the formatting or user interface related changes that are not important or impacting from a functionality point of view. More details about the feature can be seen here, where the feature is described in greater detail.

Comparison Without BIDS Helper

Comparison With BIDS Helper Smart Diff feature

As you can see the BIDS Helper only highlights areas of concern instead of every single change that has occurred. This will help minimize the time it takes for you to review the changes.

(II) Sortable Package Properties Report and (III) Non-Default Properties Report:

Though the above feature is a great for identifying changes, but still it is not sufficient enough to track the changes. Identifying what changes were made to which components over time by looking at XML using the TFS compare feature or the Smart Diff feature isn't that simple for everyone.

The Sortable Package Properties Report is a package level report that creates a report output of a fixed set of package level properties and can be executed from the solution level. A sample report is available here.

The Non-Default Properties Report is the key to the problem described above. Whenever we make changes to the properties of a component, the property is said to have a non-default value and gets highlighted in bold in the properties pane/window. When this report is executed at the solution level, it offers a list of all properties with the package irrespective of the component that has a non-default value. Based on the selection criteria, it generates a report of all the non-default properties with the tasks / transforms listed under it. Also this report can be exported to Excel / PDF as it is rendered using Reporting Services. A sample report can be seen here.

More details about this reporting feature can be read here.

Non Default Properties Selection Pop-up to generate report

So the solution that can be used to track changes, is version-controlling this report in alignment to the version control of the package itself. Whenever a package is checked-in to the version-control system, a non-default properties report of the solution should be extracted and checked-in along with it. When one needs to identify the changes made to the components in the package, looking at two different versions of the report, one can easily see the changes, compared to the XML based comparison of the package files. If the changes that were made are still not clear you can look at the XML using the Smart Diff feature.

Next Steps
  • Download and install BIDS Helper
  • Create a Non-Default Properties Report for your solution, along with the Sortable Properties Report at the solution level.
  • Whenever an package is changed and checked in to the version control system, prior to that create a separate non-default properties report for the package and version-control these.
  • Whenever a build-release is made, create a non-default properties report at the solution level and version-control this report as well.
  • Take time to review what other features are available in BIDS Helper


Last Updated: 2009-12-03


next webcast button


next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools