Share via

How to identify which DTS Package is being called by scheduled job

  • Article

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.

Comments

  • Anonymous
    January 01, 2003
    PingBack from http://www.keyongtech.com/2089266-how-to-identify-a-dts

  • Anonymous
    June 25, 2013
    Brilliant.... I've just spent ages trying to work out why my scheduled job was running on the wrong server. Great article, very helpful :)

  • Anonymous
    May 20, 2014
    I'm typing this command into a DOS / Command window on the SQL Server 2012 in question, and I get the message back " 'DTSRUN' is not recognized as an internal or external command, operable program or batch file".
    Any ideas, please? Thanks in advance!

  • Anonymous
    June 06, 2014
    DTSRUN is not supported on SQL 2014

  • Anonymous
    August 13, 2014
    That did not work. There is nothing in the clipboard to paste. /!Y /!C outputs the same encrypted string to the console. /!X /!C copies nothing to the clipboard

  • Anonymous
    January 21, 2015
    Worked beautifully!! This saved us in a really tight troubleshooting situation. Thank you for posting this.

  • Anonymous
    February 27, 2015
    Loved it, I've been looking for long time. Glad I found this. Works like charm and I can see which DTS actually I am running in SQL 2000 SQL Job ( I know, you are laughing that we still have some SQL 2000 servers) :)

    Thanks much!!

  • Anonymous
    July 15, 2015
    Anyone have a solution when the DTS packages are on a production SQL server that we aren't allowed to remote into in order to paste the results into its Notepad after running them via SQL Agent using the /!X /!C options?

  • Anonymous
    November 18, 2015
    Awesome.. it works perfect! Thanks.