PRB: DTS Error "ValidateSchema Failed..." When You Run DTS Package If Tables Have Exact Same Structure (256040)

MORE INFORMATION

The DTSTransformFlags property is used to ensure data integrity for the Designation table. DTSTransformFlags can be set at various levels of strictness. The most restrictive is the constant setting of DTSTransformFlag_RequireExactType. DTSTransformFlag_RequireExactType requires the Source and Designation tables to have the same column type, length (including fixed versus variable length), scale, precision, sign and nullablity. If anyone of these values differs between the Source and the Designation tables, the error message shown in the "Symptoms" section occurs.

DTS determines if the column metadata is the same between the Source and Designation tables by calling the IColumnsInfo::GetColumnInfo method for the OLE DB providers. DTS then compares the DBCOLUMNFLAGS portion of the DBCOLUMNINFO structure that is populated by the method call. DBCOLUMNFLAGS is a bitmask that describes the column's datatype, length, nullabilty and other characteristics of the column.

DTS looks at the ISNULLABE and MAYBENULLABLE elements of the DBCOLUMNFLAGS bitmask to determine if the column is nullable. Some OLE DB providers always set the MAYBENULLABLE element regardless if the field is nullable or not, in the interest of performance. These providers cause DTS to fail on the exact comparison of the columns between Source and Designation tables, thus causing the "ValidateSchema Failed" error message. The Microsoft Jet Provider and Microsoft Access ODBC Driver are examples of a provider and driver that set the MAYBENULLABLE flag regardless of the query.
To determine the DBCOLUMNFLAGS settings for your provider perform the steps that follow.

NOTE: The example uses the Microsoft Jet 4.0 Provider.

1. Run the OLE DB RowsetViewer utility that is shipped with the Microsoft Data Access (MDAC) SDK.
2. Perform a full connect and specify Jet 4.0 as the provider and Northwinds.mdb as the DataSource.
3. Once a session is established, type Select * from Customers in the Session window.
4. Click the Command menu option, point to ICommand, click Execute, and then click OK in the dialog box.
5. Once the Rowset is created, click the Rowset menu option, point to IColumnsInfo, and then click GetColumnInfo.

The meta-data for each column displays. Note the MAYBENULL value.

Steps to Reproduce Behavior

To reproduce the error message, use these steps:

1. Run the following script on SQL Server to create a sample table:

   Use Pubs
   Create table test (Col1 varchar(25) not null, Col2 varchar(25))
   					

2. Create a table in Microsoft Access named Acctest that has the exact structure as the test table. Set the Required property for Col1 so that it does not allow nulls. Populate the table with some test data.
3. Create an ODBC data source name (DSN), which points to the SQL Server database and the Microsoft Access database.
4. Find the DTSEXMP3 sample that is shipped on the SQL Server 7.0 CD-ROM. The sample is located in the D:\MSSQL7\DevTools\Samples\Dts folder. In order to see the error message display, you must follow the instructions in the following Microsoft Knowledge Base article:

   240221 INF: How To Handle Errors in DTS Package and Step Objects

5. Run the sample.
6. The sample first requests the DSN of the Source table. Type the name of the DSN that points to the Microsoft Access MDB file.
7. The sample then request the DSN of the Designation. Type the DSN name for the SQL Server.
8. Once connected you then choose the SQL Statement option button.
9. In the Statement Window type the following:

   Select * from Acctest
   					

10. For the Designation Table name, type the following:

    Pubs..Test

11. Click OK.

The error message appears in the output window.

To make the sample work correctly change the following lines in the sample from:

.TransformFlags = DTSTransformFlag_AllowLosslessConversion
				

.TransformFlags = DTSTransformFlag_Default
				

This allows the sample to run without causing the error message and the transformation does occur.