Decrypt DTSRun package name

Question

Post reply

Decrypt DTSRun package name

don farrell

Mr or Mrs. 500

Points: 575
More actions
June 27, 2005 at 7:13 am

#109917

Hi all,
Does anyone know how to decrypt the name of a dts package from within a sql job. As in I have a job that runs a dts packase as step 1 ...the step is DTSRun /~Z0x859DExxxxxxxxxxx
I need to know what package 0x859DExxxxxxx relates to ?
Any help most appreciated ?
Regards,
Don

Viewing 15 posts - 1 through 15 (of 15 total)

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

Jamie Christian SSC Enthusiast Points: 125 More actions · Answer 1

Don, I am not sure I understand the Question but does this help?

-- ===================================================

PRINT ''

PRINT '+-----------------------------------------------------------------------------+'

PRINT '¦ DTS Package Details'

PRINT '+-----------------------------------------------------------------------------+'

PRINT ''

-- ===================================================================================

-- ===[ DTS Package Details ]=========================================================

--

-- Purpose : Lists in Detail DTS Package Names, IDs, Decriptions and Creation Dates

--

-- ===================================================================================

SET NOCOUNT ON

BEGIN

CREATE TABLE #DTS_Packages

(

[Package Name] VARCHAR(60) NOT NULL,

[Package ID] VARCHAR(40) NOT NULL,

[Description] VARCHAR(100) NOT NULL,

[LastDate] DATETIME,

[Owner] VARCHAR(60)

)

INSERT #DTS_Packages

SELECT name, id, description, MAX(createdate) AS LastDate, owner

FROM msdb..sysdtspackages

GROUP BY name, id, description, owner

SELECT [Package Name], [Package ID], [Description], [LastDate], [Owner] FROM #DTS_Packages

DROP TABLE #DTS_Packages

END

SET NOCOUNT OFF

GO

Hope so

philcart SSC-Forever Points: 47794 More actions · Answer 2

You can't decrypt the DTSRUN command line. It's a combination of package name, package guid, server connection details and any necessary parameters.

You cuold try running SQL Profiler to see what the package is doing.

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

Jamie Christian SSC Enthusiast Points: 125 More actions · Answer 3

I am Being Stubborn here

using MSDB

SELECT step_name AS Step_Name, job_id, step_id, subsystem, command, database_name, server

FROM sysjobsteps

WHERE (command LIKE N'DTSRUN /~%')

James Horsley Hall of Fame Points: 3196 More actions · Answer 4

You probably can't decrypt but the chances are you can find which package it is by running it - many options really - it depends hwo many DTS jobs you have got setup but my guess is you will be able to isolate a number as "not likely" then in the "likely candidates" you could ensure logging is enabled and check the DTS exec logs after running scheduled task to see which one ran - or you could just add and ActiveX task that e.g. msgbox's you the name of the package and then run the commandline directly (not scheduled) so you see the msgbox

James Horsley
Workflow Consulting Limited

webooth SSCommitted Points: 1550 More actions · Answer 5

This utility claims to do what you want.

DTSRunDec

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26

hot2use SSCrazy Points: 2915 More actions · Answer 6

Hi Jamie

Your script does list the internal ID, but not the ID used in the Jobs section of EM.

Regards

hot2use

__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.

HoundDog Valued Member Points: 64 More actions · Answer 7

James Christian's answer works for me. Expanding slightly on it to bring back one or two more fields here's what I'm using :

/*This command can be used to determine which DTS package a job is running where the job lists the DTS package in the format DTSRun /~xxxxxxxxxxxxxxxxxxxxxx. */

USE msdb

GO

SELECT sj.name as 'JobName', sjs.step_name AS 'StepName', sjs.job_id,

sjs.step_id, sjs.subsystem, sjs.command, sj.date_created,

sj.originating_server, --name of the server from which the job came.

sjs.database_name --this will be NULL if subsystem is not T-SQL

FROM sysjobsteps sjs INNER JOIN sysjobs sj

ON sjs.job_id=sj.job_id

WHERE sjs.command LIKE N'DTSRun /~Z0xEEF6B66CBA75D66CBD%'

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 8

Jamie's first script gives the package names from sysdtspackages. the second script gives the jobs and step names. However, I cannot see the relationship. Please explain further. We cannot assume that the step name is the package name, although I always do that. How can we, knowing the internal ID, link it back to a package name?

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

Neither of the scripts will help unless the package name is used in the job step name. The original question was how to decrypt an encrypted DTSRUN command and the only way I've found to do that is by using the DTSrundec tool mentioned above. I've found a compiled version here:

http://maiux.com/dr/node/21 and after unzipping it I was able to run it at a cmd prompt and decrypt an encrypted DTSRUN command.

Greg

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 10

It is possible to decrypt the name as I once found a utility written by a Russian dude which did it. It was a bit scrappy and errored but it did return the name correctly. Sadly I've lost it but I'll see if I can't hunt it down again and I'll post the link here if so.

Steve Pettifer SSC Eights! Points: 915 More actions · Answer 11

Further to my last post, whils searching for DtsRunDec (the decoder app), I found this thread on sqlforums.com. One chap (jamespua, near bottom of page) says that if you use the DTSRun utility but add the switches /!X /!C at the end, the utility will decode the name for you (without executing the package) and put the decrypted run string into the cliboard which you can then paste out at will. Having tried it, it works perfectly.

Example:

DTSRun /~Z0xDECE772DF6021A7ED260B31A2A37454F9614F6426C26B75B35481216F12946BB2ED19DC1E116ECD0C39E279544DAEE48E088FCE1CD35D0A1A99E11530ED82E9E97FF95CD4ECB7AD5D56F66CA8CC060E3E08B99BF3478B3DF4ADFD1912C097B77C9C2C44419F78FC5C7B6E8A269A9840E5387B48AB10106F2AECEDBE59C1DB35A7811F81D04ABF01F5E67C148778028B165D51BDF88DEE3CBF8F7582B6CA554D826B9BB /!X /!C

Gives:

DTSRun /S "<server>\<instance>" /U "<uid>" /P "<pwd>" /N "<package name>" /!X /!C

Obviously, names have been changed to protect the innocent!! Top marks to jamespua on sqlforums for such a handy tip. The full documentation for DTSRun and it's switches is here.

Enjoy!

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

No kidding?! I tried using the arguments also and it does work. Who knew that was right under our noses

all this time?

Thanks!

Greg

akanksha chaturvedi SSC Journeyman Points: 77 More actions · Answer 13

Thanks for the Post Greg

Can you please send /attatch the complied tool as was unable to find the same on site address mention above.

Regards

Aks

Jake Shelton Hall of Fame Points: 3109 More actions · Answer 14

Mods - I KNOW it's an old thread, but I'm sure it's writeable for a reason....

Just to add, after having run DTSRUn with the above switches, you may just see the messages

Loading...

Executing...

This is NOT actually executing - I just checked the history.

To bring up a command containing the package name, just right click inside the CMD box.

Cheers guys!!

Jake