ExecuteDTSPkg

Cicode Programming Reference > Cicode Function Categories > SQL Functions Introduction > ExecuteDTSPkg

ExecuteDTSPkg

Loads and executes a DTS (Data Transformation Services) package which initiates data transfer and transformations between OLE DB data sources.

A DTS package is created using the DTS utility provided in Microsoft SQL Server 7.0. It can be saved in a COM structured file, a Microsoft Repository, or in an SQL Server Database.

All except the first of this function's parameters are optional, and their use will depend on your needs.

Syntax

ExecuteDTSPkg(sFileOrSQLSvrName [, sPkgName] [, sParam1, ... , sParam5] [, sPkgPwd] [, sPkgVer] [, sLogFile] [, sSQLSvrUsr] [, sSQLSvrPwd])

sFileOrSQLSvrName:

The path and name of the file containing the package (for file-based packages), or the SQL Server name (for SQL Server stored packages).

sPkgName:

The package name.

For file-based packages where only one package is stored in a file, you can ignore this parameter, as the package name defaults to the name of the file.
If the package has been named differently to the file, or a file contains more than one package, you need to specify the package name. You need to also specify the package name for SQL Server stored packages.

sParam1, ,sParam5:

Five optional variables which may be used as global variables within the DTS package. The variables need to be named Param1, Param2, Param3, Param4, and Param5.

sPkgPwd:

The package password.

The creator of the DTS package may have implemented a password so that unauthorized users cannot access it. In this case, you need to specify the package password. If no password has been implemented, you can omit this parameter.

sPkgVer:

The package version. If you don't specify a version, the most recent version is used.

sLogFile:

AN optional path and name for a log file. The log file can track activity such as:

File DTS package detected
SQL DTS package detected
Package initialized successfully
Package executed sucessfully
Package execution was not successful

sSQLSvrUsr:

The user name providing access to the SQL Server where the DTS package is stored. A user's account on the SQL Server consists of this user name and, in most cases, a password.

This parameter also determines which method is used to load the package.

If sSQLSvrUsr is specified, the package is assumed to be an SQL Server stored package. In this case, the package is loaded using the LoadFromSQLServer() method. Otherwise, the package is file-based and LoadFromStorageFile() is called.

sSQLSvrPwd:

The password providing access to the SQL Server, if the user's account on the server requires a password.

Return Value

0 (zero) if the package was executed successfully, otherwise a DTS error number is returned.

Example

/* File-based package with one package per file, where the package 
name is the same as the file name.*/

iResult = ExecuteDTSPkg("c:\dtspackages\package.dts");

/*SQL Server stored package with additional parameters */

iResult = ExecuteDTSPkg("Server1", "TestPackage", "Param1", "Param2", "Param3",
 "Param4", "Param5", "Fred", "1", "c:\packages\PkgLog.txt", "jsmith", "secret");