Tuesday, January 22, 2008

Using Beyond Compare to compare 2 SSIS packages

Pretend you have two SSIS packages that you want to compare. Let's assume you don't care about cosmetics like position and width of squares, and that you are concerned about connections, queries, and everything else except GUIDs of objects.
When you save a SSIS package you get a .dtsx file. It is an escaped XML file for the most part. This means it is also text based. Beyond Compare does a great job of comparing text file. However, all the things we said we didn't want are also in this file.
Beyond Compare is powerful enough to allow us to tell it what things need to be ignored in the file. This is done by doing the following.
Open Beyond Compare | Tools | Pick Rules | New Rules...
NOTE: Later you can get back to it by choosing it in the menu first. Then after that you just have to go to the Beyond Compare | Tools | Edit Current Rules...
General Tab:
Name: SSIS Package
Associated with: *.dtsx
Whitespace Includes:
Tabs and spaces (8 character positions)
Line endings: checked
Importance Tab:
Unimportant Text checkboxes: check them all
Here is the important part.
Click the New button.
Category: Regular Expression
Regular Expression: (width|height|top|left|x|y)="-?\d{1,10}"
Click the New button.
Category: Regular Expression
Regular Expression: \{.{36}\}
Click the New button.
Category: Regular Expression
Regular Expression: ddsxmlobjectstreaminitwrapper binary=".{1,24}"
All other tabs and values are default values.
Now when you compare .dstx files these rules will automatically be applied, and the unwanted stuff will not be used as criteria when comparing the files.

8 comments:

yannickb said...

thanks for this
One more to complete the rule... to ignore versionBuild
Regular expression:
<DTS:Property DTS:Name="VersionBuild">-?\d{1,10}</DTS:Property>

Brent V said...

Hi yannickb,

Thank you for the tip. That is an excellent addition. Thank you for the feedback.

Thanks,

Brent

Jason said...

Very helpful! Thanks much!

Benny said...

very useful post. Thank you so much.

Brent V said...

Benny,

Thank you for the feedback. So glad it was of help.

Brent

Brent V said...

Jason,

Thank you for the kind feedback. I am so glad it helped.

Brent

Anonymous said...

Could you update for Beyond Compare Version 3? Many Thanks :)

Anonymous said...

Could you update for Beyond Compare Version 3? Many Thanks :)