BUG: SQL-DMO BulkCopy Object IncludeIdentityValues Property Fail (167867)

SYMPTOMS

When inserting into a SQL Server table using the SQL Server Distributed Management Object's (SQL-DMO's) BulkCopy object, automatically incremented values are inserted into the table's identity column based on the identity column's seed. The values in the source file are not inserted into the identity column. This occurs even when BulkCopy's IncludeIdentityValues property is set to True.

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

REFERENCES

For more information about the BulkCopy object's IncludeIdentityValues property, query on "IncludeIdentityValues" in SQL Server Books Online.

For sample code using the BulkCopy object, see the following article in the Microsoft Knowledge Base: