Post reply

Decrypt DTSRun package name

  • 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

  • June 27, 2005 at 10:29 pm

    #568810

    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

  • June 27, 2005 at 11:50 pm

    #568815

    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

  • June 28, 2005 at 2:51 am

    #568849

    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 /~%')

  • June 28, 2005 at 3:39 am

    #568858

    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

  • June 28, 2005 at 8:52 am

    #568951

    This utility claims to do what you want.

    DTSRunDec

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

     

  • July 5, 2005 at 11:55 pm

    #571097

    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.

  • June 6, 2006 at 3:02 am

    #641889

    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%'

     

  • January 31, 2007 at 8:27 am

    #686870

    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?

  • February 1, 2007 at 12:45 pm

    #687204

    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

  • February 2, 2007 at 2:57 am

    #687338

    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.

  • February 2, 2007 at 3:16 am

    #687341

    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!

  • February 2, 2007 at 10:59 am

    #687496

    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

  • March 27, 2007 at 11:30 pm

    #697317

    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

     

  • May 1, 2014 at 3:05 am

    #1710054

    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 15 total)

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