Copy Activity Fails because of a Type conversion Failure

Question

I am trying to use a copy activity to move data between two tables that are partitioned by a column called CaptureDateOnly, which is a Date cast of a column Called CaptureDate.

When i am trying to execute the copy activity, i am getting the following error:

ErrorCode=TypeConversionFailure,Exception occurred when converting value '09/30/2025 00:00:00' for column name 'CaptureDateOnly' from type 'Date' (precision:, scale:) to type 'String' (precision:, scale:). Additional info: Specified cast is not valid.",

I am not sure why i have a value of 09/30/2025 00:00:00 in my date CaptureDateOnly , and why i am having a conversion between Date and String, if both my tables have a Date Type for the CaptureDateOnly as can be seen in Mapping tab.

Tried a few values for the Date and DateTime format in the Conversion type settings however i didn't get the copy working.

Is there any way i can have it working. Thanks

Answer 1

Hi EL Jawad, Mohammad

It sounds like you're running into a type conversion error during your copy activity. The error message you shared indicates that the system is struggling to convert a value from a Date type to a String type, which can be quite frustrating. Here's what you can try to resolve this issue:

Check Column Mapping: Double-check the column mapping settings in your copy activity. Ensure that both source and destination tables are correctly configured with the same data types for the CaptureDateOnly column. It sounds like both should be Date types, so make sure there are no overrides or incorrect settings in the mapping tab.

Data Format Issues: Since the error mentions a specific date 09/30/2025 00:00:00, verify how dates are formatted in your source system. Ensure that there are no unexpected or varying formats in your data, as this could lead to conversion issues.

Fault Tolerance Settings: You can enable fault tolerance in your copy activity to skip over rows that are causing conversion errors. This won't fix the underlying issue, but it allows the activity to continue processing other rows. More on this can be found in the fault tolerance documentation.

Conversion Settings: You mentioned trying different values for Date and DateTime formats in the conversion type settings. Make sure you're using a format that matches the source data. You might want to look into the formatting guides for date types in Azure Data Factory.

Run Diagnostics: Use copy activity diagnostics to check for any underlying issues that might not be obvious. The troubleshooting guide for copy activity errors may provide additional context for your specific error.

If after trying these steps you're still blocked, here are some follow-up questions to help narrow down the problem:

• What type of data source are you copying from? (e.g., SQL Server, Azure SQL Database, etc.)
• Can you confirm the data types of both the source and destination columns for CaptureDateOnly?
• Are there any additional transformations or mappings applied to the data in your copy activity?
• Has this error occurred for all rows, or only specific values in your dataset?

Hope this helps get you back on track! Let me know if you have any other questions!

References:

• Copy Activity Fault Tolerance
• Troubleshoot Copy Activity Errors