Post reply

Deciphering DTSRun String in Sql Server Agent Job

  • February 18, 2005 at 12:42 pm

    #65989

    I am trying to bring up our DTS and SQL Server Agent Jobs Sorbox compliant.  I am trying to decipher the DTSRun~/ (Long Numeric String) in each step within each job.  There is no easy way.

    I have looked inside msdb but it also has this long numeric string.  Does anyone know how to decipher this long string or where in msdb it exist.  Thanks.

  • February 21, 2005 at 2:04 am

    #543269

    I'd like to know this too, as I found this thread when searching for a solution to the exact same problem.

  • February 23, 2005 at 10:07 am

    #543740

    In the DTS GUI, the package and version GUIDs are in the package properties dialog box.  You can find the package name in msdb..sysdtspackages like this:

    SELECT     name

    FROM         sysdtspackages

    WHERE     (id = '{55521ACB-1643-45CD-B882-A6AB20FF59F8}') OR

                          (versionid = '{67B35377-A60B-4EC8-A240-184CFD12834E}')

    Fields:

    name is the package name

    id is the package GUID

    versionid is the version GUID

    If you have the Wrox SQL Server 2000 DTS book, see page 69 to read about the switches.

    [font="Courier New"]ZenDada[/font]

  • February 23, 2005 at 12:48 pm

    #543756

    Thanks Jules.

    I have gone through this process and able to look at the GUIID and Version ID. I am needing to decipher the DTS RUN Cmd inside the Server Agent - Jobs. 

    There is a tool that I am trying to use - DecDTSRun.c.  It is written in C and I do not have much experience in C.  Are you familiar with this tool.

    Ernest

  • February 24, 2005 at 9:40 am

    #543953

    No, sorry, not familiar with the tool. 

    Do you simply need to query sysjobsteps?  The command field has the dts run command in it.

    [font="Courier New"]ZenDada[/font]

  • February 24, 2005 at 11:01 am

    #543979

    I am needing to decrypt the string (DTSPackage) after the dtsrun command so that I am able to see which package is attached to each job.  Sort of...reverse engineer.  My other alternative is to recreate the jobs; however, this process is much to tediuos.

    Up to this point, I haven't found a method to decrypt this string.

    Ernest

  • February 24, 2005 at 2:37 pm

    #544026

    In the future, when you schedule a dts package as a job - say using dtsrunui, simply don't check "encrypt" when you generate the command string.  Or create the string manually in EM.  Don't right click on the package to schedule - that always creates an encrypted command string.  If that is the reason that you have encrypted command strings in the first place, then you may be in luck - because the name of the dts package being executed will be in the name field of sysjobsteps unless someone manually changed the name of the step.  But that's a pretty big leap of faith. I don't think you should be able to decrypt the string once encrypted.  I guess that's kind of the point, eh?  Given that that is the case, you can't prove that a dtsrun command really does what you say it does unless you re-write it yourself without encryption.  Sure it's a hassle but it's probably the right thing to do.

    [font="Courier New"]ZenDada[/font]

  • March 4, 2005 at 2:43 pm

    #545585

    You shouldn't be able to, but you can...

    Of course compiling C programs will drive a non-c programmer crazy.  They distribute their programs uncompiled just to piss everyone off.

     

  • March 17, 2005 at 12:55 pm

    #547656

    Hi, I have the same problem.

    Did you finally find a solution for this ??

    If not, where did you find the DecDTSRun.c ??

     

    Thank's

    Sylvio

  • March 17, 2005 at 2:19 pm

    #547673

    I found the C at the following link.  I hope that you have better luck.  I am still working on this item.

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

    Ernest

  • August 10, 2007 at 10:14 am

    #726098

    I have seen where DBAs would right click the DTS Package (SQL 2000) and schedule a job.  If you schedule a package to run that way, the SQL Job it creates will have an encrypted name for DTS package in SQL job step - similar to what you see below: 

    DTSRun /~Z0x5B431B25562BD74F4CB941E51326350F8A91C9437DA8D73EBD1C2DE9619DD5E319023043855FECBEE1C4212EC9C8F723E63AD6FEED07C6E6C271FA081A7A944807AF9338BFBD84172F0918E1ABADF33574D3102F3EB5FA5B853FF6A24B366B006E9991A21B8C69B71750032AE391DF759D0B3B09EEAB94B687E05E66CE7307C6304204

    To avoid this, you can simply create a  SQL Job and choose CmdExec as a step type and type following in your step:

     DTSRun /N"your package name" /S<servername> /E<for trusted connection>

     OR

    DTSRun /N"Your package Name" /S<ServerName> /U<UserName> /P<Password>   --- for SQL authentication

    That way you will have meaningful package name in your job.  But in case for some reason, you do have those Jobs with weird encrypted DTS package name, here is how you can identify which package is being called by that step (this is something I found from SQL Forum):

    1. Copy the DTSRUN line (everything including the DTSRUN)

    2. Open a Windows Command Line window

    3. Paste the DTSRUN line into the CMD window.

    4. To the end of the line, add /!X /!C

    /!X = do not execute /!C = copy results onto Windows Clipboard

    5. Run the command

    6. Open Notepad

    7. Click Edit>Paste

    that will paste the actual command into Notepad and it will show the name of the package.

     

  • November 8, 2007 at 12:52 pm

    #749618

    Dale,

    You are a superstar.

    Thanks You

  • March 2, 2008 at 8:13 pm

    #784284

    You guys easy my job.

    Thanks to all

  • March 3, 2008 at 8:30 pm

    #784762

    Just add /!X /!C after the DTS command.

    DTSRun /~Z0x7078C2EC98.... /!X /!C

    That will decrypt the string and copy if to the clipboard.

    Then paste it somewhere and you will see something like this:

    DTSRun /S "servername" /N "DTS_pkg_name" /E /!X /!C

    If you don't want to do this at a DOS prompt wrap the whole thing in xp_cmdshell and run it from QA:

    xp_cmdshell 'DTSRun /~Z0x7078C2EC98.... /!X /!C'

    You will get some output like the following:

    DTSRun: Loading...

    DTSRun: Executing...

    NULL

    Then do a paste ( ) in your query window and you will get the results as above.

  • March 3, 2008 at 9:19 pm

    #784774

    Thanks, Appreciate the help

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

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