I have created a transform data task that inserts rows in SQL Server from
Microsoft Access 2002. The Access source table has an Autonumber field (that
I do not want to use) and the SQL Server destination table has an Identity
field for the primary key. Everytime I run the package, regardless of the
setting "Enable Identity Insert" option, the package fails with the
following error message:
"Cannot insert the value NULL into column 'MyPrimaryKeyField', table
'MyDatabase.dbo.TableName'; column does not allow nulls. INSERT fails."

How do I resolve this issue? Thanks in advance.

Have a look at this article

Problems With IDENTITY() and the DataPump task.
http://www.sqldts.com/default.aspx?6,107,293,7,1


--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community