Data Factory SQL to Oracle pipeline error

CTrudgeon 216

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,'

Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-09T22:20:10.95+00:00

Hi @CTrudgeon ,

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.

Thanks
Saurabh
CTrudgeon 216 Reputation points

2021-04-09T22:50:27.79+00:00

Hello @Saurabh Sharma ,

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
Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-13T00:55:40.597+00:00

@CTrudgeon ok. How many rows ADF is trying to write to Oracle instance ?
CTrudgeon 216 Reputation points

2021-04-13T15:57:53.45+00:00

@Saurabh Sharma It's a very small job. Approximately 6500 rows.
CTrudgeon 216 Reputation points

2021-04-13T19:54:22.947+00:00

@Saurabh Sharma It's a straight copy job with no data transformation or manipulation. Is it possibly a versioning issue?
Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-13T21:22:18.417+00:00

@CTrudgeon Have you imported mappings on Copy Data activity from "Mappings" Tab. Can you please provide a screenshot.
CTrudgeon 216 Reputation points

2021-04-14T14:31:28.6+00:00

@Saurabh Sharma Yes, I've imported the mapping schemas. Here is the screenshot.
Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-14T15:03:51.037+00:00

@CTrudgeon Thanks. Also, can you share pipeline run id and activity run id so that I can try checking in backend.
CTrudgeon 216 Reputation points

2021-04-14T15:24:54.647+00:00

@Saurabh Sharma Thanks so much for looking into this. Here are the IDs.

Pipeline Run ID: ba27920b-95a9-4681-a961-cdbe22fad2ef

Activity Run ID: a75378e3-ad50-4fdd-b853-e30f98f74b94
Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-15T00:43:54.637+00:00

@CTrudgeon Thanks for sharing. I am checking internally on this one and get back to you.
Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-15T15:16:47.737+00:00

@CTrudgeon Can you also share the DDL of the oracle table please.
CTrudgeon 216 Reputation points

2021-04-15T19:56:39.567+00:00

@Saurabh Sharma I've attached the DDL.

88365-ddl.txt
Saurabh Sharma 23,676 Reputation points Microsoft Employee

2021-04-15T20:12:52.487+00:00

@CTrudgeon thanks for sharing. Looking into it.
CTrudgeon 216 Reputation points

2021-04-20T20:57:46.627+00:00

@Saurabh Sharma Have you had a chance to look into this? Thanks.
CTrudgeon 216 Reputation points

2021-04-23T17:21:55.723+00:00

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"?
sonu kuswaha 0 Reputation points

2023-07-17T08:18:14.9633333+00:00

Go to the Linked Service and Add Additional Parameter With Value shown in the diagram .

Accepted answer

CTrudgeon 216 Reputation points

2021-04-23T21:28:14.3+00:00

I found the solution. I needed to set EnableBulkLoad=0 in the ODBC DSN or connection string.
Please sign in to rate this answer.
HimanshuSinha-msft 19,376 Reputation points Microsoft Employee

2021-04-27T02:28:45.137+00:00

Hello @CTrudgeon ,

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

BLK 0 Reputation points

2024-03-19T17:02:08.9166667+00:00

Hi, I have the same problem, i tried connect to th Database and it didn´t conect , it shows this message "ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-12650: No common encryption or data integrity algorithm ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver][Oracle]ORA-12650: No common encryption or data integrity algorithm"

Previously the pipeline was executed successfully, but in the last four days pipeline was not executed, I checked the user and password, i checket if the user was locked but all good
Sign in to comment

1 additional answer

MadhaviB 76 Reputation points

2021-04-20T22:31:32.923+00:00

Could you please try using WireProtocolMode=1 and set WorkArounds =<<value>> in connection properties.
Please sign in to rate this answer.
CTrudgeon 216 Reputation points

2021-04-22T19:33:38.927+00:00

@MadhaviB 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.
Sign in to comment