I have an issue at hand right now similar to this but its the other way around.

I have an stored procedure which uses an openquery to query a linked server and brings a result set with one of the columns with string data more than 8000 characters. My target is to load this result set into an excel sheet (.xlsx)

The dev server is running ace12.0 driver and ssis 2008 r2. I used an execute sql task to create a table in the excel sheet with all the other columns as varchar(50) except the column that holds the large character data which is created with the datatype as memo since i did read somewhere that memo in excel maps to dt_ntext in ssis.

Now, when i use as OLE DB Source task to run the stored procedure and then check the output column datatype of the large data column returned by the stored procedure, it is dt_text.

I used a data conversion task to convert all the other columns into dt_wstr (unicode) and the large column into dt_ntext(unicode), i mapped the columns to the columns in the table that i created in the excel sheet using an excel destination task.

It gives me this strange error that i am just finding really hard to understand .. it is as following:

"Error: 0xC0202025 at Data Flow Task, Excel Destination [185]: Cannot create an OLE DB accessor. Verify that the column metadata is valid."

i can provide additional information if you need any

any help will be extraordinarily appreciated.