SSIS vs. SQL: Source Code Management — Why SP Diffs Are Readable and `.dtsx` Diffs Are Not

Published Aug 30, 2018 · Updated Sep 2, 2026
by

Anyone diffing two versions of an SSIS package sees change markers scattered across the XML even for a trivial rename — eight “changed regions” in this article’s example, and the diff doesn’t even attribute the rename to the right task. The same modification in a stored procedure shows a three-line diff and is reviewable in 30 seconds. Source code management is a maintainability decision: not a tool question, but an artifact-format question.

What you’ll take away:

  • Comparing two versions of a T-SQL script in Visual Studio: a clearly readable inline diff.
  • Comparing two versions of an SSIS package (.dtsx): even renaming a script task produces eight “changed regions” in the XML, with the rename attributed to the wrong task.
  • Complex example: the same hierarchy/ranking task on [DimEmployee] from AdventureWorks — versioned as a stored procedure it’s clearly diffable, as raw .dtsx XML effectively not.
  • Source Code Management 2026: what has moved since the 2018 TFS state — Git, Liquibase/Flyway, sqlmesh.

Prerequisites: SQL Server 2017+ (for the examples), SSIS 2017+ (Visual Studio with SSDT), Git or Azure DevOps Server as VCS. The examples were developed in 2018 with Visual Studio 2017 and TFS — the diff argument transfers 1:1 to the Git-centric stack of 2026.

Contents

Overview

SQL Server Integration Services (SSIS) is a powerful toolbox for building ETL pipelines. There are plenty of good reasons to use SSIS, and plenty against. Within the Microsoft product stack, the alternative for building complex ETL pipelines is essentially Transact-SQL (T-SQL).

This article is part of a series of articles on the important decision criteria for choosing the right technology — SSIS and/or T-SQL.

Source code management enables source code to be stored in different versions. After a modification, the changed file can be saved as a new version. For every version, the source control system also stores metadata: the date and time of the change, the user ID of the person who made it, or a reference to a change request. Older versions of a document can be restored from these.

In addition, source code management systems offer features that are essential for multi-developer teamwork and for release management. In short: source code management is an essential building block of professional software development. In the Microsoft world, Git is today’s standard — either hosted on GitHub/GitLab/Bitbucket, or self-hosted via Azure DevOps Server (the successor to Team Foundation Server, TFS). Both integrate with the Visual Studio development environment.

Another important feature of source code management is comparing two versions of a file. Comparing two versions of the same file lets you identify the differences. One use case is the four-eyes principle: one developer edits a document, and another reviews the changes. Only the changes in the new version are relevant for the review, and exactly those are what the version comparison delivers.

A version comparison is only useful, however, when the meaning of the changes and ideally the reason for them are recognisable from the comparison. Comparing binary files is usually not helpful — neither the meaning nor the reason is visible. Comparing two versions of a text file has a high chance that both are recognisable. But this gets harder when the data in the text file is stored hierarchically and in a structured way. A text diff only stays meaningful as long as the serialisation remains stable between two versions, that is, as long as content changes are not drowned out by ordering, formatting, and metadata changes. SSIS packages are stored in a hierarchically structured XML file with the extension *.dtsx, while SQL statements are usually stored as plain text files with the extension *.sql.

This article describes version comparison of SQL scripts and SSIS packages using three examples.

  • Diffing a SQL script: two versions compared
  • Diffing an SSIS package: one rename in the .dtsx XML
  • Diffing complex development artifacts: SQL statement vs. SSIS package

Diffing a SQL script: two versions compared

The following figure shows the result of comparing two versions of a SQL script in Visual Studio. Deviations are highlighted in colour.

Visual Studio diff view of a SQL script: the previous version on the left with red markers, the modified version on the right with light green for changed lines and a stronger green for the actual changes. To the right of the vertical scroll bar, a track shows the position of the changes throughout the document.

