👀 Views: 420 💬 Answers: 1 📅 Created: 2025-06-15

azure-data-factory data-copy sql-server json

I'm using Azure Data Factory (ADF) to copy data from an on-premises SQL Server database to Azure Blob Storage. I have a derived column transformation in my pipeline that should replace null values with a default string, but it seems to be failing. My derived column expression is as follows: ```sql if(isNull(column_name), 'Default Value', column_name) ``` However, when I run the pipeline, I get an behavior: `The input column 'column_name' does not exist in the input schema.` After some investigation, I found that this scenario occurs when the column is indeed null in the source, causing it not to be included in the derived columns output. I tried to add a column mapping in the Copy Data activity, but it didn't solve the question. I also ensured that the column is present in the source dataset schema and is correctly referenced in the derived column settings, but still nothing works. Here’s a simplified version of how my dataset is defined in ADF: ```json { "name": "SQLServerDataset", "properties": { "linkedServiceName": { "referenceName": "MySqlServer", "type": "LinkedServiceReference" }, "type": "SqlServerTable", "typeProperties": { "tableName": "dbo.MyTable" } } } ``` And in the pipeline I am using: ```json { "name": "CopyPipeline", "activities": [ { "name": "CopyData", "type": "Copy", "inputs": [ { "referenceName": "SQLServerDataset", "type": "DatasetReference" } ], "outputs": [ { "referenceName": "BlobStorageDataset", "type": "DatasetReference" } ], "transformation": { "name": "DerivedColumnTransformation", "type": "DerivedColumn", "columns": [ { "name": "NewColumn", "expression": "if(isNull(column_name), 'Default Value', column_name)" } ] } } ] } ``` I also checked the ADF activity logs, but there were no additional details on the behavior. Has anyone encountered a similar scenario, or does anyone have a workaround for ensuring null values are handled correctly during data copies? Any help would be appreciated!