Execute Process Question

Execute Process Question

Post by Frank P » Wed, 30 Apr 2003 22:12:52



I have a client that uses SQL 7 and I would like to execute a package
within a package. I noticed SQL 2K has a task to launch another task,
but it looks like all I can do is to call it by execute a process task.
How would I use this if I want to launch a structured dts file on my C
drive?

Help apprecaited. Thanks.
Frank  

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Execute Process Question

Post by Edgardo Valde » Wed, 30 Apr 2003 23:33:22


SQL2000 has a task that allows you to execute a package
within another package. However, you can create a task to
execute command line using the DTSRUN executable and the
package ID.

 Command Prompt Utilities

dtsrun Utility
  New Information - SQL Server 2000 SP3.

The dtsrun utility executes a package created using Data
Transformation Services (DTS). The DTS package can be
stored in the Microsoft? SQL Server? msdb database, a COM-
structured storage file, or SQL Server Meta Data Services.

From Books Online:

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]
]

Arguments/?

Displays the command prompt options.

~

Specifies that the parameter to follow is hexadecimal text
representing the encrypted value of the parameter. Can be
used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R
options. Using encrypted values increases the security of
the command used to execute the DTS package because the
server name, password, and so on, are not visible. Use /!Y
to determine the encrypted command.

/S server_name[\instance_name]

Specifies the instance of SQL Server to connect to.
Specify server_name to connect to the default instance of
SQL Server on that server. Specify
server_name\instance_name to connect to a named instance
of SQL Server 2000 on that server.

/U user_name

Is a login ID used to connect to an instance of SQL Server.

/P password

Is a user-specified password used with a login ID.

/E

Specifies a trusted connection (password not required).

/N package_name

Is the name of a DTS package assigned when the package was
created.

/G package_guid_string

Is the package ID assigned to the DTS package when it was
created. The package ID is a GUID.

/V package_version_guid_string

Is the version ID assigned to the DTS package when it was
first saved or executed. A new version ID is assigned to
the DTS package each time it is modified. The version ID
is a GUID.

/M package_password

Is an optional password assigned to the DTS package when
it was created.

/F filename

Is the name of a structured storage file containing DTS
packages. If server_name is also specified, the DTS
package retrieved from SQL Server is executed and that
package is added to the structured storage engine.

/R repository_database_name

Is the name of the repository database containing DTS
packages. If no name is specified, the default database
name is used.

/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.

/L log_file_name:

Specifies the name of the package log file.

/W Windows_Event_Log

Specifies whether or not to write the completion status of
the package execution to the Windows Application Log.
Specify True or False.

/Z

Indicates that the command line for dtsrun is encrypted
using SQL Server 2000 encryption.

/!X

Blocks execution of the selected DTS package. Use this
command parameter when you want to create an encrypted
command line without executing the DTS package.

/!D

Deletes the DTS package from an instance of SQL Server.
The package is not executed. It is not possible to delete
a specific DTS package from a structured storage file. The
entire file needs to be overwritten using the /F and /S
options.

/!Y

Displays the encrypted command used to execute the DTS
package without executing it.

/!C

Copies the command used to execute the DTS package to the
Microsoft Windows? clipboard. This option can also be used
in conjunction with /!X and /!Y.

Remarks

Security Note  Batch files may contain credentials stored
in plain text. Credentials may be echoed to the user's
screen during batch execution.

If you do not specify any command line switches, specify
an incorrect command line switch, or your command
statement contains a syntax error, dtsrun returns error
information and usage instructions. If you enter dtsrunui
on the command line without any command line switches, you
will start the DTS Run utility.

Spaces between command switches and values are optional.
Embedded spaces in values must be embedded between double
quotation marks.

If an option is specified multiple times, the last
occurrence takes precedence. One exception is the /A
command switch. Specifying more than one /A switch creates
multiple global variables.

When specifying a global variable with the /A command
switch, you must use a type identifier to indicate the
data type of the global variable.

A tilde (~) character after the forward slash (for
example, /~Z) indicates that the parameter value is
encrypted and what follows is the hexadecimal text of the
encrypted value.

The table shows the global variable data types and their
IDs.

Data type Type ID
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

For information about where to find or how to run this
utility, see Getting Started with Command Prompt Utilities.

Examples
To execute a DTS package saved as a COM-structured storage
file, use:

dtsrun /Ffilename /Npackage_name /Mpackage_password

To execute a DTS package saved in the SQL Server msdb
database, use:

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
 /Mpackage_password

To execute a DTS package saved in Meta Data Services, use:

dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name
 /Rrepository_name

Quote:>-----Original Message-----
>I have a client that uses SQL 7 and I would like to
execute a package
>within a package. I noticed SQL 2K has a task to launch
another task,
>but it looks like all I can do is to call it by execute a
process task.
>How would I use this if I want to launch a structured dts
file on my C
>drive?

>Help apprecaited. Thanks.
>Frank  

>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.


 
 
 

Execute Process Question

Post by Frank P » Wed, 30 Apr 2003 23:53:05


Thanks for the detailed reply. I did go with DTSRUN and called it from a
simple one line batch file in the execute process task. Seems to work
well. Thanks again.

Frank

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Execute Process Task gives error

Hi,

I am using SS2K.  I trying to run an exe file through an Execute Process
task whose dll is registered where the .exe file resides.  I get  the
following error

Run-time error 429
ActiveX component can't create object

Please help.

Thanks

Ather

2. Slow Exp73 from ORACLE8 database

3. Execute Process Task - Visual Basic - Return Code?

4. The Fill Factor

5. Execute Process Task Issue

6. Query Optimizer Weirdness in Stored Procedure

7. Execute Process Task

8. Oracle SSL Authentication

9. Execute Process Task issue!

10. Process OLAP cube package executes slowly

11. Time Out During Execute Process of DTS

12. DTS Execute Process task fails

13. Can I use Global Variables for Parameters to the Execute Process Task