The screenshot shows the SQL statement before the change on the left and the modified SQL statement on the right. In the modified statement, lines that contain a change are highlighted in light green. The changes themselves are highlighted in a stronger green. In the previous version on the left, the corresponding text is highlighted in red.

To the right of the vertical scroll bar, the changed regions are indicated throughout the document. A good description of using the file comparison can be found in Microsoft’s online documentation for Git in Azure DevOps.

Diffing an SSIS package: one rename in the .dtsx XML

ETL pipelines in SSIS are developed as SSIS packages. SSIS packages are stored as XML documents. Microsoft itself wrote about the SSIS package format, among other things:

In the current release of Integration Services, significant changes were made to the package format (.dtsx file) to make it easier to read the format and to compare packages. You can also more reliably merge packages that don’t contain conflicting changes or changes stored in binary format.

This quote stems from the historical Microsoft documentation about the SSIS package format — a page specifically about the format overhaul in SQL Server 2014 that has since been removed from the live documentation. The technical DTSX specification lives on as Open Spec MS-DTSX. So Microsoft explicitly evolved the format with the goal of improving comparison and merging. That doesn’t mean a line-based XML diff automatically becomes meaningful. The following example shows why.

The two screenshots below show two versions of the control flow of an SSIS package, in which only the name of the second script task was changed from B SCT Skripttask to D SCT Skripttask. The screenshots come from an SSIS package where tasks were simply placed in the control flow, named freely, and connected, but not configured further. The example is deliberately simple and only serves as an illustration.

Version 1

SSIS control flow Version 1: four script tasks A/B/C/E placed vertically, connected by precedence constraints, no further configuration.

Version 2

SSIS control flow Version 2: same arrangement as Version 1, but the second script task has been renamed from B SCT Skripttask to D SCT Skripttask.

Version comparison

Comparing the two versions is sobering: renaming a single script task results in eight changed regions of the XML document in this package, shown to the right of the vertical scroll bar.

Visual Studio diff view of the `.dtsx` XML of two SSIS packages: dense red and green markers scattered across the whole document, with eight clearly identifiable cluster markers along the right scroll bar. The actual content edit (a rename) is not directly recognisable amid the noise of the GUID reorderings.

Lines 60 to 77 represent, among other things, the script task B SCT Skripttask in the previous version. According to the comparison, this task was renamed to C SCT Skripttask — not to D SCT Skripttask. Lines 78 to 95 represent, among other things, the script task C SCT Skripttask in the previous version. According to the comparison, this task was renamed to D SCT Skripttask.

To be clear: only the name of the script task B SCT Skripttask was changed to D SCT Skripttask.

The text diff is technically correct here and still answers the review question incorrectly: it compares lines, not objects. Because it doesn’t know the identity of the tasks, it attributes the single rename to two different tasks.

Diffing complex development artifacts: SQL statement vs. SSIS package

This section looks at a somewhat more complex example that could be found in a similar form in real-world practice.

Task

In this example, the ranking of employees in the table [AdventureWorksDW2017].[DimEmployee] is to be determined per hierarchy level along the vacation and sick-leave hours of the employees. Four key figures are to be calculated for each employee. The calculation uses the SQL Server window functions specified for each key figure:

  • Vacation ranking, group assignment: NTILE(3) — NTILE(3) distributes the set of employees (per hierarchy level) as evenly as possible across three groups. If the number of rows isn’t divisible by three, the groups differ by at most one row, with the larger groups coming first. The assignment of an employee follows the ascending sort by vacation hours.
  • Vacation ranking, rank order: DENSE_RANK — DENSE_RANK assigns each employee (per hierarchy level) a position in a rank order. If two employees have the same number of vacation hours, both receive the same rank.
  • Sick-leave ranking, group assignment: NTILE(3) — analogous to vacation.
  • Sick-leave ranking, rank order: DENSE_RANK — analogous to vacation.

This task is to be solved with both a SQL statement and an SSIS package. In a second part, the developed artifacts are to be modified so that the ordering criterion is no longer vacation and sick-leave hours, but the employee’s hire date and birth date.

