How do I execute a DTS package from a stored procedure?

ASKER

so then I have to make it a saved-to-disk dts file and run it with DTSrun.exe? I'm a little unclear on your code - what are the two periods between master and xp_cmdshell?

JP

one more:

http://www.sqldts.com/default.aspx?210

about MS SQL SERVER MS-DOS utility - dtsrun.exe see BOL

see sample:
-------------
Create procedure usp_Younameit
as
---To execute a DTS package saved in the SQL Server msdb database, use:

exec master.dbo.xp_cmdshell 'dtsrun /Sserver_name /E /Npackage_name'
^ ^ ^
DB | owner of db's object
-----------------

C:\WINNT>dtsrun.exe /?
Usage: dtsrun /option [value] [/option [value]] ...
Options ('/?' shows this screen; '-' May be substituted for '/'):

Package retrieval:
/~S Server Name
/~U User Name
/~P Password
/E <Use trusted connection instead of /U /P>
/~N Package Name
/~M Package Password
/~G Package Guid String
/~V Package Version Guid String
/~F Structured Storage UNC filename (overwritten if /S also specified)
/~R Repository Database Name <uses default if blank; loads package from repo
sitory database>

Package operation (overrides stored Package settings):
/~A Global Variable Name:typeid=Value <may quote entire string (including na
me:typeid)>
/~L Log file name
/~W Write Completion Status to Windows Event Log <True or False>

DTSRun action (default is to execute Package):
/!X <Do not execute; retrieves Package to /F filename>
/!D <Do not execute; drop package from SQL Server (cannot drop from Storage
File)>
/!Y <Do not execute; output encrypted command line>
/!C <Copies command line to Windows clipboard (may be used with /!Y and /!X)
>

Notes:
~ is optional; if present, the parameter is hex text of encrypted value (0x3
13233...)
Whitespace between command switch and value is optional
Embedded whitespace in values must be embedded in double-quotes
If an option is specified multiple times, the last one wins (except multiple
/A)

ASKER

is that /S server_name /E /N package_name or /Sserver_name /E /Npackage_name?

JP

no spaces...

/Sserver_name /E /Npackage_name

ASKER

thx

ASKER

OK, so I have to do something (that that first link above showed) that exports my DTS packages, or is that calling them as they are in Enterprise Manager?

JP

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Sweetness - I'm gonna go see what I come up with

My first link was just sample how to wrap dtsrun in sp!
There is no need for your question to export (unless you'd like to do it)

how Create DTS package:
http://www.devguru.com/features/tutorials/DTS/DTS1.html
http://www.sys-con.com/coldfusion/articleprint.cfm?id=657

ASKER

ok I have:

database called 4Randy, and the stored procedure is called 4Randy in that database.

I tried this in query analyzer:

exec master.dbo.xp_cmdshell 'dtsrun /S4Randy /E /N4Randy'

and got this (After a long pause) in an output windows box:

-------------
DTSRun: Loading...

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

NULL
----------------

???
JP

ASKER

I'm sorry, the DTS package is also called 4Randy, not the stored procedure.

JP

Is your Query Analyzer session connected to 4Randy?

ASKER

Yes, or I can have a

use [4Randy]

if I need.

JP

Can you get any query to run against the 4Randy server? Seems odd that you get a SQL server does not exist or access denied error if you are actually connected to the server in question...

ASKER

One thing that throws off query analyzer is the 4 in 4Randy - could the same be true on the command line for DTSRun.exe? can I try something like:

/S"4Randy" /E /N"4Randy"?

JP

ASKER

well I truncated the tables necessary to receive the DTS'd in data without a problem.

JP

ASKER

Anyone still around?

JP

E- it is for trusted connection - what connection do you use?
--try /U User Name /P Password

BTW: in EM if you right click your DTS -> Execute-> post result of execution

ASKER

I can try that... but I always use the integrated windows security, which I'd imagine is the same as E? if not, nothing wrong with sa.

I'll get the results of execute DTS on here in just a moment.

JP

ASKER

Your suspicions were right, I've got some problems in the DTS packages I hadn't noticed before. I'll get back to ya.
JP

ASKER

My final issue that I can see is that the DTS package pulls a file from my C drive in a certain directory. When I right-click/execute it in enterprise manager, no problem, but try to call it from query analyzer and no-go. I think if I redo the DTS packages using a network path to the file, maybe it'll work.

JP

>network path to the file - it is good idea - in DTS design - you see your PC drives but sql server...

More than likely, you would be better off having the source file that currently resides on your PC on a network share on a file server. If your hard drive crashes...what is the recovery plan?

Also, I would use UNC over a mapped drive every time...better results.

ASKER

Backup plan:

Well this program will be a one-button pusher deployed to a coworker to take two text database outputs and combine them on one field and reoutput it in a text file. Should his machine crash, my development machine is backup - should mine crash, I have redundant hard drives. Should that also fail, we have nightly backups :)

