I am calling a function which returns a comma-separated list of values as a single column. It is part of an overall stored proc that pulls multiple columns. I have no problems returning a good results set. My issues is that I'm attempting to export the results to Excel using SSIS. I was able to use the Data Conversion Transform to convert all the non-unicode fields to their unicode equivalent before selecting them in the destination configuration mapping. The only one giving me trouble is that function-based field. I am guessing it's the field length? I tried to substring the value using the derived column transform before I even attempted the conversion but that didn't work. I tried to convert it to DT_NText (the unicode text stream) which it seems to like ok. (No red X on the destination transform). However, when I run the package, it keeps failing with:
] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[InvoiceDetails ] Error: An error occurred while setting up a binding for the "BilledCodes" column. The binding status was "DT_NTEXT".
[InvoiceDetails ] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.