Article ID: 922436
Article Last Modified on 4/9/2007
APPLIES TO
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Workgroup Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
SYMPTOMS
Consider the following scenario:
- In a Microsoft SQL Server 2005 Integration Services package, you use a Merge Join transformation in a data flow component.
- The data flow component also contains an OLE DB destination.
- You perform one of the following operations:
- You debug the package in Microsoft SQL Server Business Intelligence Development Studio.
- You run the package. For example, you use the DTExec.exe utility to run the package. Alternatively, you run the package from a SQL Server Agent job.
- An error occurs in the OLE DB destination.
In this scenario, when the error occurs in the OLE DB destination, you may experience the following problems:
- When you debug the package in SQL Server Business Intelligence Development Studio, you receive the following error message in a Visual Studio Just-In-Time Debugger dialog box:
- An access violation occurs in the utility that runs the SQL Server 2005 Integration Services package.
- Utilities that run the package or debug the package stop responding.
Note Utilities that run or debug the package include the following:- DTExec.exe
- DTSDebugHost.exe
- DTExecUi.exe
- The components that are downstream from the Merge Join transformation output path in the package cannot run successfully.
CAUSE
A problem in the Merge Join transformation causes the Merge Join transformation to behave as if it had one more buffer than it actually has.
This problem occurs after various failure conditions become true in the OLE DB destination. For example, the OLE DB destination may encounter a constraint violation when a row is to be inserted into the destination table.
RESOLUTION
To resolve this problem, obtain the latest service pack for SQL Server 2005. For more information about how to obtain the latest service pack for SQL Server 2005, click the following article number to view the article in the Microsoft Knowledge Base:
913089 How to obtain the latest service pack for SQL Server 2005
WORKAROUND
To work around this problem, use one of the following methods:
Method 1
Fix the problem that causes the OLE DB destination to fail. For example, eliminate the constraint violation problem on the OLE DB destination.
Method 2
- Open the OLE DB Destination Editor, and then click Error Output.
- In the Input or Output column, locate the item that fails, and then select redirect the row in the Error column.
- Specify a destination for the OLE DB Destination Error Output Data Flow path. For example, specify that the destination is one of the following:
- A flat file destination
- A table in another OLE DB destination
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2005 Service Pack 2.
For more information about how to configure an error output in a data flow component, visit the following Microsoft Developer Network (MSDN) Web site:
Keywords: kbtshoot kbfix kbexpertiseadvanced kbsql2005ssis kbsql2005sp2fix KB922436
