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.

Cost of Query in Oracle

One way to tell what is happening when a query is executed on Oracle is to open up SQL Plus and type the following before your SQL statement you want to analyze. Turning Autotrace on will give you performance cost in the way of an Execution Plan.

SET AUTOTRACE ON

When you are done, turn it off with

SET AUTOTRACE OFF

Timing SQL Plus query

To time a query in Oracle SQL Plus do the following.

SET TIMING ON

select * from mytable

.... Results here....

Elapsed: 00:00:00.15

Get definition for view in Oracle

set LONG 10000 select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';

Line one must be at least the size of the statement used to create the view. Either pick a really big number if you think the view is large or use the following to determine how long it is first. If it isn't, the text will be truncated

select text_length from all_views where view_name = 'MY_VIEW'

NOTE: name of the view is case sensitive as are most every thing in Oracle.

If you want to capture output to something other than a SQL Plus window such as a file, be sure to include do the following before you execute the select statement.

spool c:\v_person.sql

To close the file when you are done writing output to it, call the following:

spool off

To summarize:

select text_length from all_views where view_name = 'MY_VIEW'

10000

-- whatever value you get back, use on the next statement.

set LONG 10000

spool c:\v_person.sql

select TEXT from ALL_VIEWS where view_name = 'MY_VIEW';

spool off