BUG: SQL-DMO BulkCopy Object IncludeIdentityValues Property Fail

MORE INFORMATION

The BulkCopy object's IncludeIdentityValues property activates IDENTITY_INSERT so that a source file's values that map to an identity column in the destination table can be explicitly inserted into the identity column of a table. Records import properly, but the literal values in the source file are not inserted into the identity column; rather, new, incremented values, based on the initial property column's seed, are inserted.

SQLTrace reveals that IncludeIdentityValues property runs a SET IDENTITY_INSERT <tablename> ON in a different connection to SQL Server than the connection that runs the ImportData method. The SET command only applies to the current batch or connection.

For example, suppose you have a table with an identity column called Id, whose seed is 1, and a char column called MyChar:

   Id   MyChar
   --   ------
   1     A
   2     B
				

Setting the IncludeIdentityValues to True should import the following data

   Id   MyChar
   --   ----
   100   H
   101   I
				

into the SQL table like so:

   Id   MyChar
   --   ------
   1     A
   2     B
   100   H
   101   I
				

However, it fails to work properly, and actually inserts the data as:

   Id   MyChar
   --   ------
   1     A
   2     B
   3     H
   4     I