There are two challenges in this task:

  • Determining the employee hierarchy
  • Determining the ranking

SQL statement

Transact-SQL provides easy-to-use constructs for both challenges:

When data is structured by a parent-child relationship — as in the table [DimEmployee] — it can be evaluated comparatively compactly via a recursive CTE. This requires a consistent, cycle-free hierarchy with a defined root:

  1: CREATE OR ALTER PROCEDURE [dbo].[sp_insert_employee_hierarchy_ranking]
  2: AS
  3: BEGIN
  4:    SET NOCOUNT ON;
  5: 
  6:    TRUNCATE TABLE [dbo].[fct_employee_hierarchy_ranking];
  7: 
  8:    WITH CTE_Employee AS
  9:    (
 10:       -- Anchor of the recursive CTE: the CEO as the top-level employee
 11:       -- with no manager. [Level] = 1 marks the root of the hierarchy.
 12:       SELECT
 13:           [EmployeeKey]
 14:          ,[FirstName]
 15:          ,[LastName]
 16:          ,[Title]
 17:          ,[ParentEmployeeKey]
 18:          ,[VacationHours]
 19:          ,[SickLeaveHours]
 20:          ,1 AS [Level]
 21:       FROM
 22:           [AdventureWorksDW2017].[dbo].[DimEmployee]
 23:       WHERE
 24:           [ParentEmployeeKey] IS NULL
 25:       AND [Status] = N'Current'
 26: 
 27:       UNION ALL
 28: 
 29:       -- Recursive step: every employee whose [ParentEmployeeKey] points
 30:       -- to an employee already in the CTE. [Level] is incremented by 1
 31:       -- per depth. T-SQL joins anchor and recursive member with
 32:       -- UNION ALL.
 33:       SELECT
 34:           T01.[EmployeeKey]
 35:          ,T01.[FirstName]
 36:          ,T01.[LastName]
 37:          ,T01.[Title]
 38:          ,T01.[ParentEmployeeKey]
 39:          ,T01.[VacationHours]
 40:          ,T01.[SickLeaveHours]
 41:          ,T00.[Level] + 1 AS [Level]
 42:       FROM
 43:          [AdventureWorksDW2017].[dbo].[DimEmployee] AS T01
 44:       INNER JOIN
 45:          CTE_Employee AS T00
 46:          ON
 47:            T01.[ParentEmployeeKey] = T00.[EmployeeKey]
 48:       WHERE
 49:          T01.[Status] = N'Current'
 50:    )
 51:    INSERT INTO [dbo].[fct_employee_hierarchy_ranking]
 52:    (
 53:        [ParentEmployeeKey]
 54:       ,[EmployeeKey]
 55:       ,[LastName]
 56:       ,[FirstName]
 57:       ,[Title]
 58:       ,[Level]
 59:       ,[VacationHours]
 60:       ,[SickLeaveHours]
 61:       ,[VacationHours_NTILE]
 62:       ,[VacationHours_DENSE_RANK]
 63:       ,[SickLeaveHours_NTILE]
 64:       ,[SickLeaveHours_DENSE_RANK]
 65:    )
 66:    SELECT
 67:        [ParentEmployeeKey]
 68:       ,[EmployeeKey]
 69:       ,[LastName]
 70:       ,[FirstName]
 71:       ,[Title]
 72:       ,[Level]
 73:       ,[VacationHours]
 74:       ,[SickLeaveHours]
 75:       ,NTILE(3)     OVER (PARTITION BY [Level] ORDER BY [VacationHours],  [EmployeeKey]) AS [VacationHours_NTILE]
 76:       ,DENSE_RANK() OVER (PARTITION BY [Level] ORDER BY [VacationHours])                 AS [VacationHours_DENSE_RANK]
 77:       ,NTILE(3)     OVER (PARTITION BY [Level] ORDER BY [SickLeaveHours], [EmployeeKey]) AS [SickLeaveHours_NTILE]
 78:       ,DENSE_RANK() OVER (PARTITION BY [Level] ORDER BY [SickLeaveHours])                AS [SickLeaveHours_DENSE_RANK]
 79:    FROM
 80:       CTE_Employee;
 81: END;
 82: GO

