How To Execute a SQL Server 7.0 DTS Package from Visual FoxPro (247356)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft Visual FoxPro for Windows 3.0b
  • Microsoft Visual FoxPro for Windows 5.0
  • Microsoft Visual FoxPro for Windows 5.0a
  • Microsoft Visual FoxPro for Windows 6.0
This article was previously published under Q247356

SUMMARY

SQL Server 7.0 Data Transformation Services (DTS) allows the definition of processes which import, export, or transform data and can be saved as packages. Each DTS package defines a workflow that includes one or more tasks executed in a coordinated sequence as steps. After a DTS package has been created and saved, it is completely self-contained and can be retrieved and executed using the following:

The DTS run command prompt utility.SQL Server Agent to run a scheduled job that executes the package.The DTS Designer user interface.The DTS Export and Import Wizard user interfaces.The Execute method of a DTS Package object in a custom application. This article describes how to use the Execute method of a DTS Package object in a custom Visual FoxPro application.

MORE INFORMATION

When accessing DTS Packages stored in the Microsoft Repository, the LoadFromRepository method is used. This method accepts the following parameters:

ParameterData TypeRequired/Optional
RepositoryServerNameStringRequired
RepositoryDatabaseNameStringRequired
RepositoryUserNameStringRequired
RepositoryUserPasswordStringRequired
Package IDStringRequired
Version IDStringOptional
Package NameStringOptional
FlagsDTSRepositoryStorageFlagOptional
pVarPersistStgOfHostVariantOptional

When accessing DTS Packages stored in a file, the LoadFromStorageFile method is used. This method accepts the following parameters:

ParameterData TypeRequired/Optional
UNC File PathStringRequired
PasswordStringRequired
Package IDStringOptional. Required if the file contains multiple DTS Packages
Version IDStringOptional
Package NameStringOptional
pVarPersistStgOfHostVariantOptional

  1. From SQL Server 7.0 Enterprise Manager, create a DTS package and save it to the Repository as DTSPkgAlpha.
  2. From SQL Server 7.0 Enterprise Manager, create a second DTS package and save it to a file as DTSPkgBeta.DTS
  3. Create a program file named Reposit.prg, using the following code: 
    *!* Create a DTS Package Object
dtsPkg=CREATEOBJECT('dts.package')

*!* Change My_Server to reflect the server name.
*!* Change My_UserID and My_Password to reflect your User ID and Password
*!* Obtain the Package ID and Version ID from the Properties Sheet
*!* of the DTS Package

*!* Load the package to be executed from the Repository
dtsPkgName=dtsPkg.LoadFromRepository("My_Server","MSDB","My_UserID",;
   "My_Password","Package ID","Version ID","DTSPkgAlpha")
*!* Execute the DTS Package 
dtsPkg.Execute
*!* Release the DTS Package
RELEASE dtsPkg
  4. Run Reposit.prg, and note the results of the DTS Package.
  5. Create a program file named Dtsfile.prg using the following code: 
    *!* Create a DTS Package Object
dtsPkg=CREATEOBJECT('dts.package')

*!* LoadFromStorageFile() requires the following parameters:
*!* UNC path to the .DTS file to be executed
*!* User Password

*!* Load the package to be executed from a file.
dtsPkgName=dtsPkg.LoadFromStorageFile("\\UNC_Path_to\DTSPkgBeta.dts","User_Password")
*!* Execute the DTS Package 
dtsPkg.Execute
*!* Release the DTS Package
RELEASE dtsPkg
  6. Run Dtsfile.prg, and note the results of the DTS Package.

REFERENCES

(c) Microsoft Corporation 1999, All Rights Reserved. Contributions by John Desch, Microsoft Corporation.

Modification Type:MinorLast Reviewed:7/1/2004
Keywords:kbAutomation kbhowto kbSQLProg KB247356
©2004 Microsoft Corporation. All rights reserved.