How to load data in Excel from SQL Server when one of the cells have to accept value more 255 characters

5
votes

I know it's a common issue, but I still didn't find a solution. I need to export data from SQL Server 2012 to an Excel destination using SSIS.

enter image description here

The value of one of the columns is going to be more that 255 characters long, so the cell in Excel has to accept more than 255 characters as well.

That's why I got an error.

I changed the registry key to 0, but it still doesn't work.

enter image description here

I tried to create a dummy column in Excel, but the data looks weird.

Are there any other solutions that I am not aware of?

1
sql-serverexcelssis
What do you mean by "one of the column has more than 255 characters"? I just put 1,087 characters in a single Excel cell, and it worked just fine. – Brian
Using Excel as a destination is probably the worst thing about SSIS. Can you use a text file and then open in Excel? It will save you this headache, and more in the future. – Jacob H
Its gonna be send out to a bunch of managers. I am sure its too much work for them – Serdia
@Brian the Excel driver attempts to read the cells in each column and "guess" at their length, based on the first few rows (20 by default I believe). Using that information, it configures the "maximum length" for each column. By default, it chooses 255. The workarounds are ugly. In every other SSIS connection you can explicitly define the column properties, but the Excel connection gives you literally no options or properties at all (except a single one to denote the first row as the header). OP has attempted to do a registry hack to turn the "number of guessed rows" to unlimited. – Jacob H
Perhaps this will help: timradney.com/2011/05/17/… – Jacob H

1 Answers

1
votes

This is what I usually do as a workaround in such cases.

I try to use SQL query as source, so this may work only in that case. So assuming you are picking up data from a view or table (say tbl)

so instead of 

select ID,Name,DetailDescription from tbl

do (assuming DetailDescription is the column which can contain huge data)

select 
   ID =NULL, 
   Name= N'', 
   DetailDescription =REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(N'__________','_','_____'),'_','_____'),'_','_____'),'_','_____'),'_','_____')
 union
select ID,Name,DetailDescription from tbl