This statement provides each employee with the hierarchy level in column [Level] plus the requested key figures along vacation and sick-leave hours. One detail of the OVER clauses is deliberately asymmetric: for NTILE(3), [EmployeeKey] as a unique second sort key ensures that ties in the hour values are distributed deterministically across the three groups — employees with identical hour values can still end up in different groups. For DENSE_RANK it is omitted, so that ties actually receive the same rank. With the AdventureWorksDW2017 data set and the filter on [Status] = N'Current', the query returns 254 employees across five hierarchy levels. For these five levels, SQL Server’s default recursion limit (100 recursions) is sufficient. For deeper hierarchies, OPTION (MAXRECURSION n) needs to be considered.

Result table from the T-SQL statement: 254 rows with the columns ParentEmployeeKey, EmployeeKey, LastName, FirstName, Title, Level (1 to 5), VacationHours, SickLeaveHours, plus the four ranking columns for NTILE and DENSE_RANK.

Comparing the statement shown above with a modified version, in which VacationHours is replaced by HireDate and SickLeaveHours by BirthDate, is this article’s first diff example: three column edits and one data-type change. The diff in Visual Studio shows the roughly 15 affected lines clearly localised.

SSIS package

As easy as this task was to solve in T-SQL, developing the SSIS solution was hard and above all time-consuming when it is to be built exclusively with SSIS components (including script components) and without offloaded SQL. The following approach was chosen for the solution shown here:

  • Each task requires two tables in the database.
  • The Data Flow 1000 DFT Calculate Levels determines only the hierarchy.
  • The Data Flow 3000 DFT Calculate Ranking determines only the ranking and stores the result in a table.
  • The ranking for vacation and sick-leave hours is calculated in two script tasks each.

There may well be a much simpler solution.

Control Flow

The control flow of the SSIS package still looks reasonably simple with four tasks.

SSIS control flow of the complex example: four tasks connected sequentially — Truncate, Calculate Levels, Level Counts, Calculate Ranking.

Data Flow 1000 DFT Calculate Levels

While T-SQL can evaluate the hierarchy recursively, the SSIS data flow itself offers no directly comparable mechanism. If the recursion is not to be offloaded into a SQL query, the mapping of the five hierarchy levels has to be developed level by level — which ties the solution to the concrete depth of the hierarchy.

Data Flow `1000 DFT Calculate Levels`: vertically arranged layout with more than 30 tasks (Multicast, Sort, Merge Join, Derived Column for Levels 2 through 5) — the non-generic hierarchy calculation fills a full screen.

Data Flow 3000 DFT Calculate Ranking

The actual ranking calculation happens in Script Tasks by comparing two consecutive records.

Data Flow `3000 DFT Calculate Ranking`: two Sort Tasks (one by Level + VacationHours, one by Level + SickLeaveHours), two Script Tasks to calculate the NTILE/DENSE_RANK columns, and a final OLE-DB Destination writing into the target table.

Modifying the package

After completion, the package was committed to Git (or, in an on-prem stack, via Azure DevOps Server) and then modified so that the ordering criterion for the ranking was no longer the vacation and sick-leave hours but the hire date and birth date. In essence, three field names and one data type had to change. Despite the larger number of tasks involved, the scope of the changes stayed limited. In this example the modification was done within a few minutes, and the result was committed as well.

Comparing the two versions

Comparing the two committed versions produced the following changes:

Visual Studio diff view of the `.dtsx` XML of the two SSIS package versions: the entire document is shot through with red and green markers, and the right scroll bar shows dozens of hit locations, although only three field names and one data type were changed in substance.

What matters for judging the scope of the change in the SSIS package is the right-hand vertical scroll bar. The hit locations of the modifications are highlighted in green and red along the scroll bar. The diff marks changes across large parts of the XML file, although in substance only three field names and one data type were changed.

