Microsoft Codename "Data Transfer" throwing error with the primary keys

Question

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 [2]

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

Answer 1

Hi TisSinFul,

I am also facing the same problem.  Have you found a way around with this tool?  

Best regards,

CT

Answer 2

Hello TisSinFul / CT,

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 reproduced.

If uploading to a public share is not feasible, could you copy some content from the xlsx into this forum thread?

Thanks,
Ming.
Data Transfer team.

---

Pak-Ming Cheung - MSFT

Answer 3

Hi Pak-Ming,

Thanks for helping out.  This is the table that I'm loading into.  It is made using LightSwitch.



Here is a portion of the content I tried to upload

Code	Name	Description	Item_Brand	Status	Category	RetailPrice	ReceiptLine	Type	ProductSize	UsagePeriod
AF01	Apex Swiffe		3	1	DRINK		Apex Swiffe	1	1BOX
AF02	Apex BeauTea		3	1	DRINK		Apex BeauTea	1	1BOX
AF04	Grassiq		3	1	DRINK	165	Grassiq	1		12
AF05	Grassiq 1 Box (Tea bag)		3	1	DRINK	120	Grassiq (Tea bag)	1		12
K001	Rejuvenating Cleansing Gel		5	1	DK-PRODUCT	108	Reju Cleansing Gel	1	150 ml	120
K002	Purifying Cleansing Milk		5	1	DK-PRODUCT	108	Puri Cleansing Milk	1

The error I get is "Cannot insert explicit value for identity column in table 'Items' when IDENTITY_INSERT is set to OFF."  I mapped the RowId (auto-generated) bigint to the Id field on my database.

Best regards,

Chun Te

Answer 4

CT,

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.

Thanks,
Ming.
Data Transfer Team.

---

Pak-Ming Cheung - MSFT

Answer 5

Ming Cheung,

Both of your suggestions are not possible to solve the problems that I am having. 

1. (1) unmark the "Id" column in the SQL table as "NOT Identity", or

- this is not possible as ID is set as an Identity by Lightswitch and is not possible to unmark this. 

2. (2) map the column "RowId (auto-generated)" to the "Id" column in the SQL table?

-This did not work.  Maybe because the SQL table needs to generate the ID by itself. It is not possible to consume the RowId auto-generated by "Microsoft Data Transfer".

Any other methods we can try? Thank you for your help

 

Answer 6

Are you using the small repro (you have given to me) or using your original data file?

I actually tried (2) with your small repro file. It can work.

Don't know whether there are anything missing for this different observation.

Thanks,
Ming.
Data Transfer Team.

---

Pak-Ming Cheung - MSFT

Answer 7

Dear Ming Cheung,

I am using the small repro that I given to you but still can't seem to get it working. Below is the print screen of my mapping, perhaps it helps to find what is the problem? 

Thank you.



Answer 8

Sorry for confusion.

After trying this again, I can repro the issue now. I have filed a bug to track the issue. 

Again, thanks for reporting this issue to us.

Thanks,
Ming.
Data Transfer team, Microsoft.

---

Pak-Ming Cheung - MSFT

Answer 9

Dear Ming,

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?

Thank you for your help again. 

Answer 10

In the short term, our focus remains to be on the "upload scenario" - uploading to either SQL Azure / Azure Blob storage.

Thanks,
Ming.
Data Transfer Team, Microsoft.

---

Pak-Ming Cheung - MSFT

Answer 11

Dear Ming,

Do you think that there will be a solution to my problem in the next few days? Sorry for the urgency. 

Thank you for your help again. 

Answer 12

CT,

We will try to fix this bug in our next deployment. However, I don't think that this issue can be fixed in a few days. In the mean time, could you try the following workaround (2 steps) first:

     1) Upload your Excel file into a *new* table (say, named "NoIdentity") via the "Data Transfer". Accept default settings and it will create a column named "ID".

     2) Connect to SQL Azure portal (or use the SQL management studio), execute the following SQL query:

INSERT INTO [Items] (col1, col2, col3, ...)
    SELECT col1, col2, col3, ... FROM [NoIdentity]

This will insert all data in the "newly-created" temporary table "NoIdentity" into the existing table "Items".

Here, "col*" is the name of the columns. Please don't include the identity column "ID" in the list of columns, since SQL server will automatically generate the value of the identity column.

      3) Delete the table "NotIdentity"     

We understand that this is a bit inconvenience. But I hope that you can try this workaround in the mean time.

Thanks,
Ming.
Data Transfer team, Microsoft.

---

Pak-Ming Cheung - MSFT