Microsoft fabrics Copy Activity Copy on prem data to Delta table

[c_avu]

I'm copying on-prem server data into lakehouse (Microsoft fabrics Delta table)

sample table:

Id | Name |      Creation_Date             |        Update_Date             |

1 |  Pr1      |2025-01-01 09:42:00.000 | 2025-01-02 07:00:00.000 | 
1 |  Pr2      |2025-01-02 09:42:00.000 | 2025-01-05 07:00:00.000 | 

When i'm copying this table using the copy activity, I want to use Destination tab, "partition_columns" option on "Creation_Date" and save it in hierarchial order Year --> Month --> Day in sink Delta table.

I cannot use Creation_Date column directly in Dynamic expression,
Copy Activity --> Destination --> Advanced --> Partition Column --> adding using dynamic expression
@concat('/year', formatDateTime(Creation_Date , 'YYYY'))
Unrecognised expression:Creation_Date --> ERROR

Any idea how to do this using copy activity ?

Many thanks

[v-priyankata]

Hi @c_avu 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-priyankata]

Hi @c_avu 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-priyankata]

Hi @c_avu 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Hakuna_matata]

Hi @c_avu  ,

The error suggests that Creation_Date field might be treated as a string or a non-date type. Ensuring that the Creation_Date column is recognized as a datetime field in the source may help. 

After which we may use this expression to test

@concat('/year=', formatDateTime(item().Creation_Date, 'yyyy'), '/month=', formatDateTime(item().Creation_Date, 'MM'), '/day=', formatDateTime(item().Creation_Date, 'dd'))

Hope it helps!

If this solved your problem, please accept it as a solution!!