DTSRun Parameters

Hi

Having looked at the MS site I am confused by the syntax I should be using for DTSRun in SQL Server 2000.

The MS site says the syntax is ...

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

Given a SQL Servername of 'machinename\Tstradli', SQL Server User name of 'SA', Password of 'Fred', and a Package Name of 'DoPost' and no package password that I can discern ...

I am using the string

"DTSRun /Smachinename\Tstradli /USA /PFred /NDoPost"
and I am getting the error "The filename, directory name, or volume label syntax is incorrect." so obviously SQL Server cannot see my DTS package ...

Does any body know what I am doing wrong here? Do I need to specify the owner of the package at any point?

Thanks

T

Did you store the package in sql server? Tried this?

DTSRun /S machinename\Tstradli /U SA /P Fred /N DoPost

The package is in SQL Server. When I try the above syntax from the OS command prompt I get the error "Log on failed for user SA 'not a trusted SQL server connection' " ... I get the same error when I log on providing my windows log on (which is how I would log onto SQL 2k normally) ...

This is the error I get:

Error string: Login failed for user 'SA'. Reason: Not associated with a trus
ted SQL Server connection.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Do I need to have an ODBC connection set up to allow DTS to access the database?

Sounds like the security mode is Windows authentication in your server.

Can you try

DTSRun /S machinename\Tstradli /E /N DoPost

Does sql server allow sql authentication? If not, should replace -U and -P options with -E for windows authentication.

This is the error I get in response to the command

DTSRun /S machinename\Tstradli /E /N DoPost

Error: -2147467259 (80004005); Provider Error: 17 (11)
Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exi
st or access denied.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

On a seperate note how do I switch SQL Servers autentication mode from windows to SQL Server?

Did you add your windows account as sql login in sql server? To allow sql authentication, right click the server and go to properties -> security tab in em. Choose 'sql server and windows' under authentication.

Thanks - I've got the DTSRun command executing from the OS with the details specified above. However it is now producing a run time error ...

Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: ActiveX Scripting encountered a Run Time Error during the exec
ution of the script.
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500

DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.

The DTS step executes correctly from DTS designer. However I believe it returns a runtime error when run from the OS cmnd line because the DTS step is expecting 2 string parameters ... when I specify these after the DTS package name it does not recognise the package and does not execute DTS but does without?

I am not able to see the runtime error I am getting as no details are being sent to my DTS logs - though I am not sure how these are set up.

How should I set up DTS so I can see the DTS logs when executing a DTS package from the OS?

How are parameters passed to DTS Packages through OS cmnd line and xp_cmndshell ... is this possible?

Many thanks

T

Open package design page and go to package -> propertoes -> logging tab in em, you can enable logging there.

Thanks I can see the error in the log now. However it is the same as the message passed back to the command prompt (i.e. unspecified runtime error).

Can parameters be passed to DTS Packages running activeX scripts? How is this specified as part of the DTSRun command? I am not sure how the variables I need in my Active X script can get there ...

Tried with /A option to specify global variable name and value?

I've tried this but still get the runtime error I am trying to pass 2 string parameters for 2 string global variables i_url and i_request and have tried variations on the following syntaxs ...

DTSRun /S machine /U user /P pwd /N DoPost /A DTSGlobalVariables("i_Url").Value:String='request url' , DTSGlobalVariables("i_Request").Value:String:='req uest string'

DTSRun /S machine /U user /P pwd /N DoPost /A i_Url:String='request url' , i_Request:String:= 'request string'

I have placed message boxes inside my DTS Jscript to display the global variables if they ever get there - which has not happened so far ...

So far I am trying this just through the cmnd line before placing a functioning command in a T-SQL procedure to be executed by xp_cmdshell.

Anybody have any ideas what I'm doing wrong? I am new to DTS ...

The syntax that you are using to pass in your parameters are wrong. Here is the Syntax and excerpt form MS

/A global_variable_name:typeid=value

Specifies a package global variable, where typeid = type identifier for the data type of the global variable. The entire argument string can be quoted. This argument can be repeated to specify multiple global variables. See the Remarks section for the different available type identifiers available with global variables.

To set global variables with this command switch, you must have either Owner permission for the package or the package must have been saved without DTS password protection enabled. If you do not have Owner permission, you can specify global variables, but the values used will be those set in the package, not those specified with the /A command switch.

- The typeId Values are :

Integer (small) 2
Integer 3
Real (4-byte) 4
Real (8-byte) 5
Currency 6
Date 7
String 8
Boolean 11
Decimal 14
Integer (1-byte) 16
Unsigned int (1-byte) 17
Unsigned int (2-byte) 18
Unsigned int (4-byte) 19
Integer (8-byte) 20
Unsigned int (8-byte) 21
Int 22
Unsigned int 23
HRESULT 25
Pointer 26
LPSTR 30
LPWSTR 31

Cheers!