Find a DTS package name from the guid

Question

Find a DTS package name from the guid

Chris Poole

Mr or Mrs. 500

Points: 555
More actions
September 10, 2003 at 8:59 am

#160667

Is it possiple to find the name of the DTS package having only the DTS guid string.
For instance DTSRun /~Z0xFAF2964C6EAA85C98790728A150DFE8851FB03A41336CB18C6D68AD5FC95B847BAEEBA2BAF836F7EE292EEB8808EC749911D1AB32065E0301F7AB1BC5FC0934CEB37722036239AFC029ADEC334CF3A806D5A1BF4CD36D262639371BC0E2FDC4698F86691CB5F7ABEC5989D04513B4FEA3D6E59FC7F93E0566E73F5414F6C282FF74A8A0F4F9BF2CF25FAE4F93404E3151A8883E2F2E1DBDCA97E6986C2BE4F37EF56A4FA7F8C06C8ECDD238252F756991B54E1B2ECD3913F472524
If what calls this, a job step, did not reference the DTS package in its name how would you find it among 50 or so packages?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

A tilde (~) character after the forward slash (for example, /~Z) indicates that the parameter value is encrypted and what follows is the hexadecimal text of the encrypted value. No idea how to get back the package name.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

The package id is stored in the sysdtspackages table in MSDB. You can query the table to get the package name. I'm not sure how encryption of the command line and parameters (indicated by ~Z) affects that query.

Greg

Chris Poole Mr or Mrs. 500 Points: 555 More actions · Answer 3

Thanks for the input. I can take the version id off of the package properties and trace the name that way. But if a job fails at 2:00am, I don't have the time to search through 50 ids one at a time in the sysdtspackages. I inherited a system where the developers/dba's did not use dtsrun and saw no reason to relate the name of the DTS package to the job step or vice versa. Every

once and a while I try to make a pass through them and change what I can match over to a dtsrun, but real life gets in the way and I don't get irritated enough until the next time a job fails.

Sometimes MicroSoft's logic escapes me. Why encrypt this information????

philcart SSC-Forever Points: 47796 More actions · Answer 4

You could use DTSRUNUI, or the command line options for DTSRUN, to generate the encrypted string for you. That might help in the matching.

The encryption is useful when you are using SQL security and need to hide the login details.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

Edited by - phillcart on 09/10/2003 5:21:34 PM

--------------------
Colt 45 - the original point and click interface

YuanQiang Mr or Mrs. 500 Points: 520 More actions · Answer 5

You can right click one of the DTS packages you have and schedule the package. This will add a job under Jobs folder in Enterprise Manager. You then can match the 'DTSRun...' in the newly added job to the one you have.

It is time consuming, but once you have a list of what pachages any jobs run, you are fine.

A little tip: the encrypted script you have here is more likely generated from a SQL7 Enterprise Manager, not SQL2000. The encrypted scripts generated for the same DTSPackage are different.

Chris Poole Mr or Mrs. 500 Points: 555 More actions · Answer 6

Thanks. That is basically what I do now. I was just wondering if someone else had found a quicker way of doing it.