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

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.

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.

Method 1

  1. Design the package in DTS Designer.
  2. Save it to SQL Server (or to a .dts file, or to the repository).
  3. 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.

Method 2

  1. Design the package in DTS Designer.
  2. Save it to a Visual Basic module file (.bas).
  3. 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

Method 3

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

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.

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.

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.

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

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
Propriedades

ID do Artigo: 323685 - Última Revisão: 12 de set de 2008 - Revisão: 1

Comentários