Working with Excel in SSIS is not fun and something I whole-heartedly discourage. With that said, I had a not so uncommon issue that I thought I would blog, once I found the answer. The issue is with writing columns of data to Excel with a width>255 characters, more specifically varchar(max).
Whenever I need to export data to Excel within SSIS I like the approach of having a blank template that I copy to a work location, fill with data, change the name and ship it off. The issue I ran into was where I had to change a column name in one of my templates. Upon saving the template and re-running the DataFlow Task I got the error codes above. The complete message was as follows:
I delete the existing tab, called Excel_Destination, with a drop table statement. I make sure the Execute SQL Task has a connection type of EXCEL, a result set of type None, and the connection manager is tied to the Excel spreadsheet I am modifying.
The above statement will run and delete the offending tab. Now I have to rebuild it, so I do it with a create table statement with a twist; the backquote (`). The backquote surrounds the names of the columns. The table name is the tab name.
Now, you might have noticed that I have standard sql data types (varchar, char, int). It turns out that all translates to varchar(255). The only column of interest to me is the memo data type. That is synonymous with varchar(max).
So long as I don't resave that template, the DT_NTEXT field will stay. If you need to make a change to the column names, do it programmatically or you will get the above error messages. Hope this saves some of you the headache I went through :)