FIX: 'Unspecified Error' Message Returned Placing a NULL in a Non-NULL Column (228933)

SYMPTOMS

When executing a Data Transformation from a SQL Server source table that contains a column with a NULL value to a column in a SQL Server destination table defined as NOT NULL, the following message occurs:

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

Although the behavior is expected, the error message does not supply enough information to resolve the problem.

WORKAROUND

Inspect row described in the error message in the source table to discover which column is being passed as NULL. Inspect the schema of the destination table to discover which column is defined as NOT NULL. Then apply one of the following workarounds:

Correct the destination table schema to accept NULL values on the specified column.
Ensure that NULL values are not passed to columns defined as NOT NULL.
Add a step to the transformation to inspect for NULL values and add a value to be passed to the column(s) defined as NOT NULL.
Add a default to the destination table column or columns defined as NOT NULL.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 1 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

232570INF: How to Obtain Service Pack 1 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

MORE INFORMATION

The service pack correction for the 'Unspecified Error' is to give a more specific error message under this condition. Passing a NULL to a column defined not to accept NULL values will continue to fail.

After applying SQL Server 7.0 Service Pack 1 or later, the following error message should be returned:

Error at Destination for Row number n. Errors encountered so far in this task: n. Insert error, column n ('table_name', 'data_type'), status 10: Integrity violation; attempt to insert NULL data or data which violates constraints. Unspecified error