CODEWITHSUNDEEP
azureazure-sql-databaseazure-data-factory
Pradeep Kaja
Pradeep Kaja

Reputation: 125

How to update a constant value into sql table field by using copy activity in azure data factory

I have a SQL table which contains different fields along with Load_date. I have data in CSV format and stored it in blob storage. Now the job is to copy data from CSV to SQL Table through azure data factory using copy activity. while performing this activity i want to dynamically populate the Load_date field because this fields is not available in CSV.

Upvotes: 0

Views: 1507

Answers (2)

AM07300222
AM07300222

Reputation: 121

If the 'Load_date' field means current time, you can try to set a default value by using: 'getdate()' for this field in target table like below: enter image description here

Thus you don't need to consider if source CSV file having this field or not, while rows inserting to target table, this field will filling with default value automatically.

Upvotes: 0

Martin Esteban Zurita
Martin Esteban Zurita

Reputation: 3209

You cannot do that unless you use Data Flows: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

If data flows are too expensive, you can first insert the data in the table and then with a Stored Procedure fill all the null values in this Load_date field with getdate() or whatever data you want.

I'll leave an example for your SP's update logic:

update [dbo].[YourTable] set [Load_date] = getdate() where [Load_date] is null

Then add a Stored Procedure activity in data factory, that will run after the copy activity finishes.

Hope this helped!

Upvotes: 0

Related Questions