Azure Synapse target: Error with long string value

[bilzekek]

Hi,
I have a problem with Replicate, when transferring data from on-prem SQL server to Azure Synapse, dedicated SQL pool.
As an example, I have a simple task with 1 table and only two columns. There are no transformation on the table itself, and neither any global transformations on the task.

The initial load, however, fails with below error:

I have tried to use both table and global transformation, that take use of the substr(X,Y,Z).

So for example as a global transformation rule, I have used “replace column value” option and then the function: substr($AR_M_SOURCE_COLUMN_DATA,1,4000).

This removes the error, but also insert NULL in all rows of the [description] column.

What can I do to avoid this error and get the data transferred?

[KellyHobson · Accepted solution]

Hey @bilzekek 

Can you try adding the following internal parameter:

Manage endpoint -> Synapse endpoint -> Advanced tab -> internal parameters

Search for "syntax" and set value to.

"syntax" = "AzurePDWMaxLob"

Then try the task without the global transformation.

Thanks,

Kelly

[bilzekek]

Hi,

This does seem to do the trick, although it cuts the length of the string in the column to 4000. I dont suppose there is anything to do there?

Thanks

[lyka]

Hello,

Are you still using substr($AR_M_SOURCE_COLUMN_DATA,1,4000) 

If yes then that maybe the reason why its truncating the column to 4000

You can increase the change the limit lob size on your task settings

Thanks

Lyka

[bilzekek]

Hi Lyka,

I removed the transformation as instructed.

Where do i change lob size limit? It says it is unsupported on the table settings: "Table-level LOB column handling is not supported with the Microsoft Azure Synapse Analytics target endpoint."

[lyka]

Hello,

Go to task settings > target metadata

Thanks

Lyka

[bilzekek]

Thank you, will try this.

[MoeE]

Hi @KellyHobson,

The Synapse target limitations state that full lob replication is not supported. Is it okay to keep this setting on?

Regards,

Mohammed