Hi

I'm trying to figure out the best way to identify which packages are
affected by changes in either the source or target databases.
These are my findings sofar:

1) Rely on accurate design documentation for the DTS packages to find
affected packages.
2) Store the packages in Meta Data Services
2a) Export the packages to XML files and create reports/search these
XML files to find affected packages.
2b) Query the meta data tables directly or using the object model. (The
information is scattered over several tables and it seems quite difficult to
obtain this).
3) Save a copy of all the packages as VB files. Add these to a Visual Studio
project and search through the project for affected packages.

I would be grateful for any information on what is regarded as the "best
practice" and/or any comments from the community.

Best practice? Ugggh

Personally I would be looking at doing it the object model way.

The problem here is that you may have an ExecuteSQL task in your package (or
DataPump task)

Searching for an object in the SQLStatement/SourceSQLStatement property is
easy if it is

INSERT INTO tblMyTable(............................)
OR
SELECT a,b,c FROM MyTable


But what if it is

EXEC p_MyStoredProc ?,?


You may then need to look inside the proc also.



--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Urquell" <Urquell (AT) discussions (DOT) microsoft.com> wrote