JP

ASKER

Well, I've used the UNC to the file in the DTS package. I've also changed it from integrated security to the sa user with correct password, but I still get the same results no matter what I'm doing. Any other ideas?

JP

what error?: when you are trying execute DTS pack from EM?

ASKER

DTS from EM works, DTS called from Query Analyzer doesn't (or command prompt on server). Error is listed above, but here again:

-------------
DTSRun: Loading...

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

NULL
----------------

I get this each time I try to run one of the DTS packages this way. I've tried setting them up using integrated security and SQL Server user/pass, same result. I've changed from a hard-path to UNC path for where to retrieve the file it DTS's in...

Let me go into more detail in case I haven't mentioned something important:

There will be a directory on my harddrive called 4Randy (c:\4Randy)
In there will be placed two files, status.txt and status-1.txt
At this point I have 3 empty tables in my 4Randy database on the server, which is not the same as my computer.
These tables are status, status-1 and final.
I run DTS package 1 which pulls status.txt from my harddrive into the status database on the server
I run DTS package 2 which pulls status-1.txt from my harddrive into the status-1 database on the server
I run the 4Randy stored procedure which combines these two tables using one common field into the final database
I then run the final DTS package that write the contents of the final database on the server back to my harddrive as Results.txt

Maybe there's something in this process of two different machines I'm missing?

JP

can you run your dtsrun... from MS-DOS?

you can generate dtsrun string with dtsrunui.exe utility:

Start->RUN->[type ->]dtsrunui.exe-> in general tab select your pack->click advanced near ...
click generate -> copy it -> inside xp_cmdshel ' ....' -> run and see..

ASKER

I did that, and when I run it, I get something different, but still with an error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from status-1 to [4Randy].[dbo].[status-1] Step
DTSRun OnError: Copy Data from status-1 to [4Randy].[dbo].[status-1] Step, Error = -2147467259 (80004005)
Error string: Error opening datafile: Access is denied.

Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0

Error Detail Records:

Error: 5 (5); Provider Error: 5 (5)
Error string: Error opening datafile: Access is denied.

Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0

DTSRun OnFinish: Copy Data from status-1 to [4Randy].[dbo].[status-1] Step
DTSRun: Package execution complete.
NULL

JP

was your file in use during pack execution?
can you type not UNC path to your file - e.g. d:\ and try again?

also see:
http://sqlteam.com/Forums/topic.asp?TOPIC_ID=13645
https://www.experts-exchange.com/questions/10807322/DTS-Text-File-Access-Denied.html
http://dbforums.com/t441699.html

ASKER

I've tried with UNC or with direct path - same results

JP

ASKER

One thing of note - it does work from the DOS prompt on the server now...

JP

ASKER

So if I understand right, that means the DTS package is correctly able to, from the server, grab the file from my machine through the UNC path, and make it work. So why then, when called from xp_cmdshell, does it not work?

JP

plan B`:copy from DOS working code in text file -> rename .txt file in BAT - create batch file

then run:
exec master.dbo.xp_cmdshell 'C:\yourbachfile.bat'

ASKER

Very good idea, but it didn't work :/ I wonder what's going on? If I run the bat file from the server machine, it does fine. If I run it using cmdShell in Query Analyzer, errors.... what gives?

JP

ASKER

Has anyone ever had something like this to work? Looking back, even on some of the links I was given above to similar questions, none of those people every found a solution.

JP

ASKER

Are there some kind of permissions in SQL Server that may need to be set? Any certain user maybe? An in-built user? It's gotta be something... Does Query Analyzer, when calling something like that, have some kind of automatic unseen username?

JP

First: make sure MSSQLSERVER service and, very important, sqlserveragent service are running as member of local admin group

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Yeah, it's checked. I'm having one of my coworkers who knows a little more about the admin side of Sql Server look at it in a bit and see if we can get that straightened out. I'll get back to you. And thanks!

JP

ASKER

We're messing with it, but no luck yet :( Any other pointers about the permissions/etc?

JP

try:

https://www.experts-exchange.com/questions/21298210/SQL-2000-Reset-Proxy-Server-Specified-user-can-not-login.html?query=xp_sqlagent_proxy_account++EugeneZ&topics=42

one more example:
https://www.experts-exchange.com/questions/21132476/Proxy-Agent-does-not-seem-be-work-for-Non-sysadmin-users.html?query=xp_sqlagent_proxy_account++EugeneZ+dts&topics=42

ASKER

I have a little problem in my UNC - it wanted //mycomputer/sharedfolder/file, I was giving it //mycomputer/c$/sharedfolder/file - dumb mistake. It all works fine now! Thank you all so much! I'll do my best to split fairly.

JP