The value violated the schema’s constraint for the column(Cannot able to upload 4000 length data from Table to Excel 2007 using OLEDB Destination IN SSIS)

[Origianlly posted by]: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de21545f-9733-4a86-bd54-3891fe08ce55/the-value-violated-the-schemas-constraint-for-the-columncannot-able-to-upload-4000-length-data?forum=sqlintegrationservices

Hi All,

I am not able to upload data into excel 2007. I have created package is SSIS and I have used OLEDB destination to connect Excel 2007 file. When I am trying to export data into excel 2007 its giving error like The Value voilated Constraint of the column. My table has one column URL with NVARCHAR(Max) Data Type. I have converted this column data type into NText data type using data conversion and from that conversion it will connect EXcel 2007 file.
The URL column contains minimum length of 2000 it means all rows are greater than 255 characters. I have verified the advaned tab of Excel 2007 destination connection it has taking DT_WSTR 255 by default. Can any one please help out how can we export data more than 255 characters in length. In my case there are no rows less than 255 character length.

Any Help?

Thanks
Satish


DataBase Designer

Tuesday, September 22, 2009 6:34 AM
Avatar of SatBI
Hi All,

After long time reserach I have found it doesnt possiable to move data from Table to Excel 2007 that having columns data more than 255 character. I have tried all the ways but not reached the result. But it will upload to excel 2003 with out any problem.

In simply When source column > 255 excel 2007 assigns data type DT_WSTR 255  no matter how your settings are, it results the transfermation fail.
When Source column > 255 ( in sample data first 8 rows) Excel 2003 assigns DT_Ntext, so the transfermation will succed.

In contarst excel 2007 doesn’t support DT_Ntext data type.

I did have used excel 2003 and make segregation my data into multiple sheets. I know its a ditry work, but i have no option.

As of I know SQL SERVER 2008 Integartion Services do support excel 2007.

Thanks,
Satish


SatBI

  • Marked as answer by SatBI Wednesday, September 23, 2009 12:44 PM
Wednesday, September 23, 2009 11:14 AM
Avatar of SatBI

I too ran into this limitation/bug or whatever. I searched the entire Internet to no avail. I did not want to use Excel 2003 as above.

My solution or hack is to “fake out” the Excel destination object by using an Excel file pre-filled with > 255 characters in the columns that may receive > 255 characters. The prefill data can be anything and only needs to be on row 2 assuming row 1 has headers.  You would also need to do the registry entry to change the sample data from 8 to 1 row. The Excel Destination columns then correctly set to Unicode text stream [DT_NTEXT] to allow SQL export of greater than 255 characters to those Excel column with no errors.  All my rectangles turn beautiful green when run in BIDS!

I simply hide row 2 so that the “pre-fill” data doesn’t show for my customers.  In case someone finds the hidden row I have used this text to explain: “Text fill to facilitate import to Excel column greater than 255 characters – ignore.” repeated enough to add up to >255.

Hope this helps someone.

Richard

  • Proposed as answer by Savorathon Thursday, October 15, 2015 2:21 PM
Saturday, May 16, 2015 2:37 AM
Avatar of SatBI

@Richnet31:

After spending many hours searching for solutions to this issue, I tried your “fake out” method and it worked great!  I’m glad you took the time to share your info.  Many many thanks.

One thing I should mention is that I didn’t need to edit the registry on my PC.  Just one sample row after the header was sufficient.

Cheers!

John K

  • Edited by Savorathon Thursday, October 15, 2015 2:25 PM
Thursday, October 15, 2015 2:25 PM
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s