Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
Problems With IDENTITY() and the DataPump task.
By Allan Mitchell
Version 7.0
Level Beginner
Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7

Insert data from the source into the destination identity column

Below is the transformations tab for our DataPump task.

Fig 1

TransformationsScreen

Here you can see we've mapped the source columns one-one with the destination columns. Let's jump sideways for a second and examine what happens if we try inserting a row into our destination table using Query Analyser and T-SQL.

INSERT MyIdentityTableDestination(IdentityColumn, MyCharacterCol) VALUES(1,'A')
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'MyIdentityTableDestination' 
when IDENTITY_INSERT is set to OFF.

The error is raised because by default you cannot supply your own value for the column which has the IDENTITY property set. To prevent the error you need to change IDENTITY_INSERT property for the table.

SET IDENTITY_INSERT MyIdentityTableDestination ON
INSERT MyIdentityTableDestination(IdentityColumn, MyCharacterCol) VALUES(1,'A')
SET IDENTITY_INSERT MyIdentityTableDestination OFF
Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7