Default Value in Copy Activity to Synapse DW not populating in sink

Question

1. Inside our dedicated SQL pool we have a staging table we are trying to load, and that table has a CreatedDateTime column.
2. We aren't allowed to use an expression as a default for the column in the table definition, so we are trying to populate the value before the load.
3. In our Copy activity in our load pipeline, for the sink, we populate the Default values Value field for the column with @utcnow(). It isn't populating in the table column in the SQL pool.
4. 

We also tried using a literal (2021-09-30) in the Vaule field of the Copy activity and had the same issue.
Note #1 - CreatedDateTime is defined as a varchar(50) since @utcnow() is a string.
Note #2 - CreatedDateTime does NOT exist in the source (a Parquet file in our ADLS Gen2 account.

The JSON for all of this:
{
"source": {
"type": "ParquetSource",
"storeSettings": {
"type": "AzureBlobFSReadSettings",
"recursive": true,
"enablePartitionDiscovery": false
}
},
"sink": {
"type": "SqlPoolSink",
"allowCopyCommand": true,
"copyCommandSettings": {
"defaultValues": [
{
"columnName": "CreatedDateTime",
"defaultValue": "2021-09-30T12:34:43.8216569Z"
}
]
}
},
"enableStaging": false
}

Our questions:

1. What are we doing wrong so that the Copy activity default that we are setting is not populating the field in the sink?
2. Is there some better way to handle setting a default creation timestamp in a table in the dedicated SQL pool?

Answer 1

Hi @Johnny Humphrey ,

Thank you for posting your query on Microsoft Q&A Platform.

defaultValues in Sink is not the right place to add values into additional column. defaultValues just Specifies the default values for each target column in Azure Synapse Analytics. see below screenshot for same or Click here to know more.

Since, your source data doesn't has "CreatedDateTime" column, you should add it as additional column under Source tab in copy activity and then in Mappings tab you should map that additional column with your sink column.

Please Note, When you add additional column in Source tab then you need to use "Copy method" as "Bulk Insert" under sink tab. Reason is, additional columns on source will not support "Copy command" & "PolyBase" copy methods. If still, you want to use these copy methods then enable staging.

Below screenshots show details implementation.

Source Tab:

Sink Tab:

Mappings Tab:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators