Knowledge Base

HOW TO: Troubleshoot DTS Packages That You Run from Visual Basic Applications

Article ID: 323685

Article Last Modified on 10/16/2006

APPLIES TO

Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 64-bit Edition
Microsoft SQL Server 7.0 Standard Edition

This article was previously published under Q323685

IN THIS TASK

SUMMARY Three Methods to Run a DTS Package from Visual Basic

Method 1
Method 2
Method 3

The DTS Package Runs on the Client Workstation Troubleshooting Recommendations

Use Visual Basic 6.0
Set the Package to Fail When Errors Occur
Implement Package Events

REFERENCES

SUMMARY

When you run a Microsoft SQL Server Data Transformation Services (DTS) Package from a Microsoft Visual Basic application, the package may fail without raising any error messages to help you to troubleshoot the failure. This article describes how to troubleshoot DTS packages that you run from Visual Basic applications.

back to the top

Three Methods to Run a DTS Package from Visual Basic

There are three basic methods to create, to save, and to run a DTS package from Visual Basic. In each of the following methods, you must use a reference to the Microsoft DTSPackage Object Library in the references of the Visual Basic project.

back to the top

Method 1

Design the package in DTS Designer.
Save it to SQL Server (or to a .dts file, or to the repository).

Load, and then run the package (as is) from Visual Basic by using code that is similar to the following code:

    Dim pkg as DTS.Package
    Set pkg = New DTS.Package
    pkg.LoadFromSQLServer "<servername>", "<user id>", "<password>", _
        DTSSQLStgFlag_Default, "", "", "", "<package name>"
    pkg.Execute

If you want to continue editing the package in DTS Designer, you may prefer to use this method because it always runs the latest version of the package.

back to the top

Method 2

Design the package in DTS Designer.
Save it to a Visual Basic module file (.bas).
Incorporate the module into a Visual Basic 6.0 project, and then run the code.

To incorporate the module into a Visual Basic 6.0 project:
1. Start Visual Basic 6.0, and then create a new Standard EXE project.
2. Click Project, References, and then add a reference to the Microsoft DTSPackage Object Library.
3. Click Project, click Add File, and then add the .bas module that DTS Designer saved to the project.
4. In Project Explorer, right-click Form1, and then click Remove to make Sub Main in the DTS .bas module the Startup Object for the project.

NOTE: If you are using SQL Server 7.0, you must use a separate tool to convert your DTS package to Visual Basic code. For additional information about the other tool and how to use it, click the article number below to view the article in the Microsoft Knowledge Base:

239454 INF: ScriptPkg Tool Generates DTS Object Model Code to Help Use DTS Programmatically

back to the top

Method 3

Code a new package from scratch in Visual Basic and use the DTS object model.

back to the top

The DTS Package Runs on the Client Workstation

When you run a DTS package from a Visual Basic application, the steps of the package run on the client workstation, and not on the computer that is running SQL Server. This behavior has the following implications:

Files: The client workstation must have the redistributable DTS DLLs and support files. See the REDIST.txt file in the root folder of the SQL Server installation CD for a list of these files.
Permissions: The package runs in the security context of the user that starts the Visual Basic application. As a result, you may experience permission issues when the package accesses resources on SQL Server or externally, such as text files or file-based databases.

back to the top

Troubleshooting Recommendations

Use Visual Basic 6.0

To avoid possible issues between DTS and the COM Interoperability of .NET Framework, use Visual Basic 6.0 instead of Visual Basic .NET to troubleshoot the execution of your package.

back to the top

Set the Package to Fail When Errors Occur

Mark the package to fail when an error occurs. To do so:

In DTS Designer, click the Logging tab of the Package Properties dialog box, and then click to select Fail package on first error.

-or-

In the code, set the FailOnError property of the Package object to True. Without this property setting, no error is raised to the Visual Basic application. (This is explained in the documentation of this property in SQL Server Books Online.)

NOTE: This step is not necessary if you implement Package events as described in the following section, but you may want to try this step alone first to retrieve an error message.

back to the top

Implement Package Events

To retrieve additional error information, particularly when the error occurs on the Execute method call, you can use the GetExecutionErrorInfo method of the Step object. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

240221 HOW TO: Handle Errors in Data Transformation Services 'Package' and 'Step' Objects

However, the events of the DTS package provide additional information about package progress, and the OnError event provides the same information as GetExecutionErrorInfo.For additional information about how to implement the five events of the Package object and for the sample code, click the article numbers below to view the articles in the Microsoft Knowledge Base:

221193 HOW TO: Install Data Transformation Services (DTS) Event Handlers in Visual Basic

321525 HOW TO: Use DTS Package Events in Visual Basic .NET
The following is a list of the five events of the Package object.

NOTE: You must declare the WithEvents of your Package object, and then implement handlers for all five events if you implement any of them or an access violation will occur.
- OnStart
- OnProgress
- OnFinish
- OnError
- OnQueryCancel
The OnQueryCancel event fires periodically so that you can cancel the execution of the package. However, under certain circumstances, package execution is canceled unexpectedly, and then you receive the error message, "Execution was canceled by user."For additional information about this error message, click the article number below to view the article in the Microsoft Knowledge Base:

319058 BUG: DTS Package Execution Is Canceled Unexpectedly in a Visual Basic Application
Threading issues are the most common cause of access violations when you run DTS packages from Visual Basic applications. Particularly when you implement Package events, it is extremely helpful to specify that all the steps of the package execute on the main package thread. To specify this, follow these steps for each task:
- In DTS Designer, in the Workflow Properties dialog box of the task, click the Options tab, and then click to select Execute on main package thread.
  
  -or-
