Six months ago I inherited an SSIS package from a developer who had left the company. The package worked — sort of. It ran in production every night and produced output that looked right. Nobody had touched it in eighteen months.
Then I needed to make a change.
There was no source control history. No documentation. The connection strings pointed to a server that no longer existed (a hardcoded override somewhere made it work anyway). There were three commented-out data flow paths with names like "OLD WAY" and "TRY THIS." I couldn't tell what the package had ever done or why the current version was the current version.
I rebuilt it from scratch. Three days. Most of that time was archaeology, not development.
The Mental Model That Creates This Problem
Data developers tend to think of SSIS packages as deployments, not source code. You build the package, you deploy it, you're done. The .dtsx file sitting on the server is the source of truth.
Software developers don't think this way. They know the running binary is not the source of truth — the repository is. The binary can be rebuilt from source at any time. If you lose the source, you lose the ability to understand, modify, and maintain the thing.
An SSIS package is source code. The .dtsx file is XML. It has a history. It has authors. It has decision points where someone chose one approach over another. That history should be preserved.
What Source Control Does for You
Putting your SSIS project in source control — whether that's TFS, SVN, or Git — gives you three things that are hard to get any other way.
History. Every change is recorded. When the package breaks after a deployment, you can diff the current version against the previous one and see exactly what changed. "It worked last week" becomes answerable.
Blame. Not as a punishment mechanism — as a communication mechanism. You can see who made a change and when, which means you can have a conversation about it rather than guessing.
Rollback. If a change breaks something, you can revert to the last working version while you investigate. This is the difference between a five-minute recovery and a weekend emergency.
The Practical Setup
If you're using BIDS with SQL Server 2008 R2, source control integration is available via the Team Explorer client for TFS. The experience isn't seamless — SSIS projects don't diff as cleanly as C# code because the XML is verbose and auto-generated — but version history is version history. In the SQL Server Data Tools preview for Visual Studio 2010, the integration has improved somewhat.
Either way, the workflow is the same:
- Create a project in your source control system
- Add the .dtproj and all .dtsx files
- Commit before and after every meaningful change
- Write commit messages that explain why, not just what
That last point matters more than people realize. "Fixed customer load" tells you nothing in six months. "Fixed customer load — source system started including deleted records, added WHERE IsDeleted = 0 to source query" tells you everything.
What This Changes
Once you start treating SSIS packages as source code, other things follow naturally. You start thinking about what a "version" means. You get more careful about the difference between the development copy and the production copy. You stop making changes directly on the production server.
The deeper change is psychological. Source control makes you feel accountable to the history of the system. That accountability tends to produce more careful, more deliberate work.
Trust me on this one: if you're not doing it yet, start now. Future you will be grateful.
If you've found a workflow that works well for SSIS under source control — especially around branching or handling connection string overrides across environments — I'd love to hear about it. As always, I'm here to help.