DTSrun.exe - application error

Please help!!
The above keep appearing when we schedule a dts to run.
We can run the dts manually as a local package and it
works, but when run through as a job, either as a schdule
or manually it fails.
Can anybody enlighten me as to why this is happening?Di Nicholls,
Could you post the full error? You might get a better response in the
.dts newsgroup.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Di Nicholls wrote:
> Please help!!
> The above keep appearing when we schedule a dts to run.
> We can run the dts manually as a local package and it
> works, but when run through as a job, either as a schdule
> or manually it fails.
> Can anybody enlighten me as to why this is happening?

dtsrun works from command prompt, but hangs from Query Analyzer

I have a dts that works from the command prompt, but hangs in Query Analyzer. Here's the code inside Query Analyzer:
exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'

From the command prompt, it's just:
dtsrun /S BFHSQL4 /N vhl_dts_14144b /E

Any ideas what's wrong? Thanks for your help!Did the dtsrun cause SQL Server hang or just Query Analyzer hang? Did you got any error message when executing the package from Query Analyzer? You need more information for troubleshooting: use SQL Profiler to respectively capture a trace when execute dtsrun to the same package from commandline and Query Analyzer.|||Thanks for your response. I guess hangs might not be the right word. There are no error messages. Query Analyzer just tries to execute the package forever. It takes less than a minute to run from the command prompt. I've let it run for 1/2 hour in Query Analyzer before cancelling the query, then it takes several minutes to cancel. Other packages run fine with the same syntax. I think the problem is with this particular package. The dts just runs an activeX script, as follows:
Function Main()
Dim fileName

fileName = "\\mypath\myExcelFile.xls"

dim excelObject
set excelObject = CreateObject("Excel.Application")

excelObject.workbooks.open fileName

excelObject.Quit
set excelObject = Nothing

Main = DTSTaskExecResult_Success
End Function

I'm not really familiar with SQL Profiler. Do you still think using it will be helpful for this scenario? Thanks again for your help.|||Turns out that there was code inside the Excel file that was trying to save a file to a path that didn't exist. I fixed the path and now everything works fine.

DTSRun Utility Wont

I have been successful at setting up three or four DTS packages to run
using the DTSrun command line utility and the Windows scheduler until
now. The latest package gives me the following error no matter what I
try.

Error: -2147217355 (80041035); Provider Error: 0 (0)
Error string: General error -2147217355 (80041035).
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 705

I googled the error but got only two hits, only one of which actually
discussed the error. That one talked about the security and ID context
of the system agent but I can't even run the package from the command
line, never mind using the Windows chron. The DTS command line I'm
using looks like this:

DTSRun /S MyServer\Instance /R DBName /N MyDTSPacakgeName /E

Variations included /U MyUserName /P MyPassWord and a couple others.
Next step would have been to use /!Y and /!C to create an encoded batch
file to use for the Windows scheduler.
Any help would be most appreciated.
RandyCan you run the DTS package from the designer (hopefully on the same
machine)? Just seeing if the error might be internal to the package.

Stu|||Runs like a champ from designer. Won't run scheduled from Enterprise
Manager, whether on a client machine or directly on the server and
won't run from a command line.|||And you can run other packages this same way? That would certainly
rule out any permissions problems, but I'm drawing a blank as to what's
going on.

Stu|||Exactly. I have one other package that runs every night but it's been
running for three or four months now. I have another package that I ran
one time last weekend using Windows scheduler again with no problem.
This third package needs to run every day from now on but I can't get
it to run from a command line which is the first step I take toward
building a batch file for scheduler.
And there are VERY few references to the error using Google, which
surprised me.

DTSrun utility security ?

Using SQL2000

I've created a DTS package and I now want to kick it off via a webpage interface. The package is realatively simple and either adds new rows to a table or updates them or both.

My question is this. If I create a batch file on the server that calls the DTS package and the Webpage is the client which executes the batch file what security context is the package going to run in.

The dtsrun batch file looks like this.
dtsrun /Sservername /Npackagename /E /Mpassword

The /E tells the package to use windows authentication.

As always, ThanksHi

A batch file will usually run in the security context of the user that kicked it off. Its worth making sure the user context has the required access to tables stored procedures etc ( read, update etc ) as well.

Be careful too if you are passing user crededtials from a web site on the internet to the SQL box - its an accident waiting to happen unless its an encrypted link ( people can "sniff" network traffic to capture the username & password). Alternatively, you could have a web page that updates a field in a table and have a job on the server that scans the table for a change in value, then kicks off a DTS package. This stops credentials being passed around the network.

Cheers,

SG.|||Great information and very unique way of getting around the username and password thing. I was expecting that I would have to encrypt but with your suggestion I'm thinking I'll try the table update you suggested.

I was fairly sure about the security context but I wanted to double check.

Thanks for the reply

Mike|||Howdy

Glad I could help.

Cheers

SG

DTSRUN utility

I would like to run a dts package from the command-line and I am trying to integrate a process exit code in my DTS package which I can intercept in my command-line. Is this possible. Because the next step in my batch file is dependent on the outcome of my DTS package.

Let me know if you have already tackeled that problem.I just did this test in SQL 7.0.

I created a simple package that just had 1 Activex Script Task:

Function Main()
Main = DTSTaskExecResult_Failure
End Function

From the command-line I ran DTSRUN utility, after which I checked the status of errorlevel, the DOS error status variable and it was set to 1, error. A value of 0 is success.

c:\ dtsrun /S nike /E /Ptest /M
c:\ echo %errorlevel%|||So simple and yet so effective. THX

dtsrun to start a package - cannot find xp_cmdshell

Newbie here.

In my database I'm needing to automate some data imports. I have the
import set up as a DTS package and it works wonderfully. But I'm
having trouble kicking it off as a stored procedure, or even from the
Query Analyzer. I used dtsrunui to get a proper connection string, but
when I enter

EXEC xp_cmdshell 'dtsrun /S "(local)" /N "MyPackage" /A
"KeyNum":"19"="19687627" /W "0" /E'

I get an error that says "Could not find stored procedure
'xp_cmdshell'". xp_cmdshell is indeed there, under Master, Extended
Procedures. I tried calling it dbo.xp_cmdshell, but that didn't help.
I'm guessing that I need to point the command to the location of the
SP, but I have no idea how to do that. Anyone willing to shed a little
light would get my eternal gratitude. :)

Thanks in Advance, maddmanuse master first or add master. to the procedure name. xp's don't operate
like sp's where you can call them from any database.
kevin Ruggles

"Maddman" <maddman_75@.yahoo.com> wrote in message
news:1102626201.340243.92890@.f14g2000cwb.googlegro ups.com...
> Newbie here.
> In my database I'm needing to automate some data imports. I have the
> import set up as a DTS package and it works wonderfully. But I'm
> having trouble kicking it off as a stored procedure, or even from the
> Query Analyzer. I used dtsrunui to get a proper connection string, but
> when I enter
> EXEC xp_cmdshell 'dtsrun /S "(local)" /N "MyPackage" /A
> "KeyNum":"19"="19687627" /W "0" /E'
> I get an error that says "Could not find stored procedure
> 'xp_cmdshell'". xp_cmdshell is indeed there, under Master, Extended
> Procedures. I tried calling it dbo.xp_cmdshell, but that didn't help.
> I'm guessing that I need to point the command to the location of the
> SP, but I have no idea how to do that. Anyone willing to shed a little
> light would get my eternal gratitude. :)
> Thanks in Advance, maddman|||That did the trick. Thanks!

DTSRun string

I have a job that I moved from one server to another via generate sql script.
This job contains approximately 25 separate steps, where each step
calls/executes a DTS package using DTSRun.
The job is failing and I suspect that it may be attempting to call/execute
the DTS package on the previous server. In each step the DTSRun command is
used, followed by a long alphanumeric string, such as the following:
DTSRun
/~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
How can I ensure the DTS package being called is the same DTS package on the
same server as the job? Is the long alphanumeric string an ID of some sort
that I can compare?
Message posted via http://www.droptable.com
Replace the command with
DTSrun /n JobName /S Servername /e
Look at the DTSrun command for an explanation of the switches.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56acebf480d1d@.uwe...
>I have a job that I moved from one server to another via generate sql
>script.
> This job contains approximately 25 separate steps, where each step
> calls/executes a DTS package using DTSRun.
> The job is failing and I suspect that it may be attempting to call/execute
> the DTS package on the previous server. In each step the DTSRun command is
> used, followed by a long alphanumeric string, such as the following:
> DTSRun
> /~Z0xF8E8465A4E87F65E80A594D219039F3DB02F031FE0290B C36746B34119246D0BA612A999760D84E91CD170332E5D7CB5 90B764064631A1951589D6F310C930314F0F68864FCADAF023 2E3454D07DE9BF7DC458BE8D2E4EA0C3A3FBE361B352C4319E D868A5232FAEDCA9031A0BB43FBFF026E3B668A032B1E8316B
> How can I ensure the DTS package being called is the same DTS package on
> the
> same server as the job? Is the long alphanumeric string an ID of some sort
> that I can compare?
>
> --
> Message posted via http://www.droptable.com
|||So what you are telling me is there the long string does not refer to an ID
of some sort?
Walt Mallon wrote:[vbcol=seagreen]
>Replace the command with
>DTSrun /n JobName /S Servername /e
>Look at the DTSrun command for an explanation of the switches.
>Walter
>[quoted text clipped - 13 lines]
Message posted via http://www.droptable.com
|||The encrypted command line has the server (either as local
or by name), package, login info etc so yes if you just
copied the job from another server, it's could likely be
pointing to the wrong server.
-Sue
On Mon, 31 Oct 2005 21:55:56 GMT, "Robert R via
droptable.com" <u3288@.uwe> wrote:
[vbcol=seagreen]
>So what you are telling me is there the long string does not refer to an ID
>of some sort?
>Walt Mallon wrote:
|||It does but you can't see it. You get that string by scheduling the job via
the DTS designer. It's encrypted because you can have database login
information in the string. Alternatively, you can specify package name,
server, authentication, etc. using the DTSRun command by typing up the
command yourself. That way you can ensure what server the package runs on.
I believe Books on Line covers the DTSRun command.
Walter
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:56ae01531ee3b@.uwe...
> So what you are telling me is there the long string does not refer to an
> ID
> of some sort?
> Walt Mallon wrote:
>
> --
> Message posted via http://www.droptable.com
|||Thanks Walt. That is what I needed to know, i.e., the string generated when
scheduling the package via the DTS designer is an encrypted string, not an ID
of some object.
Walt Mallon wrote:[vbcol=seagreen]
>It does but you can't see it. You get that string by scheduling the job via
>the DTS designer. It's encrypted because you can have database login
>information in the string. Alternatively, you can specify package name,
>server, authentication, etc. using the DTSRun command by typing up the
>command yourself. That way you can ensure what server the package runs on.
>I believe Books on Line covers the DTSRun command.
>Walter
>[quoted text clipped - 14 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1