How to Find the DTS Package Name from the Job Step GUID

Question

How to Find the DTS Package Name from the Job Step GUID

Carlos Urbina

SSCommitted

Points: 1850
More actions
May 4, 2006 at 10:30 am

#168124

Does anyone know how to find the DTS Package Name from the Job Step GUID that displays on the Job "Command" DTS Run Line, similar to the snippet below? DTSRun /~Z0xFDED6EF28936C3EEE54F6C5BDA7CE276F0....
That string has to live somewhere, because when the job executes, the correct version of the package is executed. I tried looking for just a few bytes of that string in sysdtspackages with the following query, but did not get a hit. I also googled for about an hour and was not able to find anything. (I didn't include the sysdtspackages.packagedata column because it is binary).
select * from msdb.dbo.sysdtspackages
where id like '%FDED%'
    or versionid like '%FDED%'
    or categoryid like '%FDED%'
    or owner_sid like '%FDED%'
Any help will be appreciated.

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

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

Gordon Pollokoff SSCommitted Points: 1930 More actions · Answer 1

the use of the Z option in the command line indicates that the command line is encrypted using SQL Server 2000 Encryption. That is why you cannot find the package in sysdtspackages.

If you don't have too many packages, you can schedule each and compare the newly created job to your existing job.

Gordon Pollokoff

"Wile E. is my reality, Bugs Bunny is my goal" - Chuck Jones

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 2

Thanks for the suggestion. I tried it but it seems that our system generates a new GUID each time we schedule a package. In the job comments it said that this job was executing a package by the same name, so I located that package and I scheduled it three times. Beginning/ending snippets of the original and of each result are below.

DTSRun /~Z0xFDED6EF28936C3EEE....A1E3260427B

DTSRun /~Z0xC4DCE7ED019B57146....4C85B76DC104802B178CF66BAF3

DTSRun /~Z0x10000187842D35CC6....654E31B488E

DTSRun /~Z0x829295D58724B2DEC....D93CD43F45B

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 3

If you schedule by right clicking the DTS Package you will always get the encrypted DTSRun command line.

However if you use the dtsrunui.exe tool in C:\Program Files\Microsoft SQL Server\80\Tools\Binn

Select the DTS Package

Click Advanced

Click Generate...

it will create an unencrypted version of the DTSRun command line which contains the name of the DTS Package. This can then be pasted into a SQL Job Step, for example.

Not exactly what you asked for - but it may help.

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

Carlos,

See my reply in this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=110&messageid=232301

Greg

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 5

Thanks, Greg, the answers in the suggested thread, http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=110&messageid=232301, were very enlightening. I especially appreciated Jeet's stored proc that runs a package. But LS hit it on the nose, for he said, "....take a look at the variable @description. It should state the package name that the job is executing, like this: @description = N'Execute package: XXX'".

We are interested in jobs scheduled through right-mouse clicking on the package, and from LS' suggestion we now know that SQL specifies the name of the package in those cases. Since we were attempting to parse through all the jobs converted to SQL scripts in an effort to find job dependencies, executing the jobs was out of the question.

Nevertheless Greg, your suggestion is excellent, for it had the seed of the following idea: if we logged package execution and looked at Job History, we could find the matching times, even with queries against msdb.dbo.sysdtspackagelog and msdb.dbo.sysjobshistory.