Exporting Data from SQL2005 to EXCEL 2007

Hi Experts,

I'm trying to export a table (ImmediateToWeb) to an EXCEL workbook with a sheet(Items). As soon as the Extended Description column exceeds 255characters I receive an error, "The value violated the schema's constraint for the column".

 Why can't I export a field where it's length exceeds 255?

Thanks in advance
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve HoggITCommented:
Unfortunately Excel only recognizes a few data types. The string (DT_WSTR) will only go to 255. If you need longer you need to use DT_NTEXT.
How do you do this, you need to create your table in Excel with the suspect column defined as Memo.
Steve HoggITCommented:
In your Data Flow task to Excel, add a Data Conversion task to convert the SQL Column to DT_NTEXT. You might even get away with that without having to change the column in the actual Excel file (table). That might work.
cErasmusAuthor Commented:
I have a Master spreadsheet with all the columns, thanks I'll have a look.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

cErasmusAuthor Commented:
I have changed the column Extended Description's data type from nvarchar(2000) to ntext, now I get an error in the validation process [Excel Destination [4333]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Follow the tips of HoggZilla,
Here you have an example attcahed.

I hope it helped.

cErasmusAuthor Commented:
Pedro,It still seems to fail???
Steve HoggITCommented:
>>I have changed the column Extended Description's data type from nvarchar(2000) to ntext, now I get an error in the validation process [Excel Destination [4333]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
You probably need to recreate the destination table in Excel using TEXT as the column type. I wish there was another way. You might be able to format the column in Excel as type Text.
When all else fails, go back to the package wizard and let SSIS do the work for you. After the wizard successfully creates your package, copy the details back into your existing tasks.
Steve HoggITCommented:
Saving text data. When the Excel driver saves text data values to an Excel destination, the driver precedes the text in each cell with the single quote character (') to ensure that the saved values will be interpreted as text values. If you have or develop other applications that read or process the saved data, you may need to include special handling for the single quote character that precedes each text value.
Saving memo (ntext) data. To successfully save strings longer than 255 characters to an Excel column, the driver must recognize the data type of the destination column as memo and not string. If the destination table already contains data, the first few rows that the driver samples must contain at least one value in the memo column that is longer than 255 characters. If the package creates the destination table during package design or at run time, the CREATE TABLE statement must use LONGTEXT, or one of its synonyms, as the data type for the memo column.
Data types. 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     date (DT_DATE)
String     Unicode string, length 255 (DT_WSTR)
Memo     Unicode text stream (DT_NTEXT)

Data type and length conversions. Integration Services does not implicitly convert data types. As a result, you may need to use the Derived Column or Data Conversion transformations to convert Excel data explicitly before loading it into a non-Excel destination, or to convert non-Excel data before loading it into an Excel destination. In this case, it may be useful to create the initial package by using the Import and Export Wizard, which configures the necessary conversions for you. Some examples of the conversions that may be required include the following:
Conversion between Unicode Excel string columns and non-Unicode string columns with specific codepages.
Conversion between 255-character Excel string columns and string columns of different lengths.
Conversion between double-precision Excel numeric columns and numeric columns of other types.

Exectute the attached SQL script and update the excel and SQL connections

See the solution of HoggZilla
CREATE TABLE [dbo].[Product](
	[ProductID] [varchar](50) NOT NULL,
	[PendingDay] [int] NULL,
	[StrTeste] [varchar](1000) NULL,
	[ProductID] ASC
           ([ProductID] ,[PendingDay] ,[StrTeste])
VALUES (4,1,'sdsdsdsddddiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu')

Open in new window

cErasmusAuthor Commented:
Thanks Hoggzilla,

My sheet (Items) contains a lot of code and have functionality with other sheets, is there an easy way to change the column in Excel(Extended Desciption) so that it will take fields greater than 255 characters?
Steve HoggITCommented:
You can try this. Right click on your Excel Destination in the Data Flow task window. Select Advanced Edit. Go to the Input and Output Properties. Verify the External Columns property of the suspect column is set to DT_NTEXT.
Aside from that, I am not sure what your next steps would be aside from the above suggestions. Let me know how that works.
Also, did you try this to fake the process? Update some of the rows temporarily?
>If the destination table already contains data, the first few rows that the driver samples must contain at least one value in the memo column that is longer than 255 characters.
cErasmusAuthor Commented:
I tried that, after the first five rows where Extended Description < 255 the next one fails and the whole package. Thanks for all the help, I'll have a look again.
Steve HoggITCommented:
OK, here is what I did to get this to work for me. Mind you, it takes some patience. I went into the Excel worksheet and selected my insert area and created a new range, I called mine MyRange.
Then, I inserted a fake row at the end of the current records, in that last row I inserted a value of 1200 characters into the varchar column.
When I went into my data flow task I went to the Excel Destination and selected my newly created range. I noticed now that the column in question, that was varchar, now in the new range it is recognized as NTEXT. I ran the package and it wrote the results.
So, bottom line, it can work. Try to follow these steps, hopefully i didn't leave anything out, and you will get it, I'm sure.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve HoggITCommented:
Check out this picture. I have put my mouse over the Varchar255 column and now you see it is an NTEXT column. All has to do with the range and that extra row I put at the bottom with the 1200 chars.
cErasmusAuthor Commented:
Thanks Hoggzilla, I'll have a look, thanks for all the info
cErasmusAuthor Commented:
Thanks for all the help, I decided that I would create a datasource in excel that connects to a view in sql.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.