Decrypt DTSRun package name

  • don farrell

    Mr or Mrs. 500

    Points: 515

    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

  • Jamie Christian

    SSC Enthusiast

    Points: 125

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

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

    PRINT ''

    PRINT ''

    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: 47713

    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

    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

    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

  • webooth

    SSCommitted

    Points: 1500

    This utility claims to do what you want.

    DTSRunDec

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

     

  • hot2use

    SSCrazy

    Points: 2915

    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

    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: 1790

    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

    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

    Greg

  • Steve Pettifer

    SSC Eights!

    Points: 915

    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

    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

    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

    Greg

  • akanksha chaturvedi

    SSC Journeyman

    Points: 77

    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

    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

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

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