CODEWITHSUNDEEP
azure-data-factory
Michael
Michael

Reputation: 511

How to use dynamic date in mapping of a REST dataset to SQL dataset

I get some data from Elasticsearch (via REST Dataset) and add it to a SQL Server Table. I want to add the acutal date via utcnow() to the datasets written to the SQL DB.

I´ve tried to add with @formatDateTime(adddays(utcnow(),-2), 'yyyy-MM-dd') in the field but this leads to "null" in the target database.

Upvotes: 1

Views: 45

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Have to say that your syntax for formatDateTime is correct. Per my understanding of your requirements,you want to add one more additional column (named nowDate)which is not involved by source dataset.I'm afraid that it is error condition which is listed here:

  • Source data store query result does not have a column name that is specified in the input dataset "structure" section.
  • Sink data store (if with pre-defined schema) does not have a column name that is specified in the output dataset "structure" section.
  • Either fewer columns or more columns in the "structure" of sink dataset than specified in the mapping.
  • Duplicate mapping.

However, since your sink is sql server,you could add column in stored procedure which could be executed in the copy activity.Please refer to this guide:https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server#invoke-a-stored-procedure-from-a-sql-sink

Upvotes: 1

Related Questions