Thankyou @MartinJaffer-MSFT .
However, I'm getting the some issue when I tried to upsert data for larger datasets (500K rows approx.) using copy activity.
ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=-2,Class=11,ErrorCode=-2146232060,State=0,Errors=[{Class=11,Number=-2,State=0,Message=Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],''Type=System.ComponentModel.Win32Exception,Message=The wait operation timed out,Source=,'
I tried below options
Unchecking the Use Tempdb option to "Select user DB schema" to dbo schema.
Set "Write batch size" to 10000 rows
Set "Write batch timeout" to 00:30:00 (30 mins)
None of the options worked. :-(
Also, I'm not able to add Dynamic Content for Key columns textbox. I tried this to pass keys dynamically from parameters unfortunately no parameters were showing up in the dynamic content for Key Columns
Can you suggest???