The value violated the schema's constraint for the column

HI,

Problem Source:

In general we get this type of error when we try to move data from Sql Server table ( or any other Source) to Excel 2007, If any of column data having more than 255 character length the transfermation gets fail and it will throw the above error.

Reason:

We need to remember we are exporting to Excel 2007 ( Connect thorugh OLEDB Connection Manager) not to Excel 2003 which will connect thorugh Excel connection manager.

There are siginificant differences between OLEDB Connection Manager and Excel Connection Manager.


When we try to export excel 2003 SSIS will convert all source data types to corresponding SSIS data types, becuase excel connection manager internally uses excel driver for its funtionality.

The Excel Connection Manager uses the Microsoft OLE DB Provider for Jet 4.0 and its supporting Excel ISAM (Indexed Sequential Access Method) driver to connect and read and write data to Excel data sources

The Excel driver recognizes only a limited set of data types.

For example, all numeric columns are interpreted as doubles (DT_R8), and all string columns (other than memo columns) are interpreted as 255-character Unicode strings (DT_WSTR).

Integration Services maps the Excel data types as follows:

• Numeric double-precision float (DT_R8)
• Currency currency (DT_CY)
• Boolean Boolean (DT_BOOL)
• Date/time datetime (DT_DATE)
• String Unicode string, length 255 (DT_WSTR)
• Memo Unicode text stream (DT_NTEXT)

So when we export data into excel 2003, SSIS first get 8 rows as sample from source, if any column data length > 255 character length in those 8 sample rows the excel connection manager will assign DT_Ntext by default else it assign DT_WSTR 255.

you can check it by click Advanced Editor on Excel Destination task. In the advanced editor on the External column and Properies tab check on External column s list the Column > 255 will assign DT_Ntext data type.

Hence it work in Excel 2003.

But the case in excel 2007 is different because we connect excel 2007 by using OLEDB connction amanager. It doesn't support DT_Ntext data type. So it always assign DT_WSTR 255 irrespective of source column length. So transfer mation will fail and throw above error.


Solution:

Export to excel 2003 instead of excel 2007. It's may be dirty solution, but no option.
If you have more than 65536 rows try to split into multiple sheets.

Note: Excel 2003 supports only 65536 rows per sheet. while Excel 2007 supports 10,00,000 rows per sheet.

Its all my experience only, it may be worng.

Please place any Suggestions and Comments .

Thanks,