By: Siddharth Mehta | Comments | Related: > 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
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
