In ADF how to avoid Empty string in Sink (copy activity NULL values in Source) Snowflake to Salesforce

Question

I am loading data from Snowflake to Salesforce using ADF. Int, Double and Date columns having NULL values in Source are being inserted as empty string in Sink (blob staging) by default resulting in pipeline to fail with error “the given value of type string cannot be converted to type date of the specified target column”.

• Please consider Salesforce connector don't allow Dataflows activities
• For securiry reasons I have to use staging only in Azure

Appreciate any input to resolve this.

Thanks!

I have tried to use Data Flows but the Salesforce Linked service don't allow dataflows.

Answer 1

When staging data from Snowflake to Azure Data Lake, it is better to use the Parquet file format instead of CSV. Parquet is a columnar storage format that is optimized for performance and space efficiency, and it can retain the data types of the original data. CSV file stores all data in string format. While coping from staged csv file, the empty string value is unable to convert to the required data type in sink. This is the reason for your error message. To solve this, below is the approach.

• Take two copy activities - One to copy data from snowflake to azure data lake- parquet file and other to copy data from parquet file to salesforce data.

Img1: First copy activity: Snowflake to Parquet file

Img2: Second copy activity: Parquet to Salesforce