SSIS Oracle destination insert commit size.

Zaim Raza 0

Hi,

Which property in Oracle destination gives the same result as OLEDB Maximum Insert commit size functionality. right now, Script component is concatenating multiple rows together instead of one row per batch.

Simply I want script component output field should contain one row at a time instead of all error rows.

Please suggest how to achieve to store one row at a time in script component output columns.

User's image

Greg Low 1,495 Reputation points Microsoft Regional Director

2024-03-12T08:57:09.04+00:00

Sorry, not quite following. If you are writing a script component as a destination (I think that's what you're suggesting), in the ProcessInputRow method, you need to write the row to the database. That is called for each row so it should end up with the same type of outcome as setting a batch size to 1.

Generally in the PreExecute, you create the command object, and then in the ProcessInputRow method, you populate and call the command.

Or is that not what you're meaning? For example, are you writing a transformation and not a destination?
ZoeHui-MSFT 33,211 Reputation points

2024-03-13T02:33:20.7366667+00:00

Hi @Zaim Raza，

Not clear about your issue, do you mean that you only want the Error output to be 1 column and then insert into the script task? If yes, you need to connect the source to script task instead of the oracle destination.
Zaim Raza 0 Reputation points

2024-03-13T20:33:41.7033333+00:00

Hi @Greg Low , @ZoeHui-MSFT

We can leverage the SSIS OLEDB destination's functionality to set the maximum insert commit size. Presently, I'm focused on implementing a data load using an Oracle destination (Microsoft connector for oracle), where only error rows are directed to a script component for XML construction, then subsequently sent to ETL_LOG.

Note: The following is implemented in OLEDB destination but I am implementing the same concept in Oracle destination ( image is attached in initial question)

https://dinesql.blogspot.com/2016/11/ssis-ole-db-destination-sends-all-all-records-to-error-output.html

However, I've encountered an obstacle with the Oracle destination. Despite setting rows per batch and batch size to 1, I'm unable to send just one row at a time to the script component. Instead, the destination sends batches of error rows to the script, leading to the creation of multiple rows in the output XML variable.

My goal is to have only one row in the output XML so that I can store the error row in ETL_LOG along with its error code and description. Achieving this was straightforward with the OLEDB Destination's Maximum Insert Commit Size functionality, but I have not found a similar feature in the Oracle destination.

Could you please advise on how to transmit a single row to the script component for transformation in this context?

Thanks in advance.

Zaim Raza
Greg Low 1,495 Reputation points Microsoft Regional Director

2024-03-15T09:37:03.6533333+00:00

Simplest option might be to install the OLE-DB provider for Oracle, and then just use the OLE-DB Destination perhaps?
Alternately, if you're passing it through a script component anyway, you could have it write to the target row by row.
In your image, it's not clear to anyone reading this, what's happening in either the script component, the derived column (I presume that's the DC) or the OLEDB DEST Error Log.
If you are using the Oracle Destination, have you set both the BatchSize and the TransactionSize to 1?

Where is the transformation to XML happening?
Zaim Raza 0 Reputation points

2024-03-19T03:46:28.4333333+00:00

I can't able to paste the actual image due to sensitive information (i.e. task names). I am using script component in order to build the XML of the input columns(Oracle destination error output) and then this XML i will store with error code and error description in Database log table.

Yes i tried to set both transaction size and rows per batch property to 1 and it will send rows in batch ( Please see my image in initial question).