Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Viet Nam

    Thumbs up Unanswered: How to call a DTS package (SQL Server 2000) in VisualBasic

    Dear friends,
    I am implementing a COM by VisualBasic. In this COM, I want to call a DTS package on SQL server to transfer data.
    How can I do that? Please help me!

  2. #2
    Join Date
    Jan 2003
    I found this in MSDN
    Hope it helps!

    The following code example shows a DTS package using an
    through the Execute and UnInitialize methods:

    Private WithEvents mobjPkgEvents As DTS.Package
    . . .
    Private Sub RunPackage()
    'Run the package stored in file C:\DTS_UE\TestPkg\VarPubsFields.dts.
    Dim objPackage As DTS.Package2
    Dim objStep As DTS.Step
    Dim objTask As DTS.Task
    Dim objExecPkg As DTS.ExecutePackageTask

    On Error GoTo PackageError
    Set objPackage = New DTS.Package
    Set mobjPkgEvents = objPackage
    objPackage.FailOnError = True

    'Create the step and task. Specify the package to be run, and link the step to the task.
    Set objStep = objPackage.Steps.New
    Set objTask = objPackage.Tasks.New("DTSExecutePackageTask")
    Set objExecPkg = objTask.CustomTask
    With objExecPkg
    .PackagePassword = "user"
    .FileName = "C:\DTS_UE\TestPkg\VarPubsFields.dts"
    .Name = "ExecPkgTask"
    End With
    With objStep
    .TaskName = objExecPkg.Name
    .Name = "ExecPkgStep"
    .ExecuteInMainThread = True
    End With
    objPackage.Steps.Add objStep
    objPackage.Tasks.Add objTask

    'Run the package and release references.

    Set objExecPkg = Nothing
    Set objTask = Nothing
    Set objStep = Nothing
    Set mobjPkgEvents = Nothing

    End Sub

  3. #3
    Join Date
    Jan 2003
    Viet Nam

    Thumbs up

    Very thanks, sanchi!
    By the way, can we call DTS packages from Stored Procedure?

  4. #4
    Join Date
    Jan 2003
    London, England
    Yup, you can create a job running the DTS package and the call the job from your VB-program. Right-click your DTS-package in Enterprise Manager and Scedule it to run at some interval. Then you go to the job-management window in EM, edit the newly created job, and delete the schedule but leave the job as it is with the rest. The you can execute the following statement from your VB-app:

    EXEC sp_start_job @job_name = 'myDTSjobname'
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts