SSIS job will not run (NETIQKB72023)

  • 7772023
  • 11-Jan-2010
  • 11-Jan-2010

Environment

Security Manager 6.x

SQL 2005

Situation

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.

Resolution

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.

Cause

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.

 

Additional Information

Formerly known as NETIQKB72023