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
LVL 1
cErasmusAsked:
Who is Participating?
 
HoggZillaConnect With a Mentor Commented:
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.
0
 
HoggZillaCommented:
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.
0
 
HoggZillaCommented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
cErasmusAuthor Commented:
I have a Master spreadsheet with all the columns, thanks I'll have a look.
0
 
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.
0
 
PedroCGDCommented:
Follow the tips of HoggZilla,
Here you have an example attcahed.

I hope it helped.
Pedro
www.pedrocgd.blogspot.com

Package-EE45-dtsx.txt
SSIS-Image.JPG
testStr.xls
0
 
cErasmusAuthor Commented:
Pedro,It still seems to fail???
SSISError.gif
SSISError2.gif
0
 
HoggZillaCommented:
>>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.
0
 
HoggZillaCommented:
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.

http://msdn.microsoft.com/en-us/library/ms137643(SQL.90).aspx
0
 
PedroCGDCommented:
Exectute the attached SQL script and update the excel and SQL connections
Helped?

See the solution of HoggZilla
Regards
CREATE TABLE [dbo].[Product](
	[ProductID] [varchar](50) NOT NULL,
	[PendingDay] [int] NULL,
	[StrTeste] [varchar](1000) NULL,
 CONSTRAINT [PK_ProductID] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
INSERT INTO Product
           ([ProductID] ,[PendingDay] ,[StrTeste])
VALUES (4,1,'sdsdsdsddddiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuuu')

Open in new window

0
 
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?
0
 
HoggZillaCommented:
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.
0
 
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.
0
 
HoggZillaCommented:
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.
excel-destination-columns.bmp
0
 
cErasmusAuthor Commented:
Thanks Hoggzilla, I'll have a look, thanks for all the info
0
 
cErasmusAuthor Commented:
Thanks for all the help, I decided that I would create a datasource in excel that connects to a view in sql.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.