Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Converting VARCHAR(max) to unicode for Excel export Expand / Collapse
Posted Tuesday, March 19, 2013 10:33 AM


Group: General Forum Members
Last Login: Wednesday, August 17, 2016 1:55 PM
Points: 148, Visits: 396
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:
[32]] 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 [32]] Error: An error occurred while setting up a binding for the "BilledCodes" column. The binding status was "DT_NTEXT".
[InvoiceDetails [32]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Post #1432768
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse