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

What to watch out for (Contd.)

If we make one change to the script and comment out the line that moves data into our identity column.

'**********************************************************************
'  Visual Basic Transformation Script
'************************************************************************

'  Copy each source column to the destination column
Function Main()
	'DTSDestination("IdentityColumn") = DTSSource("SourceID")
	DTSDestination("MyCharacterCol") = DTSSource("SourceCharacterCol")
	Main = DTSTransformStat_OK
End Function

Remember we included all columns in the transformations screen and we have now commented out the identity column line in the script. This is what the transformations screen now looks like.

Fig 6

NewTransformations

The thing to note here is that the Identity Column is included in our transformation. On the options tab if we check Enable identity insert then we get the following error if we execute the DataPump task.

Fig 7

ErrorNewTransformations

The way to fix this would be to go into the options tab and clear the Enable identity insert option, and remove the identity column from the transformations. You can do this by going to the screen in Fig 5 and selecting the Destination columns tab and removing the identity column from the selected list.

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