Re: DTS and stored procedure
From: JD (jdavis_at_idfg.state.id.us)
Date: 05/24/04
- Next message: Andy: "DTS with relative path or system variables"
- Previous message: Kent Prokopy: "RE: HowTo: Copy a DTS Package"
- In reply to: Ashish Ruparel [MSFT]: "RE: DTS and stored procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 24 May 2004 12:29:59 -0600
thank you
"Ashish Ruparel [MSFT]" <v-ashrup@online.microsoft.com> wrote in message
news:hP%23aG$QQEHA.1452@cpmsftngxa10.phx.gbl...
> Hi,
>
> There is a command prompt utility "dtsrun".
> You can invoce this utility from the stored procedure, using the extended
> stored procedure, "xp_cmdshell".
>
> The syntax for the stored procedure would be
>
> create procedure [CallDtsPack] As
>
> execute xp_cmdshell '/S<SQLServerName> /E /N<DTSPackage Name>',NO_OUTPUT
>
>
> The above stored procedure will connect to the SQL Server using Windows
> Authentication and no output would be displayed.
>
>
> Syntax for DTSRUN can be viewed at
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp
> _dtsrun_95kp.asp
>
> The dtsrun utility executes a package created using Data Transformation
> Services (DTS). The DTS package can be stored in the Microsoft® SQL
ServerT
> msdb database, a COM-structured storage file, or SQL Server Meta Data
> Services.
>
> 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
>
>
>
>
>
>
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
- Next message: Andy: "DTS with relative path or system variables"
- Previous message: Kent Prokopy: "RE: HowTo: Copy a DTS Package"
- In reply to: Ashish Ruparel [MSFT]: "RE: DTS and stored procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
