question

JefferyHill6537-8262 avatar image
0 Votes"
JefferyHill6537-8262 asked YitzhakKhabinsky-0887 commented

SSIS Data Flow Task running out of memory

I am using a Data Flow task to transform data from an oracle database and store it in our SQL server database.
I'm using OLE DB for both source and destination with some data conversion in between.
I am getting the error message:
[OLE DB Source [55]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on OLE DB Source returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I have read all the performance tuning and experimented with all the different properties such as default buffer rows/sizes, but to no avail. I am trying to transform about 4 million rows. I have optimized the required columns and sizes. Always fails at about 410,000 rows. The total size per row is less than 5,000 characters.

Unfortunately increasing the memory is not an option at this time. This doesn't seem like an unordinary size and volume for SSIS to handle so can't understand why it should be failing. It fails from both Visual Studio and from SQL Server Agent.

What are my options to get this to work correctly? We are using Visual Studio 2017 and the SQL Server is 2014

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JefferyHill6537-8262 avatar image
0 Votes"
JefferyHill6537-8262 answered YitzhakKhabinsky-0887 commented

Thanks Mona I'll give that a try. I still have the issue that the preview works but when I try and execute the Data Flow step I get errors:

Validation has started
[SSIS.Pipeline] Information: Validation phase is beginning.
Progress: Validating - 0 percent complete
Progress: Validating - 50 percent complete
[Oracle Source [33]] Error: The AcquireConnection method call to the connection manager Oracle Connector 1 failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
[SSIS.Pipeline] Error: Oracle Source failed validation and returned error code 0x80004005.
Progress: Validating - 100 percent complete
[SSIS.Pipeline] Error: One or more component failed validation.
Error: There were errors during task validation.

Any ideas why?

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I was able to get the connection to work.
I changed the Protection Level from DontSaveSensitive to EncryptSensitiveWithUserKey.

I'm still not entirely sure what the difference is or why/how it affects the connection.

Your method of using a parameter worked perfectly. Thank you.

1 Vote 1 ·

@JefferyHill6537-8262,

Glad to hear that you made some progress.

What provider you are using now?

I changed the Protection Level from DontSaveSensitive to EncryptSensitiveWithUserKey.
I'm still not entirely sure what the difference is or why/how it affects the connection.

ProtectionLevel is not affecting performance or memory consumption.
DontSaveSensitive - it doesn't save connection password in the actual package.
EncryptSensitiveWithUserKey - does save connection password in the actual package. It is poor choice when the password will be changed.



0 Votes 0 ·

That is what I figured what was happening.
I'm not sure what you mean by what provider am I using now?
Is there a way to set the password when setting protection level to DontSaveSensitive. Ideally we would like to keep this protection level. Can it be added to the connection string? I don't see any property that would contain it

0 Votes 0 ·
Show more comments

That's exactly what I did. I added password to the connection string and left the protection level at DontSaveSensitive and it worked perfectly fine using the "Oracle Source" object in Data Flow.

Thanks again for everyone's help in this

Jeff

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered JefferyHill6537-8262 commented

Hi @JefferyHill6537-8262,

What OLEDB provider you are using for Oracle connection?

It is better to use the following drivers:

  • Micrososft Attunity driver for Oracle. It requires SQL Server 2017 Enterprise Edition, or earlier.

  • Microsoft new Oracle connector. Though it requires SQL Server 2019, Enterprise Edition. And it doesn't require Oracle Client installation !!!

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I installed the attunity driver for Oracle. I created a connection and tested it successfully.
I created an "Oracle Source" source object in a data flow object.
I was able to preview data in an sql statement I wrote and connected it to a destination object without errors.
However when I executed the data flow object it fails right away with error:
[Oracle Source [55]] Error: The AcquireConnection method call to the connection manager maintenix_new failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Don't know why it is failing.
The problem with the attunity driver is that it doesn't have access mode "SQL command from variable" which I need to use because I need to pass in a date variable to the sql command I want to execute in oracle.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @JefferyHill6537-8262 ,

If AutoAdjustBufferSize is set to true, the engine data flow engine uses the calculated value as the buffer size, and the value of DefaultBufferSize is ignored.

May I know if you try to set AutoAdjustBufferSize as True?

Please refer to the following links:
1.Adjust the Sizing of Buffers
2.Improving data flow performance with SSIS AutoAdjustBufferSize property

Best regards,
Mona



If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have experimented with the AutoAdjustBufferSize turning it on and off setting different buffer sizes but it still fails with memory errors.

0 Votes 0 ·
Monalv-msft avatar image Monalv-msft JefferyHill6537-8262 ·

Hi @JefferyHill6537-8262 ,

The problem with the attunity driver is that it doesn't have access mode "SQL command from variable" which I need to use because I need to pass in a date variable to the sql command I want to execute in oracle.

We can set the Oracle Source SqlCommand in the expression of Data Flow Task's properties.

91066-expression-oraclesourcesqlcommand.png

Best regards,
Mona


0 Votes 0 ·