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

Have SQL Server generate the values in the Identity column.

As we have just seen if we want to insert values into the IDENTITY column then we need to check the Enable identity insert on the last tab of the DataPump task. It follows that if we want SQL Server to generate the values then we need to remove the check. Go ahead and do that. If we leave the transformations as only having one between the characters columns then when we execute the DataPump we should see something similar to this. (N.B. We are emptying the destination after each example).

IdentityColumn MyCharacterCol
-------------- --------------
100            A

(1 row(s) affected)

Earlier we saw that if the IDENTITY_INSERT property was off and we explicitly told SQL Server to insert a value into the IDENTITY column then it failed (Page 2). We would expect the same to happen here as well, so go ahead and create a copy column transformation between the Source's SourceID column and the destination's IdentityColumn column as per Fig 1.

Now try the DataPump, and you can see the results below.

IdentityColumn MyCharacterCol
-------------- --------------
102            A

(1 row(s) affected)

So what happened? Well SQL Server has ignored the fact that we mapped a source column to the destination column and has generated its own IDENTITY value. The strangest thing is that it hasn't fired an error.

Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7