SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deciphering DTSRun String in Sql Server Agent Job


Deciphering DTSRun String in Sql Server Agent Job

Author
Message
Dale Cunningham
Dale Cunningham
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 134

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.


Chris Perkins
Chris Perkins
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 4
Dale,
You are a superstar.
Thanks You
usmanmohdsheriff
usmanmohdsheriff
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 7
You guys easy my job.

Thanks to all
Mike Fitzgerald-247548
Mike Fitzgerald-247548
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 451
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.
Chris Perkins
Chris Perkins
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 4
Thanks, Appreciate the help
Jessie-276453
Jessie-276453
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 131
Brilliant! Thanks a lot!
Dena Mansfield
Dena Mansfield
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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
twinchell
twinchell
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 126
Perfect. This was exactly what I needed.

Tom
Bronco
Bronco
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 55
Solved.
Thank you
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search