asked on

Execute DTS package from stored procedure

I tried to execute a DTS package from a stored procedure. Here is my code

CREATE PROCEDURE [RunDTS] AS
exec master..xp_cmdshell 'dtsrun /S COCHISE5 /U vnguyen/P sqlserver/ N DTS_Outbox'

it seems correct to me but when I execute the stored procedure from Query Analyzer, I received an error output
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DTSRun: Must specify a package name or guid or version guid, or a source filename.

DTSRun: Invalid command options

Usage: dtsrun /option [value] [/option [value]] ...

Options ('/?' shows this screen; '-' May be substituted for '/'):

Package retrieval:
.....

Please help. Thanks. Van Nguyen

arbert

have you tried to put a space between sqlserver and /N ?

arbert

Actually, that whole string is a little off:

'dtsrun /S COCHISE5 /U vnguyen /P sqlserver /N DTS_Outbox'

CochiseCounty

ASKER

Thanks arbert, seems like it is working with the code, but now the problem I have is the security/permission, this is what I got...

Error string: Error opening datafile: Access is denied.

Please help

BillAn1

Only thing that looks out of place is your spaces - there should be a space between each value and the next /, and no space to the letter i.e.

CREATE PROCEDURE [RunDTS] AS
exec master..xp_cmdshell 'dtsrun /S COCHISE5 /U vnguyen /P sqlserver /N DTS_Outbox'

assuming you have such a DTS package called DTS_Outbox

CochiseCounty

ASKER

Of course I have the DTS package called DTS_Outbox. That package is used to copy data from a SQL table to a text file. I am not sure how to set permission for that text file, the text file is not on the web server.

arbert

Did you use a drive letter in your DTS package for the filename? You should use UNC paths to the file. Also, like BillAn1 suggested, you need to make sure you have right to the file (wherever it is). If it's on a different server, you need to make sure the user/password you use has access to that other server. Also, if you end up scheduling this DTS package, you need to make sure the SQLAgent account has rights to the files/resources too.

Brett

SoftEng007

verify your server COCHISE5 has permissions to the files your working with.

SoftEng007

wow arbert you write faster than I can think !

arbert

lol....No, I type faster than I think (gets me in trouble all the time)!!! I think most people think faster than I type and think :)

CochiseCounty

ASKER

If there is something wrong with the permission, how come when I right click the package and Execute, it works fine. Thanks again

Ken Selvia

You can also start a DTS package from T-SQL using sp_oacreate without using xp_cmdshell;

DECLARE @hr INT
DECLARE @oPKG INT
EXEC @hr = sp_OACreate 'DTS.Package', @oPKG OUT
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'LoadFromSQLServer("(local)", "", "", 256, , , , "DTS_Outbox'")', NULL
IF @hr = 0
EXEC @hr = sp_OAMethod @oPKG, 'Execute'
IF @hr = 0
EXEC @hr = sp_OADestroy @oPKG

SoftEng007

>>If there is something wrong with the permission, how come when I right click the package and Execute, it works fine. Thanks again

in DTS designer you're running the package on the machine that DTS designer is running on. when you call the package using dtsrun /S tell dts to run the package located on /server. the drive or folder permissions may not be the same from machine to machine. I would log in to the machine in question as the user that will run the package and try to open the file.

CochiseCounty

ASKER

Hi kselvia, I tried to use the code that you gave me. My DTS package is to copy data from SQL table to a text file. WHen I execute the stored procedure from Query analyzer, it says 'The command(s) completed successfully.'. But there is no text file created. So what did I do wrong?

ASKER CERTIFIED SOLUTION

Ken Selvia

membership

This solution is only available to members.

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

Start Free Trial