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

Question

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.

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.

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?

Answer 1

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