Select the product you need help with

PRB: DTS Transfer From SQL Server to Oracle Through OLE DB Provider Shows Ongoing Private Bytes Consumption

Article ID: 293320 - View products that this article applies to.

This article was previously published under Q293320

SYMPTOMS

When you use Dtsrun.exe to transfer a large table from SQL Server to Oracle by using the Microsoft OLE DB Provider for Oracle, or the Oracle OLE DB Provider for Oracle, during the transfer you may see that the private bytes of Dtsrun.exe continue to increase during the entire transfer.

For the transfer of a large enough number of rows, the computer memory may be stressed, which causes this error message to occur:

DTSRun OnProgress: Copy Data from document_field_default to "DATA_CONVERT"."DOCUMENT_FIELD_DEFAULT" Step; 3050000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3050000 DTSRun OnError: Copy Data from document_field_default to "DATA_CONVERT"."DOCUMENT_FIELD_DEFAULT" Step, Error = -2147024882 (8007000E) Error string: Error at Destination for Row number 3050982. Errors encountered so far in this task: 1. Error source: DTS Data Pump Help file: Help context: 0 Error Detail Records: Error: -2147024882 (8007000E); Provider Error: 0 (0) Error string: There is insufficient available memory. Error source: Microsoft Cursor Engine

NOTE: The error message occurs when Dtsrun.exe is run from the console.

Back to the top | Give Feedback

RESOLUTION

To work around the high consumption of private bytes, use the Microsoft ODBC Driver for Oracle or the Oracle ODBC Driver for Oracle. Neither of these drivers consume as much private bytes during large row transfers from Microsoft SQL Server to Oracle.

Back to the top | Give Feedback

STATUS

This behavior is by design.

Back to the top | Give Feedback

MORE INFORMATION

Steps to Reproduce Behavior

Install Oracle client tools on a Microsoft Windows 2000-based computer that has SQL Server 2000 installed.
Create a large table, with 100,000 rows. Use the Order Details table in Northwinds. Copy Order Details to a new table and continue to re-copy the table into your new table until there are 100,000 rows in the new table.
Use the Data Transformation Services Import/Export Wizard to transfer the large table from SQL Server to Oracle. Specify either the Microsoft or the Oracle OLE DB Driver for Oracle.
When the wizard completes, save the information as a package. From the console, execute the package by using DTSRUN. For example: DTSRUN.EXE -Ssrvname -Usa -P -NOracleTransfer
Use the Performance monitor, and monitor the DTSRUN private bytes counter.

RESULT: You see an ongoing escalation in the DTSRUN private bytes counter until either the package completes or you run out of physical memory, which then causes the error message shown in the "Symptoms" section.

Back to the top | Give Feedback