How do I merge SSIS package files?

Question

I am wondering if anyone has any advice on merging SSIS's dtsx files. Here's the problems I see that make merging difficult:

• They are xml which can already be a pain for merging.
• They can have embedded C# scripts in which case they will have both the C# source code and the base64 encoded string of the dll file.
• They describe the flow of data in the package as well as the layout of the elements in the IDE.

If anyone from Microsoft is listening, a lot of those problems are solved by making the packages several files rather than one file. One dtsx could be an xml describing the flow, an xml describing the layout, some .cs source files, and some dlls. But that's not how it is. Makes me wonder why anyone uses dtsx.

A non-solution

The only solution I've seen online is to ensure that the dtsx file is locked when editing so only one user will have changes. This works fine when you're only talking about one branch but if you're working with multiple copies of the dtsx in various branches (or god forbid, DVCS), then there's no feasible way to lock them all anytime you make a change. Besides that wouldn't really solve the problem unless you could also make sure no one else changed it before you could merge it everywhere.

Answer 1

Using the free Visual Studio add-in BIDS Helper may help with your dilemma in two possible ways.

1. BIML: BIML is Business Intelligence Markup Language (BIML Reference). You can use .biml files to generate your SSIS packages. BIML files should work better with merge operations because of their more rigid structure. Although I have no experience with merging them yet, I've been using BIML files to create my SSIS packages faster than the SSIS UI allows. It has been very helpful with copy-pasting similar data flows and changing just the unique attributes.

2. Smart Diff: BIDS Helper also has a Smart Diff feature built in to help compare differences in your SSIS packages. It will not help auto-merge, but it will strip out layout information and order the XML before showing the differences. This will show you actual functional differences between two SSIS packages. Then you can use that information to manually merge changes. For your example from your comment on revelator's answer, you would use Smart Diff to compare version 1.0 of your SSIS to your fixed version in the 1.0 branch, then you would see just the changes necessary to apply that fix manually to your 2.0 branch.

Answer 2

I'd recommend avoiding merging dtsx files at all costs - it's going to be a world of pain! The way I generally develop SSIS projects is to split each distinct piece of work into a separate package/dtsx file, then call these from a Master package. This means that different people in the team can work on different packages without overlapping onto each others work. This works very well in a source controlled system. Another advantage is that each component can be independently executed or tested.