BUG: Package Execution Can Fail if Target Table Has an Identity

ID: Q238673

The information in this article applies to:

Microsoft SQL Server version 7.0

BUG #: 56157 (SQLBUG_70)

SYMPTOMS

If your destination table has an identity property and you have not selected the Use fast load option but have selected Enable identity insert in your Data Transformation Services (DTS) package before you execute the package, the package will fail with the following error message in the SQL Server 7.0 licensed version:

Error at Destination for Row number 1. Errors encountered so far in this task: 1.

Errors occurred.

If you execute the same package in SQL Server 7 Service Pack 1, you will get:

Error at Destination for Row number 1. Errors encountered so far in this task: 1.

Insert error, column 1 ('ColumnName', DBTYPE_I4), status 9: Permission denied.

WORKAROUND

The problem can be avoided by selecting both Use fast load and Enable identity insert during DTS operations. Both options are found on the Advanced tab of the Data Transformation Properties window.

NOTE: If you have triggers defined with your destination table, the triggers will be ignored when Use fast load is enabled.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words:

Keywords : kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug