I am interested in auditing dts packages via 2 scenarios:

1. Ensure the dts package doesn't change from one day to
another and...
2. Compare a dts package from one dB to another.

Can someone provide any suggestions?

For #1 - i would assume i can query the sysdtspackages
table to ensure no new row was created between yesterday
and today and that would show consistenty.

But for #2 - how would i be able to compare the details
of the dts package. Seems like the sysdtspackages table
contains the detail only in binary format? I need to
ensure that any task/property in one dts is the same as
another on a different db server. So i need a way to
compare the specific dts tasks in a format that allows me
to see any differences.

Any suggestions are appreciated.

Thanks.

#1 - True enough (but as you know that really only shows whether the package
was saved, not changed).

#2 - I've been wondering about this myself. Maybe you can compare the two
binary files, but of course that won't tell you what, specifically, was
changed. One way that comes to mind is to "thumb through" the steps and
tasks of a given DTS package and compare that to a table of expected steps
and tasks. That would also allow you to compare a DTS package on one server
to that on another. This could be done from a stored procedure (using the
sp_OA* procedures) or from a vbScript or from another DTS package designed
for auditing. Of course, this would all be a heck of a lot simpler if there
were sysDTSsteps and sysDTStasks tables.




"Rich" <rrabin (AT) z-tel (DOT) com> wrote

In article <uOys6CWcDHA.2460 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, J O Holloway
<jholloway (AT) pinncorp (DOT) com> writes
... but the version GUID will show you difference. A comparison of the
image packagedata column would also reveal differences, although not
very practicable.

utility from SQL 7.0 would be a start, but there is a tool that claims
to do the job already, I say as I haven't tried the latest version(s).
It did do just that, but was a bit slow and had a few other issues for
my linking. But try the latest version, after all it is free to try-

http://www.red-gate.com/sql/DTS_Compare.htm

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

I have loaded a dts package on a db server and changed
the text in an activex component and also changed a db
connection string. I saved it so that it would create a
new row in sysdtspackages. I did a binary compare of the
packagedata field and they are identical.

Where is this stuff saved?