Results 1 to 14 of 14

Thread: DTSRun Parameters

  1. #1
    Join Date
    Jul 2006
    Posts
    23

    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

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Did you store the package in sql server? Tried this?

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

  3. #3
    Join Date
    Jul 2006
    Posts
    23
    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) ...

  4. #4
    Join Date
    Jul 2006
    Posts
    23
    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?

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Sounds like the security mode is Windows authentication in your server.

    Can you try

    DTSRun /S machinename\Tstradli /E /N DoPost

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Does sql server allow sql authentication? If not, should replace -U and -P options with -E for windows authentication.

  7. #7
    Join Date
    Jul 2006
    Posts
    23
    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?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    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.

  9. #9
    Join Date
    Jul 2006
    Posts
    23
    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

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Open package design page and go to package -> propertoes -> logging tab in em, you can enable logging there.

  11. #11
    Join Date
    Jul 2006
    Posts
    23
    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 ...

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Tried with /A option to specify global variable name and value?

  13. #13
    Join Date
    Jul 2006
    Posts
    23
    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 ...

  14. #14
    Join Date
    Jul 2011
    Posts
    1
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •