HOW TO: Troubleshoot DTS Packages That You Run from Visual Basic Applications (323685)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 2000 64 bit (all editions)
- Microsoft SQL Server 7.0
This article was previously published under Q323685 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:- Start Visual Basic 6.0, and then create a new Standard EXE project.
- Click Project, References, and then add a reference to the Microsoft DTSPackage Object Library.
- Click Project, click Add File, and then add the .bas module that DTS Designer saved to the project.
- 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 RecommendationsUse 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: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
REFERENCESGeneralFor 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 PackagesFor 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 EventsFor 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 IssuesFor 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
Modification Type: | Major | Last Reviewed: | 10/16/2006 |
---|
Keywords: | kbHOWTOmaster KB323685 kbAudDeveloper |
---|
|