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.
ExecuteDTSPkg(sFileOrSQLSvrName [, sPkgName] [, sParam1, ... , sParam5] [, sPkgPwd] [, sPkgVer] [, sLogFile] [, sSQLSvrUsr] [, sSQLSvrPwd])
The path and name of the file containing the package (for file-based packages), or the SQL Server name (for SQL Server stored packages).
The package name.
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.
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.
The package version. If you don't specify a version, the most recent version is used.
AN optional path and name for a log file. The log file can track activity such as:
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.
The password providing access to the SQL Server, if the user's account on the server requires a password.
0 (zero) if the package was executed successfully, otherwise a DTS error number is returned.
/* 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");