How to avoid conversion of empty string to null during the Synapse pipeline copy activity

Question

Hello,

In synapse pipeline I use copy activity to take data from source to dedicated SQL Pool. Staging is enabled.

Copy activity runs successfully but source empty values are converted to nulls on target (string data type on source and target).

I guess the reason is enabled staging.

How to avoid this conversion from empty to null during the copy activity with staging enabled?

Thank you very much in advance!

Answer 1

Hi @Tamashevich, Tatsiana ,

Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per my understanding you are using polybase and trying to copy data to Synapse SQL pool with staging enabled and if your source data is having empty value, in sink it is loaded as NULL and you would want to know how to load that column value as empty string instead of Null. Please correct me if I my understanding is wrong.

To copy the empty string value as is to your Synapse SQL Pool, please enable useTypeDefault property under sink settings as shown below.

Ref doc: Use PolyBase to load data into Azure Synapse Analytics

Ref doc: USE_TYPE_DEFAULT = { TRUE | FALSE }

Hope this helps. In case if your settings are different than Polybase, please do share a screenshot of your copy activity sink settings.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

Thank you for your answer. It could help but i faced with another issue during the test.

I'm using defined mapping (json file) between source and target. With default data type I received errors when data was copying into columns with decimal data type ad empty value is not allowed: