Copy activity throwing an error, when a auto incremented column is there on the Sink side

Shweta Chavan 40

Hello Team,

I am trying to copy data from Amazon S3 to Azure SQL database. On Azure SQL side I have set an autoincrement column(Row_Index) in a table, however the copy activity throws an error that "Column 'Row_Index' does not allow DBNull.Value". The Source doesn't have a Row_Index column.

I tried to delete the Row_Index from Sink schema ,the error still persist.

Is there a way to have an autoincrement column In Azure SQL, using copy activity, without getting an error.

Thankyou!

Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2024-05-16T00:56:07.85+00:00

Hi @Shweta Chavan Thank you for reaching out again.

Is this issue similar to https://learn.microsoft.com/en-us/answers/questions/1662087/how-to-copy-the-data-from-amazon-s3-to-azure-sql-d?page=1&orderby=Helpful&comment=answer-1516080#newest-answer-comment

If yes,

In your SQL DB Table, you can create an identity column that auto increments IDENTITY (Property) (Transact-SQL) - SQL Server | Microsoft Learn.

You would leave this column out of the mapping as SQL will be handling the value.

Regards,

Oury
Shweta Chavan 40 Reputation points

2024-05-16T17:20:13.5933333+00:00

Hey Oury,

No both the issues are unrelated. The first issue, is resolved the UTC timing was not captured correctly earlier.

For the auto increment issue, it still persists. I did try the workaround that you suggested to set the column to Identity and , removed the column from Sink schema. However the error still persists.

Thankyou!
Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2024-05-28T20:13:19.3933333+00:00

@Shweta Chavan I wanted to check on the progress of the above issue.?

Are you still facing the same issue?.

Regards,

Oury

1 answer

Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2024-05-20T19:43:17.34+00:00

@Shweta Chavan

Thank you for confirming that the issue https://learn.microsoft.com/en-us/answers/questions/1662087/how-to-copy-the-data-from-amazon-s3-to-azure-sql-d?page=1&orderby=Helpful&comment=answer-1516080#newest-answer-comment is resolved by fixing the UTC time.

For the copy activity throwing an error

If the data from source is coming as null, the SQL column is configured with not null, so it not accepting nulls.

Try using mapping dataflow look up activity, you can count the null values and redirect them.

Regards,

Oury
Please sign in to rate this answer.
Oury Ba-MSFT 19,726 Reputation points Microsoft Employee

2024-05-31T22:33:28.77+00:00

@Shweta Chavan

I wanted to check if the above answer were helpful. Please do comment below if you still need further assistance.

Regards,

Oury
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Copy activity throwing an error, when a auto incremented column is there on the Sink side

1 answer

Your answer