Comparing SSIS package code...any suggestions?

Comparing SSIS Packages: Strategies for Code Review and Version Control

Learn effective methods and tools for comparing SQL Server Integration Services (SSIS) packages, crucial for code reviews, debugging, and managing changes in version control systems.

Comparing SQL Server Integration Services (SSIS) packages is a common challenge for developers and DBAs. Unlike traditional code files, SSIS packages are XML-based, making direct text comparisons difficult due to their verbose and often non-semantic changes. This article explores various strategies and tools to effectively compare SSIS packages, aiding in code reviews, debugging, and maintaining a robust version control workflow.

Understanding SSIS Package Structure

Before diving into comparison methods, it's essential to understand that an SSIS package (.dtsx file) is fundamentally an XML document. This XML defines all aspects of the package: control flow, data flow tasks, connections, variables, event handlers, and more. While XML is human-readable, the sheer volume and often arbitrary ordering of attributes and elements can obscure meaningful changes when performing a simple text-based diff. Tools need to be 'SSIS-aware' to properly interpret these files.

flowchart TD
    A[SSIS Package (.dtsx)] --> B{XML Document Structure}
    B --> C[Control Flow Elements]
    B --> D[Data Flow Tasks]
    B --> E[Connection Managers]
    B --> F[Variables & Parameters]
    B --> G[Event Handlers]
    C --> C1("Tasks & Containers")
    D --> D1("Sources, Transformations, Destinations")
    style A fill:#f9f,stroke:#333,stroke-width:2px
    style B fill:#bbf,stroke:#333,stroke-width:2px
    style C fill:#ccf,stroke:#333,stroke-width:2px
    style D fill:#ccf,stroke:#333,stroke-width:2px
    style E fill:#ccf,stroke:#333,stroke-width:2px
    style F fill:#ccf,stroke:#333,stroke-width:2px
    style G fill:#ccf,stroke:#333,stroke-width:2px

Simplified SSIS Package XML Structure

Comparison Strategies and Tools

Comparing SSIS packages effectively requires more than just a standard text diff tool. The ideal solution understands the logical components of an SSIS package and highlights differences in properties, expressions, and task configurations rather than just raw XML changes. Here are several approaches:

1. Using Visual Studio's Built-in Diff (XML Comparison)

When an SSIS package is opened in Visual Studio (with SQL Server Data Tools installed), it's rendered visually. However, for direct comparison, Visual Studio's built-in XML diff utility can be useful. If you right-click on two .dtsx files in Solution Explorer and select 'Compare Selected Files', it will show an XML comparison. While this is a raw XML diff, it can sometimes pinpoint changes if you know what to look for, especially for minor property changes.

<?xml version="1.0"?>
<DTS:Executable xmlns:DTS="www.microsoft.com/SqlServer/Dts" DTS:ExecutableType="Package">
  <DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>
  <DTS:Property DTS:Name="VersionComments">Initial version</DTS:Property>
  <DTS:Variables>
    <DTS:Variable DTS:DataType="System.String" DTS:Name="MyVariable">
      <DTS:VariableValue DTS:DataValue="OldValue"/>
    </DTS:Variable>
  </DTS:Variables>
  <!-- ... other package elements ... -->
</DTS:Executable>

Excerpt of an SSIS package XML showing a variable definition.

2. Specialized SSIS Comparison Tools

Several third-party tools are designed specifically for SSIS package comparison. These tools parse the .dtsx XML and present differences in a more human-readable, logical format, focusing on properties, tasks, and connections rather than raw XML nodes. Examples include:

• BI xPress (Pragmatic Works): Offers a robust SSIS comparison feature that highlights differences in a structured way.
• SQL Compare (Redgate): While primarily for database schema, Redgate also offers tools that integrate with SSIS for comparison.
• BimlFlex (Varigence): For those using Biml, comparing Biml scripts is often more effective than comparing the generated .dtsx files directly.

3. Using Version Control System (VCS) Diff Tools

Most modern VCS (Git, TFS, SVN) allow you to configure external diff tools. You can integrate specialized SSIS comparison tools as your external diff viewer for .dtsx files. This means when you perform a 'diff' operation in your VCS, it will launch the configured SSIS-aware tool, providing a much more useful comparison than a generic text diff.

1. Configure Git for SSIS Diff

Edit your global Git configuration (~/.gitconfig) or repository-specific config to define a custom diff driver for .dtsx files. This involves telling Git how to 'textconv' (convert to text) the .dtsx file and which external diff tool to use.

2. Define a 'textconv' driver

Create a script (e.g., dtsx_to_text.ps1 for PowerShell or dtsx_to_text.py for Python) that takes a .dtsx file path as input, parses its XML, and outputs a simplified, human-readable representation of key package elements (e.g., task names, connection strings, variable values). This script will be called by Git to generate a 'text' version for comparison.

3. Integrate with a visual diff tool

Configure Git to use a visual diff tool (like Beyond Compare, WinMerge, or a specialized SSIS diff tool) with the output of your textconv driver. This allows you to see the structured differences visually.

[diff "dtsx"]
    textconv = dtsx_to_text.ps1
    external = "C:/Program Files/Beyond Compare 4/BCompare.exe"

Example Git configuration for SSIS diffing (simplified).

4. Programmatic Comparison (Scripting)

For advanced scenarios, you can write custom scripts (e.g., in C# using the SSIS object model, or PowerShell) to load two .dtsx packages, iterate through their components, and compare properties programmatically. This offers the most flexibility but requires significant development effort. It's particularly useful for automated quality checks or generating custom comparison reports.

C# (SSIS Object Model)

using Microsoft.SqlServer.Dts.Runtime;

public class SsisComparer { public void ComparePackages(string packagePath1, string packagePath2) { Application app = new Application(); Package package1 = app.LoadPackage(packagePath1, null); Package package2 = app.LoadPackage(packagePath2, null);

    // Logic to iterate through tasks, connections, variables and compare properties
    // This would involve extensive reflection and property comparison
    System.Console.WriteLine($"Comparing {package1.Name} and {package2.Name}");
    // Example: Compare a variable
    Variable var1 = package1.Variables["MyVariable"];
    Variable var2 = package2.Variables["MyVariable"];

    if (var1 != null && var2 != null && var1.Value.ToString() != var2.Value.ToString())
    {
        System.Console.WriteLine($"Variable MyVariable differs: {var1.Value} vs {var2.Value}");
    }
}

}

PowerShell (XML Parsing)

[xml]$package1 = Get-Content -Path "C:\Path\To\Package1.dtsx" [xml]$package2 = Get-Content -Path "C:\Path\To\Package2.dtsx"

Example: Compare a variable value

$var1Value = $package1.DTS.Variables.Variable | Where-Object { $.Name -eq "MyVariable" } | Select-Object -ExpandProperty VariableValue | Select-Object -ExpandProperty DataValue $var2Value = $package2.DTS.Variables.Variable | Where-Object { $.Name -eq "MyVariable" } | Select-Object -ExpandProperty VariableValue | Select-Object -ExpandProperty DataValue

if ($var1Value -ne $var2Value) { Write-Host "Variable MyVariable differs: $var1Value vs $var2Value" }