question

CTrudgeon-1880 avatar image
0 Votes"
CTrudgeon-1880 asked HimanshuSinha-MSFT commented

Data Factory SQL to Oracle pipeline error

Hello,

We are trying to migrate from Informatica to Data Factory. I'm attempting to do a simple pipeline copy activity to replace a process that works fine in Informatica and am getting the following error message. Any help would be greatly appreciated. This copy activity is from a SQL database to an Oracle table.


Error code: 2200

Failure type: User configuration issue

Details: Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error tolerance exceeded. Bulk load operation terminated. Error in parameter 7. ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in row 1. Error in parameter 7.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Error tolerance exceeded. Bulk load operation terminated. Error in parameter 7. ERROR [22001] [Microsoft][ODBC Oracle Wire Protocol driver]String data, right truncated. Error in row 1. Error in parameter 7.,Source=msora28.dll,'

azure-data-factory
· 15
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.

Hi @ctrudgeon-1880,

Thanks for using Microsoft Q&A !!
Can you please try “Skip incompatible rows” in copy settings so that you could record the problematic records which may be causing the problem.

86407-image.png

Thanks
Saurabh

0 Votes 0 ·
image.png (53.6 KiB)

Hello @SaurabhSharma-msft,

Thank you for your reply. Unfortunately, “Skip incompatible rows” did not work. I received the same error message as before and no data was transferred.

Best regards,
Chad

0 Votes 0 ·

@ctrudgeon-1880 ok. How many rows ADF is trying to write to Oracle instance ?

0 Votes 0 ·
Show more comments

The failure message says "Error in parameter 7". This is a straight copy job in which I set no parameters. Does anyone know how I can find "parameter 7"?

0 Votes 0 ·
CTrudgeon-1880 avatar image
0 Votes"
CTrudgeon-1880 answered HimanshuSinha-MSFT commented

I found the solution. I needed to set EnableBulkLoad=0 in the ODBC DSN or connection string.

· 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.

Hello @CTrudgeon-1880 ,

It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .
Thanks
Himanshu

1 Vote 1 ·
ma01 avatar image
0 Votes"
ma01 answered CTrudgeon-1880 edited

Could you please try using WireProtocolMode=1 and set WorkArounds =<<value>> in connection properties.

· 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.

@ma01 Hello. I added the connection properties and am still getting the error. The workaround value I used was 536870912 for SQL to Oracle copy errors.


90432-dfissue.png


0 Votes 0 ·
dfissue.png (14.3 KiB)