DTSRun command

Question

DTSRun command

dave battersby

Old Hand

Points: 353
More actions
April 13, 2007 at 5:58 am

#172978

I have followed this article http://www.sqlteam.com/item.asp?ItemID=19595 so that I can run a dts package from an aspx page. When running the stored procedure my job creates succsfully but fails. I have run the dts on its own without problem.
The command line of job is:
"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "(local)" /N "rm_vr_VDC101_Import" /W "0" /E
Does this look ok?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Matt Simmons SSCrazy Points: 2846 More actions · Answer 1

I remember having issues with the quotes. Sometimes you need the standard " other times I've had to use 2 single quotes together ''. They look quite similar and are very hard to distinguish when typed. Try reversing what you are using and see what happens.

Regards,
Matt

dave battersby Old Hand Points: 353 More actions · Answer 2

Ive used every combination and still no luck.

In the failed Job my command line reads DTSRun /S "(local)" /N "rm_vr_VDC101_Import" /W "0" /E

David McFarland SSChampion Points: 11815 More actions · Answer 3

If the aspx page is hosted on a different machine from the SQL Server, I don't think it's going to like "(local)" very much. Try changing that to the actual server name that the aspx host would know it as, and let us know if that works.

upalsen Old Hand Points: 381 More actions · Answer 4

why dont u use sql server to create the job by just right clicking the dts and selecting the "schedule package" option? the job will still use dtsrun with encrypted parameter ..

dave battersby Old Hand Points: 353 More actions · Answer 5

not sure if that would matter as the asp page calls the stored procedure, which sits on the same server as the dts. I toook your advice however and generated the full command using the DTSRun utility and got:

DTSRun /S "Server_name" /N "rm_vr_VDC101_Import" /G "{1F4F2CCA-2FFE-444B-8AD6-56DF05811D68}" /W "0" /E

When the sp was run however it still fails. Is there a way to actually see what failed in the job?

dave battersby Old Hand Points: 353 More actions · Answer 6

because I need the job to be created, run and deleted automatically from as asp page

Matt Simmons SSCrazy Points: 2846 More actions · Answer 7

This should fix it for you. From your ASP page call a proc that calls the DTS. This is how I did it. Below is the proc code:

DECLARE @myline as varchar(7000)

SET @myline = 'dtsrun /Sservername /Uusername /Ppassword /N"DTSName" + ''

EXEC master..xp_cmdshell @myline

Regards,
Matt

dave battersby Old Hand Points: 353 More actions · Answer 8

Do you need administrater access to run this, because I haven't, and wont be able to get this access

Matt Simmons SSCrazy Points: 2846 More actions · Answer 9

I believe so. That was my sample from Dev, where I had control, but as I recall once it was pushed to Prod the DBA's assumed control and simply edited the User and Password so that it functioned correctly.

At least here, my DBA's were willing to take that responsibility. Hope that helps.

Regards,
Matt