Exporting large text columns from SQL 2005 to Excel 2007 via SSIS

I'm using SSIS with the Office 12 Access Database engine to do this export; I've tried EPPLUS and that works well for small exports, but the large data volume here causes consistent timeouts.
I can use the DTSWIZARD package, except where the columns to be exported are large - VARCHAR(8000). These give sizing errors e.g. DTS.Pipeline reports a 4000 character restriction on the output - even when I edit to this length in the SQL creating the output.
Any tips on handling these large columns, please?
Who is Participating?
Raheman M. AbdulConnect With a Mentor Senior Infrastructure Support Analyst & Systems DeveloperCommented:
In Data conversion step, change "unicode string [dt_wstr]" 4000  to :
data type DT_STR instead
MikeDigginsAuthor Commented:
Thank you; I'd heard of this but not used it before. I'm still a bit confused, though; I created a simple package with DTSWIZARD and then used BIDS for a closer look. The package has two components; a data preparation task to create a table on the destination workbook and a data flow task. The columns are defined as VarChar(8000) on the preparation task and DT_STR, 8000, 1252 on the data flow. I'm also getting a "CREATE TABLE statement not supported" when I try to build the query in the preparation task. I'm wondering if I was being simplistic in using the wizard, or if I've missed a step somewhere; I've used DTS quite a bit but hardly touched SSIS until now.
MikeDigginsAuthor Commented:
While this works nicely for coluns 400 or less characters, it gave a lot of problems with longer ones because of Excel's insistence on Unicode format. Eventually solved by using a WCF service and pulling through data via client-side Word and Excel documents.
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.