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:

[Excel Destination [418]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E21.

[Excel Destination [418]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

[SSIS.Pipeline] Error: component "Excel Destination" (418) failed the pre-execute phase and returned error code 0xC0202025.

After much digging it turns out that when I re-saved the template it changed the column definition from an

DT_NTEXT

 to a default

varchar(255)

.  Try as I might, I couldn't get Excel to change the column definition.  Now that I understood the problem all I had to do was look online for a solution.  I was sure other people had been through this and I was right.

What I discovered was all I had to do was create my own tab within a document, just like you would create a table within SQL Server.  The trick,which I was hung up on for hours was the use of the backquote (`) key.  I was under the impression that a singlequote (') was to be used and that in translating it to the webpage it was inadvertantly making the adjustment.  I've seen this numerous times in using cut/paste.  So how exactly did I create my new template?

I delete the existing tab, called Excel_Destination, with a drop table statement.  I make sure the Execute SQLTask  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 🙂