如何合并SSIS包文件?

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

我想知道是否有人对合并SSIS的dtsx文件有什么建议。以下是我看到的使合并变得困难的问题:

They are xml which can already be a pain for merging.
它们是xml，对于合并来说已经很痛苦了。
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.
它们可以嵌入c#脚本，在这种情况下，它们将同时拥有c#源代码和dll文件的base64编码字符串。
They describe the flow of data in the package as well as the layout of the elements in the IDE.
它们描述包中的数据流以及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.~~

如果来自微软的任何人都在听，那么这些问题中有很多都是通过将包分成几个文件而不是一个文件来解决的。一个dtsx可以是描述流的xml、描述布局的xml、一些.cs源文件和一些dll。但事实并非如此。我想知道为什么有人使用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.

我在网上看到的唯一解决方案是确保编辑时dtsx文件被锁定，这样只有一个用户会有更改。当您只讨论一个分支时，这种方法可以很好地工作，但是如果您在不同的分支中使用多个dtsx副本(或者上帝不允许，DVCS)，那么在任何时候进行更改时，都没有可行的方法来锁定它们。除此之外，这并不能真正解决问题，除非你还能确保在合并之前没有其他人修改过它。

5 个解决方案

#1

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

使用免费的Visual Studio外接程序招标助手可以在两个方面帮助您解决困境。

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.

BIML: BIML是业务智能标记语言(BIML引用)。可以使用.biml文件生成SSIS包。BIML文件应该可以更好地处理合并操作，因为它们的结构更加严格。尽管我还没有合并它们的经验，但我已经使用BIML文件创建SSIS包的速度比SSIS UI允许的速度快。它非常有助于复制相似的数据流，并更改惟一的属性。
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.

Smart Diff:投标助手也有一个聪明的Diff功能，用于帮助比较您的SSIS包中的不同之处。它不会帮助自动合并，但会在显示差异之前删除布局信息并对XML进行排序。这将显示两个SSIS包之间的实际功能差异。然后，您可以使用该信息手动合并更改。对于您在revelator的回答中给出的示例，您将使用Smart Diff将SSIS的1.0版本与1.0分支中的固定版本进行比较，然后您将看到手动将修复应用到2.0分支所需的更改。

#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.

我建议不惜一切代价避免合并dtsx文件——这将是一个痛苦的世界!我通常开发SSIS项目的方法是将每个不同的工作片段分割成一个单独的包/dtsx文件，然后从主包调用它们。这意味着团队中不同的人可以处理不同的包，而不会相互重叠。这在源控制系统中工作得很好。另一个优点是可以独立地执行或测试每个组件。

#3

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.

使用较小的包是可取的，因为它们的响应速度更快。

#4

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.

如果您需要真正的合并功能，您将不得不手工编写包。由于XML中所有的连接(沿袭ids等)和设计器细节，所以无法在不破坏数据流或布局的情况下合并文件。

#5

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.

看看BIML变压器。BIML(业务智能标记语言)是一种更简单的方法来编辑和控制您的SSIS包。只需下载投标助手并检查本文。

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.

transformer还允许您对一组SSIS包应用相同的更改，而不只是一次手动应用一个SSIS包。

Cheers

干杯

#1