- In your code, set the ExecuteInMainThread property of each step to True:
```
    Dim intStepCount As Integer
    For intStepCount = 1 To pkg.Steps.Count
        pkg.Steps(intStepCount).ExecuteInMainThread = True
    Next intStepCount
                        
```
For additional information about DTS and threading issues, click the article number below to view the article in the Microsoft Knowledge Base:

318819 PRB: A DTS Package Raises Exceptions or Stops Responding When You Run It as a Scheduled Job

After you complete these steps, your Visual Basic 6.0 code should look like the following code, which prints information from each Package event to the Immediate window:

Option Explicit
Dim WithEvents pkg As DTS.Package2

Private Sub Command1_Click()
    
    'Declare variables
    Dim intStepCount As Integer
    Dim s As Integer
    
    'Load package
    Set pkg = New DTS.Package2
    pkg.LoadFromSQLServer "<server>", "<user id>", "<password>", _
        DTSSQLStgFlag_Default, Empty, Empty, Empty, "<package>", Nothing
    
    'Avoid threading issues
    intStepCount = pkg.Steps.Count
    For s = 1 To intStepCount
        pkg.Steps(s).ExecuteInMainThread = True
    Next s
    
    'Execute package
    pkg.Execute
    
    'Clean up
    pkg.UnInitialize
    Set pkg = Nothing
    
End Sub

Private Sub pkg_OnError(ByVal EventSource As String, ByVal ErrorCode As Long, _
        ByVal Source As String, ByVal Description As String, _
        ByVal HelpFile As String, ByVal HelpContext As Long, _
        ByVal IDofInterfaceWithError As String, pbCancel As Boolean)
    Debug.Print "ONERROR event from " & EventSource & vbCrLf & _
        " error code: " & CStr(ErrorCode) & vbCrLf & _
        " source: " & Source & vbCrLf & _
        " description: " & Description
End Sub

Private Sub pkg_OnFinish(ByVal EventSource As String)
    Debug.Print "ONFINISH event from " & EventSource
End Sub

Private Sub pkg_OnProgress(ByVal EventSource As String, _
        ByVal ProgressDescription As String, _
        ByVal PercentComplete As Long, ByVal ProgressCountLow As Long, _
        ByVal ProgressCountHigh As Long)
    Debug.Print "ONPROGRESS event from " & EventSource & " - " & ProgressDescription
End Sub

Private Sub pkg_OnQueryCancel(ByVal EventSource As String, pbCancel As Boolean)
    Debug.Print "ONQUERYCANCEL event from " & EventSource & vbCrLf & _
        " value of pbcancel is " & pbCancel
    'Avoid unexpected cancellation of package - see Q319058
    'Note that "If pbCancel = True" does not work
    If pbCancel Then
        pbCancel = False
        Debug.Print "pbCancel reset from True to False"
    End If
End Sub

Private Sub pkg_OnStart(ByVal EventSource As String)
    Debug.Print "ONSTART event from " & EventSource
End Sub

back to the top

REFERENCES

General

For additional information about how to run DTS packages from Visual Basic, click the article numbers below to view the articles in the Microsoft Knowledge Base:

242391 INF: DTS Package Development, Deployment, and Performance

315661 HOW TO: Run a SQL Server Data Transformation Services Package from Visual Basic

252987 INF: Execute a SQL Server DTS Package from Active Server Pages

Saving Packages

For additional information about how to save packages, click the article numbers below to view the articles in the Microsoft Knowledge Base:

293223 FIX: Problems Saving DTS Packages to Visual Basic Files

293164 PRB: Errors May Occur When You Use SQL Server 2000 to Save a DTS Package as a Visual Basic File

300192 BUG: DTS Import/Export Wizard or TransferObjectsTask Fails to Maintain Filegroup Settings for Transferred Objects

Error Handling and Events

For additional information about error handling and events, click the article numbers below to view the articles in the Microsoft Knowledge Base:

251229 FIX: GetExecutionErrorInfo and OnError Event May Not Return All Errors

271889 PRB: Error Message: 'Exception Access Violation 2147221499. Need to run the object to perform this operation' Occurs When You Run a DTS Package in Microsoft Visual Basic Code

Known Issues

For additional information about known issues, click the article numbers below to view the articles in the Microsoft Knowledge Base:

320304 PRB: A DTS Package That You Save as a Visual Basic File May Fail to Import Tab-Delimited Text Data

319048 PRB: Cannot Use Data Transformation Services Event Handlers in Visual Basic with Execute Package Task

299354 PRB: DTS Execute Package Task May Fail to Execute Child Package

316331 FIX: DTSTransferObjects with Events Generates an Error Message and an Access Violation in Visual Basic

back to the top

Keywords: kbhowtomaster KB323685

Microsoft KB Archive/323685

Contents

HOW TO: Troubleshoot DTS Packages That You Run from Visual Basic Applications

IN THIS TASK

SUMMARY

Three Methods to Run a DTS Package from Visual Basic

Method 1

Method 2

Method 3

The DTS Package Runs on the Client Workstation

Troubleshooting Recommendations

Use Visual Basic 6.0

Set the Package to Fail When Errors Occur

Implement Package Events

REFERENCES

General

Saving Packages

Error Handling and Events

Known Issues