So you have your SQL 2008 R2 or SQL 2012 server up and running and ready to pull the pin on that tired old Analysis Services. You are concerned about something breaking when you turn out those lights for the last time. A last minute search of any ETL packages you suspect may still look for the old server after it is gone…
For SSIS packages stored in the MSDB database, a fantastic article by James Greeves on how to perform an object search on all SSIS packages deployed on a server.
If we are talking legacy SQL 2000 content, then for DTS packages, the search becomes a little more troublesome. A good discussion here about how to write an ActiveX script to perform said task.
SQL Server Central Active X Solution.
However this tool (DTS Power Search) is absolutely brilliant for a one off adhoc search of all DTS packages on a specified server or list of servers . Even if the DTS packages have been imported as legacy to a SQL Server 2008 R2 instance, the tool will still identify and search the old package content nicely.
With SQL 2012 finally shutting the door on SQL 2000 you may find this article useful.
Happy object searching !
