In Azure ADF v2 pipeline, i'm trying to copy data from sql server to Azure postgresql database. My source is sql server and my sink is Azure postgresql database. I'm moving all records from my source table to destination table. However, on pipeline execution getting error as
"errorcode": "2200", "message": "'type=npgsql.postgresexception,message=08p01: invalid message format'", "failuretype": "usererror.
What is the issue?
"errorcode": "2200", "message": "'type=npgsql.postgresexception,message=08p01: invalid message format'", "failuretype": "usererror.
You may find some clues in this old case which is similar to your error trace. Please see the solution in above link: https://github.com/npgsql/npgsql/issues/189#issuecomment-67001166
Basically, if the string value we try to persist using Npgsql was derived from MemoryStream.GetBuffer() it will explode, but if it came from MemoryStream.ToArray() it will work fine.
It is explained in official document:
Note that the buffer contains allocated bytes which might be unused. For example, if the string "test" is written into the MemoryStream object, the length of the buffer returned from GetBuffer is 256, not 4, with 252 bytes unused. To obtain only the data in the buffer, use the ToArray method; however, ToArray creates a copy of the data in memory.
For now,as workaround, you could use csv file to be an intermediary. Transfer data from sql server to csv files in Azure Blob Storage. Then transfer data into destination postgresql database.
I encountered this issue myself. You need to change the "Write batch size" from 1000 to a smaller value (e.g. 1000) as per my answer here. https://stackoverflow.com/a/58558726/10393844
