👀 Views: 100 💬 Answers: 1 📅 Created: 2025-06-10

azure-data-factory azure-synapse sql-server JSON

Could someone explain I'm trying to implement I'm learning this framework and I tried several approaches but none seem to work. I'm migrating some code and Quick question that's been bugging me - I'm working with an scenario with Azure Data Factory where the Copy Activity is not correctly handling NULL values during the transfer from an on-premises SQL Server to Azure Synapse Analytics. The source table contains several fields that can be NULL, but instead of inserting NULL into the target table, the pipeline fails with the behavior message: `Data movement failed for sink 'MySink': the value want to be null for column 'MyColumn'`. I've checked that the corresponding column in Synapse allows NULLs. In my pipeline, I'm using a Copy Data activity with the following configuration: ```json { "name": "Copy Data", "type": "Copy", "source": { "type": "SqlServerSource", "query": "SELECT Id, Name, NullableField FROM dbo.MySourceTable" }, "sink": { "type": "SqlDwsSink", "preCopyScript": "IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp; CREATE TABLE #Temp (Id INT, Name NVARCHAR(100), NullableField NVARCHAR(100) NULL);", "tableOption": "truncate", "tableName": "dbo.MyTargetTable" }, "enableStaging": false } ``` I've ensured that the data types between the source and destination match, but I'm not sure how to explicitly handle the NULL values during the copy. I've tried using the `isNull()` function in my SQL query to replace NULLs with a default value, but I still encounter issues. Is there a recommended approach to ensure that NULL values are properly transferred without causing errors? Are there any settings in the Copy Activity that I might have missed? Any insights would be appreciated! My development environment is macOS. Is there a better approach? The project is a application built with Json. Thanks for taking the time to read this! I'd love to hear your thoughts on this. Thanks for your help in advance! I recently upgraded to Json latest. Thanks for taking the time to read this!