Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Deciphering DTSRun String in Sql Server Agent Job Expand / Collapse
Author
Message
Posted Friday, August 10, 2007 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 22, 2013 8:16 AM
Points: 11, Visits: 103

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.

 

Post #389748
Posted Thursday, November 08, 2007 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 03, 2008 9:18 PM
Points: 2, Visits: 4
Dale,
You are a superstar.
Thanks You
Post #420242
Posted Sunday, March 02, 2008 8:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 08, 2011 1:57 AM
Points: 1, Visits: 7
You guys easy my job.

Thanks to all
Post #462969
Posted Monday, March 03, 2008 8:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 10:38 AM
Points: 127, Visits: 369
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.
Post #463542
Posted Monday, March 03, 2008 9:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 03, 2008 9:18 PM
Points: 2, Visits: 4
Thanks, Appreciate the help
Post #463556
Posted Thursday, March 13, 2008 10:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:18 AM
Points: 1, Visits: 127
Brilliant! Thanks a lot!
Post #469164
Posted Wednesday, January 07, 2009 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 25, 2013 1:22 PM
Points: 4, Visits: 77
In case anyone is still looking for an answer....

http://blogs.technet.com/vipulshah/archive/2007/03/12/how-to-identify-which-dts-package-is-being-called-by-scheduled-job.aspx
Post #631828
Posted Thursday, October 15, 2009 2:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 06, 2009 11:29 AM
Points: 26, Visits: 126
Perfect. This was exactly what I needed.

Tom
Post #803772
Posted Friday, June 15, 2012 8:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 29, 2013 8:47 PM
Points: 2, Visits: 47
Solved.
Thank you
Post #1316666
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse