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 privileges?

I am trying to get a DTS package to be run from the command line with
the dtsrun utility. The DTS package is stored in the database. The user
I supply is a user in the database. I get an error stating "SQL Server
does not exist or access denied." It looks to me like the SQL Server
instance does exist because it tries to start the package. I get
"DTSRun: Executing". If I put in a server that is non-existent, I do not
get that message. I also know that my username and password are correct.

Here is output from my attempt to run dtsrun for my DTS pkg (server,
user, password change to protect my db security):

C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server
does not exist or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun: Package execution complete.

I suspect that my user I am connecting to the database with does not
have privileges to execute the DTS package. I cannot determine, from
BOL, what privs I need to grant to this user to let them execute this
package. Any ideas?

TIA,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - UnknownHi Brian.

The fact that it says:
> DTSRun: Loading...
> DTSRun: Executing...

indicates your user can run DTSRUN. What is more likely is that user cannot
connect to the servers and databases named in the package.

Try going into Query analyzer and connect to the server as that user and see
if you can connect to the database and select from some of its tables.

--
-Dick Christoph

"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:IyqxzH.BFw@.igsrsparc2.er.usgs.gov...
>I am trying to get a DTS package to be run from the command line with the
>dtsrun utility. The DTS package is stored in the database. The user I
>supply is a user in the database. I get an error stating "SQL Server does
>not exist or access denied." It looks to me like the SQL Server instance
>does exist because it tries to start the package. I get "DTSRun:
>Executing". If I put in a server that is non-existent, I do not get that
>message. I also know that my username and password are correct.
> Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
> password change to protect my db security):
> C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259
> (80004005)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 17 (11)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun: Package execution complete.
>
> I suspect that my user I am connecting to the database with does not have
> privileges to execute the DTS package. I cannot determine, from BOL, what
> privs I need to grant to this user to let them execute this package. Any
> ideas?
>
> TIA,
> Brian
>
>
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown|||To add to Dick's response, be aware that the DTS package runs on same
machine as DTSRUN. You'll get these errors if you run the package on a
client machine and have specified SQL Server name 'local' in the DTS
connection.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:IyqxzH.BFw@.igsrsparc2.er.usgs.gov...
>I am trying to get a DTS package to be run from the command line with the
>dtsrun utility. The DTS package is stored in the database. The user I
>supply is a user in the database. I get an error stating "SQL Server does
>not exist or access denied." It looks to me like the SQL Server instance
>does exist because it tries to start the package. I get "DTSRun:
>Executing". If I put in a server that is non-existent, I do not get that
>message. I also know that my username and password are correct.
> Here is output from my attempt to run dtsrun for my DTS pkg (server, user,
> password change to protect my db security):
> C:\>dtsrun /Sserver_name /Uuser /Ppass /Npkg_name
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
> DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259
> (80004005)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> Error Detail Records:
> Error: -2147467259 (80004005); Provider Error: 17 (11)
> Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does
> not exist or access denied.
> Error source: Microsoft OLE DB Provider for SQL Server
> Help file:
> Help context: 0
> DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
> DTSRun: Package execution complete.
>
> I suspect that my user I am connecting to the database with does not have
> privileges to execute the DTS package. I cannot determine, from BOL, what
> privs I need to grant to this user to let them execute this package. Any
> ideas?
>
> TIA,
> Brian
>
>
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown|||DickChristoph wrote:
> Hi Brian.
> The fact that it says:
>> DTSRun: Loading...
>> DTSRun: Executing...
> indicates your user can run DTSRUN. What is more likely is that user cannot
> connect to the servers and databases named in the package.
> Try going into Query analyzer and connect to the server as that user and see
> if you can connect to the database and select from some of its tables.

The user can connect to the database and query tables just fine with QA.
This is not a Windows user, but rather one that is authenticated in the
database.

Thanks,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||Dan Guzman wrote:
> To add to Dick's response, be aware that the DTS package runs on same
> machine as DTSRUN. You'll get these errors if you run the package on a
> client machine and have specified SQL Server name 'local' in the DTS
> connection.

I do plan on running the DTSRUN utility from a client machine, but I
have stated the SQL Server's name in the /S switch. I did not use
'local' or '.' for the server name since I know the package is not local
to the client.

Thanks,
Brian

--
================================================== =================

Brian Peasland
oracle_dba@.nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown|||Hi Brian

Can you open the package and look at the connections. Dan was suggesting
that one of these connections might be "(local)". also I would add to my
previous comment, the username specified for these connections might lack
the privileges to connect to the database.

Also if to open the package and execute it from the designer you can see
which step is crashing.

