I need to find a DTS package that contains a specific table name. There are 200+ DTS packages each with multiple objects in them.
My_Prod is the table name. Is there a T-SQL command to find all the DTS packages that reference this table name?
+1
A:
There's no SQL command, but the files are XML-based, so you can do a Windows search on the file contents to see if your table name pops up in any of them.
Eric
2009-07-29 19:34:56
good answer. but it didnt work.
funkymunkey
2009-07-29 20:44:00
Did you search inside the file? I think it's a different search box on Windows search.
Eric
2009-07-29 21:42:46
+1
A:
As Eric said, there isn't a SQL command that I am aware of.
You could take a look at SSIS-DTS Package Search. I've never used it but it looks like it may work for what you need.
Also, you could take a look at this article. I slightly modified the code from it to search all DTS packages on a server and change connection settings that were pointed towards a specific server. It would be a little work but you might be able to get it to do what you want.
Hope this helps.
BrianD
2009-07-30 02:04:25