SSIS Data Flow Task running out of memory

Question

SSIS Data Flow Task running out of memory

Jeffery Hill (6537) 126

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

Accepted answer

2 additional answers

Your answer

Answer 1

Jeffery Hill (6537) 126

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?

Jeffery Hill (6537) 126 Reputation points

2021-04-27T19:09:20.25+00:00

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.
Yitzhak Khabinsky 26,586 Reputation points

2021-04-27T20:27:53.767+00:00

@Jeffery Hill (6537) ,

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.
Jeffery Hill (6537) 126 Reputation points

2021-04-28T12:25:49.177+00:00

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
Jeffery Hill (6537) 126 Reputation points

2021-04-28T14:07:31.137+00:00

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
Yitzhak Khabinsky 26,586 Reputation points

2021-04-28T14:09:38.363+00:00
@Jeffery Hill (6537) ,

Right mouse click on the connection in the connections pain.

Select Parameterize... option, and parameterize the ConnectionString property in the project level parameter.

,

Answer 2

Yitzhak Khabinsky 26,586

Hi @Jeffery Hill (6537) ,

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 !!!

Jeffery Hill (6537) 126 Reputation points

2021-04-22T19:57:51.34+00:00

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.

Answer 3

Monalv-MSFT 5,926

Hi @Jeffery Hill (6537) ,

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.

Jeffery Hill (6537) 126 Reputation points

2021-04-22T19:58:22.707+00:00

I have experimented with the AutoAdjustBufferSize turning it on and off setting different buffer sizes but it still fails with memory errors.
Monalv-MSFT 5,926 Reputation points

2021-04-26T03:21:14.177+00:00

Hi @Jeffery Hill (6537) ,

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.

Best regards,
Mona

Share via

SSIS Data Flow Task running out of memory

2 additional answers

Your answer