April 4, 2008 at 9:18 am
How can I find which DTS package(s) references a particular table. SQL Server 2000.
Thnak you.
April 4, 2008 at 9:29 am
There's no good way to do this in SQL Server since the packages are stored in a binary format. There might be some scripts/utilties that will move them all to VB files in the file system. Then you could do a simple search (grep-type search) of the files.
April 4, 2008 at 10:29 am
Hi David,
One of the other developers here at Red Gate just put me on to this. You can do this, but it's not as straightforward as you might like, and there's no way to do it directly from within SQL Server.
There are two possible approaches.
Firstly you could use the DTS COM API to export the packages you're interested in as VB source (as Steve's already suggested), and then run a text search for the table name against the resultant files. I'm not sure whether or not this will completely export all properties, custom tasks etc., etc., though, and in any case it's quite a lot of work.
The second approach is to use a tool, called DTS Package Compare, that Red Gate created a while ago for comparing (you guessed it) DTS packages. The solution is slightly roundabout, but it's less work than the above.
So first off, go to
http://labs.red-gate.com/index.php/DTS_Package_Compare
to download and install the tool.
There's a little command line utility in this tool that you'll need to use to export the packages you're interested in as XML. This will drill down through all properties, custom tasks etc., and spew the whole lot into the XML file. You will have to do it one package at a time, but you could perhaps automate this by putting all your package names and versions into a text file and using some sort of script, be it VB, Powershell, or even bash, to iterate over the values. You could just copy and paste the command multiple times and then paste in the package name, package ID, and package version for each DTS package.
You'll find that DTS Package Compare should install somewhere like this (it might be a little different):
[font="Courier New"]C:\Program Files\Red Gate\DTS Package Compare 2.0[/font]
In this directory you'll find a utility called RedGate.DtsCompare.PackageLoader.exe.
If you execute the following command you'll get some help on the tool:
[font="Courier New"]> RedGate.DtsCompare.PackageLoader.exe /? /v[/font]
This describes all the switches for the tool. It's pretty basic because we were only interested in getting it to retrieve packages one at a time, and store them in XML files.
So, assuming integrated security, the following command should retrieve a given package and write it out to the specified target file:
[font="Courier New"]> RedGate.DtsCompare.PackageLoader.exe /server:servername /packagename:mypackagename /packageid:package_id_guid /versionid:version_id_guid /targetfile:outputfile.xml[/font]
If instead you use SQL Server authentication you'll also need to specify your username and password using the /username and /password switches.
If your package is password protected you should specify the package password using the /packagepassword switch.
You might be able to omit the version ID if you're only interested in the latest version of a package, although I'm not sure so you may need to play around a bit.
Once you've got all your packages into XML files you can then search them for the table name using any standard text search tool. Grep is my personal favourite for this kind of thing, but you could just open up all the files in a text editor, or Visual Studio, and then search, or even use the standard Windows Explorer Search > Find in Files functionality.
Obviously if you have similar table names in several databases you're liable to get some false positive results, but these should be relatively easy to exclude just by opening the matching files and having a quick check.
Hope that helps.
Thanks,
Bart
April 4, 2008 at 5:45 pm
thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply