10

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.

ZombieDev
  • 959
  • 1
  • 10
  • 25

5 Answers5

8

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.

Zusukar
  • 382
  • 2
  • 8
  • I don't really do SSIS stuff anymore. But this seems like a decent answer. That BIML thing looks like it would be easier to diff (as easy as any XML file can be). – ZombieDev Aug 05 '16 at 20:20
7

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.

grapefruitmoon
  • 2,988
  • 1
  • 22
  • 26
  • 1
    We do some splitting into smaller packages but most of our packages do very specific things that don't lend themselves well to splitting them into smaller pieces. Even with that, that only works for keeping people from stepping on each others toes in the same branch. Say we have some package to import a special file format. We find a bug and fix it in that dtsx in version 1.0 of our software. But in the 2.0 branch parts of import.dtsx have been changed to get better performance or something. Those two changes have to be merged or we'll have that bug all over again in when 2.0 is released. – ZombieDev Mar 29 '11 at 18:11
0

Have a look at BIML transformers. BIML (Business Intelligence Markup Language) is a much easier way to edit and control your SSIS packages. Just down load the BIDS helper and check out this article.

http://bimlscript.com/Walkthrough/Details/68

Transformers also allow you to apply the same change to a set of SSIS packages not just manually one at a time.

Cheers

gazma
  • 89
  • 7
0

Only way we get merging to work is to open both packages, copy everything from one package, paste into the other, then compile every script task if they have conflicts.

Working with smaller packages is advisable though, as they just respond faster.

cairnz
  • 3,917
  • 1
  • 18
  • 21
  • Copying the contents of one dtsx (let's call it source) to another (let's call it target) and saving is not merging. That's just discarding anything that has changed in target. If both source and target have changed, you want the end result to be a combination of the changes from both. – ZombieDev Mar 29 '11 at 18:06
  • I was thinking merging like that, merge file a and b - not in a source-control-system-way :| Disregard my comment(s) – cairnz Mar 29 '11 at 20:46
0

If you need real merging capabilities you will have to manually code the packages. Because of all the wiring (lineage ids, etc) and designer specifics in the XML there is no way to merge changes between files without breaking either data flows or the layout.

cnordbakk
  • 9
  • 1
  • This is what I think too. That you can't merge them. I would mark this as the answer but it is really just confirming the problem. I don't know if there **is** an answer – ZombieDev Apr 07 '11 at 12:55