DTS Execution Problem

[CrystalUser1]

I have a DTS package named 'Employee' and the sqlserver(computer name) is 'MyComputer'.

I created the following stored procedure to execute the DTS package from the .net application. Its not firing any error but not running the package. Please correct me the stored procedure.

ALTER PROCEDURE exportData AS
Exec master..xp_cmdshell
'DTSRUN /MyComputer /Employee/'.

The sqlServer uses the integrated security ie., windows authentication.

thanks

 

[dbomrrsm]

I guess you arent providing enough info that DTS run needs - below is the dts reun described in BOL

Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

this is the error I get when I run your code and exec the SP - btw remove the spaces from after the filenames and before the /

DTSRun:  Must specify a package source server or filename. 
DTSRun:  Invalid command options 
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 repository database> 
 
  Package operation (overrides stored Package settings): 
    /~A Global Variable Name:typeid=Value <may quote entire string (including name: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 (0x313233...) 
    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) 
 
 
Error:  -2147467259 (80004005); Provider Error:  0 (0) 
   Error string:  DTSRun:  Must specify a package source server or filename. 
   Error source:  Microsoft Data Transformation Services (DTS) Package 
   Help file:  sqldts80.hlp 
   Help context:  0 
 
NULL

[blue]DBomrrsm[/blue]

 

[CrystalUser1]

I tried all those, but not working.. i dont have any other things other than server name, package name. i tried taking out the spaces.. still not working.

 

[dbomrrsm]

have a look at dtsrun in BOL - that might say what the minimum inputs have to be.

[blue]DBomrrsm[/blue]

 

[700503]

have u tried to run the dtsrun command on the dos prompt directly? Also what is the user account for running the store procedure? Is it the same account u logged on the windows? I got the same problem before as the sql account doesn't have authority to run in command.

 

[EdwinGene]

CrystalUser1,

In your original post your xp_cmdshell command was, 'DTSRUN /MyComputer /Employee/'.

Maybe I'm just being too literal, but I don't see the options in that command (and maybe you just didn't include them in the generic listing of the command). Shouldn't the command read: 'DTSRUN /S MyComputer /U Employee /P password' or 'DTSRUN /S MyComputer /U Employee /E'?