SSIS job will not run (NETIQKB72023)

SSIS job will not run

This error is in the application log
failed with the following error:"Operation failed. The index entry of length 1028 bytes for the index'dim_object_idx' exceeds the maximum length of 900 bytes.". Possiblefailure reasons: Problems with the query, "ResultSet" propertynot set correctly, parameters not set correctly, or connection not establishedcorrectly.

Currently the only way to workaround this issue is to delete the index using the following steps.

1. Copy the index name from the error message (see bolded index name in the sample error message). 
   
   Example error message:
   
   failed with the following error:"Operation failed. The index entry of length 1028 bytes for the index 'dim_object_idx' exceeds the maximum length of 900 bytes.". Possiblefailure reasons: Problems with the query, "ResultSet" propertynot set correctly, parameters not set correctly, or connection not establishedcorrectly.
   
2. Insert the index name into following query:
   
   IF EXISTS ( SELECT '*'
   FROM sysindexes
   WHERE name = 'dim_object_idx' )
   
   BEGIN
   DROP INDEX dim_object.dim_object_idx
   END
   GO

Once these steps are complete, right click the SSIS job and select "start job at step" to confirm that the job will run successfully.

The length of the index entry exceeds the maximum length of 900 bytes.

SQL Server retains the 900-byte limit for the maximum total size of all index key columns. This excludes nonkey columns that are included in the definition of nonclustered indexes.

 

Formerly known as NETIQKB72023