I am trying to build an SSIS package that imports the identity values from one SQL table to another. I have an ADO .NET destination connection as the import is for SQL Azure although Im currently testing it in SQL 2008. The connection has RetainSameConnection
set to True. Before the import I have a SQL task that runs SET IDENTITY_INSERT activityDisplayGroup ON. This executes but then I get an error message at the data flow task.
[Destination - activityDisplayGroup 1 ] Error: An exception has occurred during data insertion, the message returned from the provider is: Explicit value must be specified for identity column in table 'activityDisplayGroup' either when IDENTITY_INSERT
is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
In the profiler I see insert bulk "dbo"."activityDisplayGroup" ([name] VarChar(200) COLLATE SQL_Latin1_General_CP1_CI_AS)
Is it at all possible to insert identies using the ADO .NET, if so what have I missed?
View Complete Post