RE: DTS and stored procedure

From: Ashish Ruparel [MSFT] (v-ashrup_at_online.microsoft.com)
Date: 05/23/04 

Date: Sun, 23 May 2004 21:48:54 GMT

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 Server™
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.

Relevant Pages

  • Re: DTS and stored procedure
    ... The DTS package can be stored in the Microsoft® SQL ... > Displays the command prompt options. ... > of the command used to execute the DTS package because the server name, ... Specify server_name to ...
    (microsoft.public.sqlserver.dts)
  • Re: OdbcDataAdapter.UpdateCommand without OdbcCommand.Parameters?
    ... "bind" the parameters to the desired columns in your DataTable and specify ... the ones with pending changes, selects the appropriate Command to execute, ... and executes that Command. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to get a list of only the files on an ftp server
    ... and it's fairly trivial to execute an NLIST ... but the trouble is that this includes directories too. ... specify a pattern like *.* in the uri to exclude directories, ... response to a LIST command so I don't want to rely on the layout of this. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Handling of shell builtins in make(1)
    ... PJ>>>The result of this is that for one and the same command you can get ... PJ>>>different behaviour whether you execute it via makeor via sh -c '...'. ... PJ>suggestions on how to efficiently implement the behaviour they specify? ... POSIX has no .SHELL target - that's pmake's invention. ...
    (freebsd-arch)
  • Re: DTS and FTP Task
    ... I have a batch file that works from the command prompt but how can I execute ... that as part of my DTS package? ... I made sure that SQL task uses the local conmnection where my file is ...
    (microsoft.public.sqlserver.server)