While the T-SQL solution can be developed relatively quickly, developing the SSIS package is laborious and took several hours. The main reasons were the poor readability of an SSIS package, but also the surprising realisation that SSIS offers no ready-made approach for what looked like a simple task. The first attempt, calculating the hierarchy level with a single formula expression, ended after two to three hours with the white flag. What remained is the approach shown here — at the cost of the readability and flexibility of the SSIS package.

After finishing the package and committing the solution, only relatively few changes were required to switch the ordering criterion for the calculation. Those few changes resulted in numerous changes in the underlying XML document. A version comparison leaves the developer puzzled about what actually changed in the SSIS package. By contrast, with T-SQL the solution path is directly readable and changes are traceable.

The example thus establishes two independent findings: the implementation is more compact in T-SQL, and the SSIS artifact produces a version comparison that is useless for reviews despite a small substantive change. For this article’s diff question, the second finding carries the weight.

Source Code Management 2026

The article’s core question — how diffable is a development artifact — is no longer a pure SQL-Server-/SSIS-only question in 2026. The Microsoft stack is just one option among others, and the diff pragmatics argument carries over across stacks.

The Microsoft stack today

Visual Studio database projects come in two flavours today: classic SSDT with .sqlproj projects on an MSBuild basis, and the SDK-style format Microsoft.Build.Sql, the default format of the SQL Database Projects extension for VS Code. The dividing line runs right through the Visual Studio world (as of September 2026): Visual Studio 2022 ships SDK-style as a preview component, while Visual Studio 2026 supports only the classic format. SSIS projects ship separately via the SSIS Projects Extension, which supports Visual Studio 2022 and 2026. The packages remain .dtsx-based XML artifacts in which ordering, layout, and metadata changes continue to bury a textual diff in considerable noise. Microsoft itself has shifted the source-control standard from Team Foundation Server (TFS) to Git: Azure DevOps Server is the TFS successor and supports Git as the modern VCS backend. TFVC remains optionally available for legacy compatibility. The cloud move to Azure Data Factory and Synapse Pipelines poses the same question in JSON instead of XML: there, too, the structure and change patterns of the pipeline definitions decide the diff quality — the tool changes, the artifact-format question stays.

The Git world

Outside the Microsoft stack the world is more open and more SQL-centric. Three building blocks shape the 2026 version-control practice:

  • Git as default VCS: GitHub, GitLab, and Bitbucket are the usual homes. Stored-procedure diffs (SP diffs) are plain-text diffs and can be reviewed in any pull-request tool without extra tooling.
  • Schema migration tools: Liquibase and Flyway typically version schema changes as plain-SQL migration files (Liquibase changelogs alternatively as XML, YAML, or JSON). Each V001__add_employee_table.sql is diffable like any other SQL script.
  • sqlmesh: sqlmesh versions SQL pipelines with explicit state tracking. The .sql files are the versioned artifacts, and the maintainability lever for diffing is the same as for a stored procedure, just stack-agnostic (Postgres, Snowflake, BigQuery, Redshift, Databricks, ClickHouse, DuckDB, Microsoft Fabric, and more). Since March 2026 sqlmesh has been a Linux Foundation project (contributed by Fivetran), placing it under community governance rather than single-vendor stewardship.

The question remains: what can be diffed?

Across stacks, the same point holds: it’s the artifact format that decides, not the tool: it determines whether a standard diff is enough or a structure-aware tool is needed. Plain SQL is trivially diffable — whether stored procedure, Liquibase/Flyway migration, or sqlmesh model. XML and JSON artifacts with GUID and position properties, such as .dtsx or ADF pipeline JSON, can also be diffed textually, but structural changes produce a diff that is barely usable for reviews. That’s where structure-aware comparison tools help.

