I'm using Microsoft Codename "Data Transfer" to transfer data from an Excel (or cvs) file to a SQL Azure database. Everything runs through normally, but when it comes to uploading the data this error is thrown 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 [x]
I've tried several ways of setting up the column mappings in the analyze part of the wizard which include (and all result in the above error:)
- Mapping an Id column in Excel (which is null) to the Id column in the database
- Mapping the RowId(auto-generated) column to the Id column in the database
This will be an awesome tool once it's working.... or at least idiot-proof!
Update:
The obvious solution is not mapping anything to the Id column and this is the first thing I tried, but it is not allowed to leave the Id unconnected.