--
-Dick Christoph
"Brian Peasland" <oracle_dba@.nospam.peasland.net> wrote in message
news:Iyrtp9.25D@.igsrsparc2.er.usgs.gov...
> Dan Guzman wrote:
>> To add to Dick's response, be aware that the DTS package runs on same
>> machine as DTSRUN. You'll get these errors if you run the package on a
>> client machine and have specified SQL Server name 'local' in the DTS
>> connection.
>>
> I do plan on running the DTSRUN utility from a client machine, but I have
> stated the SQL Server's name in the /S switch. I did not use 'local' or
> '.' for the server name since I know the package is not local to the
> client.
> Thanks,
> Brian
> --
> ================================================== =================
> Brian Peasland
> oracle_dba@.nospam.peasland.net
> http://www.peasland.net
> Remove the "nospam." from the email address to email me.
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

DTSRUN is it available in SQL Server 64-bit

Hi There,
Tried to look for the DTSRUN utility in SQL server 64-bit , but wasn't
able to locate it, is it supported to run on the 64-bit server? if not , are
there any alternatives rather than using BCP or running the DTSRUN utility
from a 32-bit machine.
Thanks in advance
Asser MaanyHi
The short answer. No.
The long answer. You could install it in the WOW64 (Windows on Windows - a
64 to 32 bit Thunking layer present in 64 Bit Windows), but, it will not be
supported by Microsoft.
It is best to run EM and DTSRUN on a 32 bit server.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Asser Maany" <asser.maany@.microtech.com.eg> wrote in message
news:OCL17fVrFHA.3836@.TK2MSFTNGP12.phx.gbl...
> Hi There,
> Tried to look for the DTSRUN utility in SQL server 64-bit , but wasn't
> able to locate it, is it supported to run on the 64-bit server? if not ,
> are there any alternatives rather than using BCP or running the DTSRUN
> utility from a 32-bit machine.
> Thanks in advance
> Asser Maany
>

DTS/XML conversion utility avaialable?

I recently viewed a webcast called "Microsoft Business Intelligence Product
Architecture for ISVs" where they demostrated the DSO/XML conversion utility
and a DTS/XML conversion utility. I have used the DSO tool but could realy
use the samething for DTS packages. I seached and can seem to find a downloa
d
for it.
Anyone know anything about it or where I can get it?http://www.microsoft.com/downloads/...&DisplayLang=en
Ohjoo Kwon
"bmacatpsi" <bmacatpsi@.discussions.microsoft.com> wrote in message
news:FCABD584-B6D1-4D42-9554-6B8D0054FFD4@.microsoft.com...
> I recently viewed a webcast called "Microsoft Business Intelligence
Product
> Architecture for ISVs" where they demostrated the DSO/XML conversion
utility
> and a DTS/XML conversion utility. I have used the DSO tool but could realy
> use the samething for DTS packages. I seached and can seem to find a
download
> for it.
> Anyone know anything about it or where I can get it?|||Thanks for your prompts response,
However thats for the DSO utility and I am looking for the DTS/XML
Conversion utility.
Anyone?
"Ohjoo Kwon" wrote:

> http://www.microsoft.com/downloads/...&DisplayLang=en
> Ohjoo Kwon
>
> "bmacatpsi" <bmacatpsi@.discussions.microsoft.com> wrote in message
> news:FCABD584-B6D1-4D42-9554-6B8D0054FFD4@.microsoft.com...
> Product
> utility
> download
>
>|||You need to contact the presenter of the webcast. It is his personal
utility.
Drop me an email and I will forward accordingly.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmacatpsi" <bmacatpsi@.discussions.microsoft.com> wrote in message
news:C13EF544-E01F-4969-AEF7-9DFD9C2CEEB0@.microsoft.com...[vbcol=seagreen]
> Thanks for your prompts response,
> However thats for the DSO utility and I am looking for the DTS/XML
> Conversion utility.
> Anyone?
> "Ohjoo Kwon" wrote:
>
http://www.microsoft.com/downloads/... />
layLang=en[vbcol=seagreen]
realy[vbcol=seagreen]

DTS utility

Hello,
I am using the DTS utility available with Enterprise Manager in MSSQL server 2000. I can transfer the tables and views without any issues but when i try to transfer the stored procedures it always gives an error. I have tried transferring individual objects too but it does'nt works. The error given is "the user <username> cannot perform the following action". Any help is appreciated.
Thanks in AdvanceEasy is to script them and run at target server.|||I always liked Nigel's sig...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=1578

And what that basically means, is what your are fundamentally trying to do is not the best way to skin a cat...

You need to undestand the methodolgy of deployement...

If you describe what you are ultimatley trying to do, I'm sure we can help..|||On the lighter side Brett conveyed the message... :D
On the hardest part I feel DTS must be used for data transfer and for this sort of things use Generate Script utility.

Also one of my peer raised this may be a security issue, so convey the permissions for this user on database objects.|||Got the point guys!!! I used the generate script utility, it is easier and without any issues.
Thanks