Whoever wants to keep ETL logic versionable puts the substance into SQL artifacts. Whoever still has to version SSIS packages complements that with:

  • BI Developer Extensions (formerly BIDS Helper) or the SSIS Compare and Merge Tool from the Visual Studio Marketplace provide a structural-tree diff instead of the XML diff. Both are community tools — there is no official SSDT feature with a GUID-noise filter.
  • A screenshot diff of the Control Flow and Data Flow designers is the visual pragmatic approach.
  • Offload accompanying SQL scripts into stored procedures, so the .dtsx only contains orchestration and the content diff lives in the SQL script. That’s the strategic answer — and SSIS vs. SQL: Readability and Maintainability makes the same point from the maintainability angle.

Take-away

  • SP diffs are readable with standard diff tools, raw .dtsx diffs are not. That is a strategic architectural decision, not a tool question — and it carries across stacks to every XML- or JSON-based ETL format.
  • Trivial edit ≠ trivial diff. In this article’s example, renaming a script task produces eight XML region markers, and the diff attributes the rename to the wrong task. Whoever wants an audit trail has to pick a diff-friendly artifact format.
  • Versionability is an artifact-format question. Plain SQL wins: stored procedure, Liquibase/Flyway migration, sqlmesh model. XML/JSON artifacts can be diffed textually, but the diffs only become readable with structure-aware tooling.
  • Strategic answer: offload the ETL substance into SQL artifacts and keep .dtsx packages (or ADF pipelines) as pure orchestration. The substantive change then stays traceable in the SQL diff — the full audit trail comes from the diff, the commit metadata, and the review together.

FAQ

Can I version .dtsx packages in Git in any meaningful way?

Yes, but the diff value is limited. .dtsx files can be committed and versioned without trouble. Meaningful diffs and low-conflict merges are much harder, however, because saving a package can land ordering, layout, and metadata changes in the XML alongside the substantive change. The practical answer is: commit them and additionally maintain a readable companion diff — via the BI Developer Extensions, a screenshot diff, or SQL offloading.

Why does Git show so many changes for .dtsx files?

SSIS doesn’t store just the substantive change when you edit a package. Depending on the package and the change, ordering, layout, and metadata changes (such as version properties) end up in the .dtsx XML as well. A line-based diff therefore marks changes across the file, even when only a name or a column changed in substance. Structure-aware diff tools or offloading the logic into SQL artifacts provide relief.

Which tool gives me a readable .dtsx diff?

Several approaches exist: (1) BI Developer Extensions (formerly BIDS Helper) for Visual Studio with Smart Diff (structural-tree diff instead of XML diff) — Marketplace builds are officially available for VS 2017/2019, and no full VS 2022 build is published. (2) SSIS Compare and Merge Tool from the Visual Studio Marketplace, a community tool that covers the noise filter and also runs in VS 2022. SSDT itself doesn’t ship a .dtsx-specific diff. (3) A screenshot diff of the Control Flow and Data Flow designers is pragmatic: not a versioned diff, but visually directly graspable. Each of these three paths beats the naive XML diff.

How do I organise a mixed repository with stored procedures and SSIS packages?

A separation by artifact type has proven itself: SQL artifacts (stored procedures, views, functions) live in a Visual Studio database project with *.sql files, SSIS packages in a separate Visual Studio Integration Services project with *.dtsx files. Both projects land in the same Git repo, but the pull-request reviews differ by artifact type: SQL diffs are reviewed as plain text, SSIS diffs need the structural-tree diff or the screenshot complement. As a best practice, offload as much SQL into stored procedures as possible — that reduces the .dtsx diff problem to pure orchestration edits.

What does an SP diff look like in the typical pull-request workflow?

An SP diff in a GitHub, GitLab, or Azure DevOps PR view typically spans just a few lines: changed lines highlighted in light green, deleted ones in red. The reviewer sees at a glance that [VacationHours] was replaced by [HireDate] and the column data type switched from smallint to date. This pattern is exactly what makes the audit trail of an ETL pipeline practicable in the first place.

SSIS-vs.-SQL cluster:

ETL context: