I am using the Microsoft Codename "Data Transfer" to transfer some data from Excel to SQL Azure.
While it is trying to upload the data it throws this error (for each row)
Exception while loading data into Table: [dbo.Xxxx]. ErrorMessage: [Cannot insert explicit value for identity column in table 'Xxxx' when IDENTITY_INSERT is set to OFF.]. Error data is logged in the ErrorLogTable [ErrorLog_xxxxxxxxxxxxx] in the destination
database. RefId: [xxxxxxxx], Line# around 
In the analyze part of the wizard I have tried
to leave the Id field unmapped, which it won't let me do
mapping the Id column in my excel sheet to the Id column form my db - this throws the above error
mapping the RowId (auto-generated) bigint to my Id column form my db - this throws the above error
Any help is much appreciated and I'm looking forward to using this tool without any errors being thrown
Could you give us the repro file so that we can troubleshoot the issue? For example, upload to some public file share and tell me the link, if your data is not sensitive. It would be great if you can reduce the file size such that the issue can still be
If uploading to a public share is not feasible, could you copy some content from the xlsx into this forum thread?
Thanks for the repro. First of all, there would be error to import your file since "RetailPrice" column was marked as "IsRequired" but no default value provided. In order to proceed, I unmark the column as "NOT Required".
Then, I tried to repro the issue by importing the Excel file. However, the issue cannot be reproduced when I simply import the above file.
I have to add a column "Id" into the Excel file and map the "Id" column in Excel into the "Id" column in the SQL table. Then, I can repro the issue.
If I map the column "RowId (auto-generated)" to the "Id" column in the SQL table, the issue cannot be repro and all rows can be inserted. I tried both "Update Table" and "Replace Table" option.
Could you please confirm that this is the behavior you have observed? If not, you may try your scenario again, since the behavior may be changed in a latest deployment of our service.
As a workaround, can you (1) unmark the "Id" column in the SQL table as "NOT Identity", or simply (2) map the column "RowId (auto-generated)" to the "Id" column in the SQL table? Normally, if you set a column in SQL as "Identity", I believe you will accept
the auto-generated Id, right? Please tell us why if both workarounds do not work for you.
Thank you & I look forward to your solution to update my Lightswitch application which is hosted in Azure. Another quick question is, Microsoft data transfer seems to be a great tool for data upload, how about download, exporting data out from the cloud?
Will it be a feature of the product?