[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Exporting Data from SQL2005 to EXCEL 2007

Posted on 2008-11-06
16
Medium Priority
?
2,965 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:cErasmus
  • 7
  • 7
  • 2
16 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22894981
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22895018
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
 
LVL 1

Author Comment

by:cErasmus
ID: 22895024
I have a Master spreadsheet with all the columns, thanks I'll have a look.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:cErasmus
ID: 22895107
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22895243
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
 
LVL 1

Author Comment

by:cErasmus
ID: 22895646
Pedro,It still seems to fail???
SSISError.gif
SSISError2.gif
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22895649
>>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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22895689
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
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22895720
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
 
LVL 1

Author Comment

by:cErasmus
ID: 22895909
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22896075
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
 
LVL 1

Author Comment

by:cErasmus
ID: 22896229
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
 
LVL 17

Accepted Solution

by:
HoggZilla earned 1500 total points
ID: 22901492
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22901521
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
 
LVL 1

Author Comment

by:cErasmus
ID: 22902758
Thanks Hoggzilla, I'll have a look, thanks for all the info
0
 
LVL 1

Author Comment

by:cErasmus
ID: 22920250
Thanks for all the help, I decided that I would create a datasource in excel that connects to a view in sql.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question