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?
MikeDigginsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
In Data conversion step, change "unicode string [dt_wstr]" 4000  to :
data type DT_STR instead
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.