SSIS: Cannot create an OLE DB accessor. Verify that the column metadata is valid

Question

I have an SSIS package that in hes OLEDB destination have an NVARCHAR(MAX) field. This field is not even been filled by the data flow. The data flow task fails with error "Cannot create an OLE DB accessor. Verify that the column metadata is valid".

I saw this similar question: How do I fix the multiple-step OLE DB operation errors in SSIS? and examined my metadata fallowing it's advice. I found that SSIS is mapping the offending column as NTEXT instead of DT_WSTR. I tried changing it's type to DT_WSTR with length 8000, but still get the same error. Also tried filling the field with NULL, same error. Setting "Validate External Metadata" to false didn't make any difference. Any suggestion about how to fix it?

Thanks.

Answer 1

I found another (possibly quicker) way to solve this, but it is a bit awkward. The caveat being that your data may be truncated. Whether it's a good idea to use it will depend on what the data is being used for.

Assuming the offending column output has been set to Unicode text stream [DT_NTEXT]. Add a second Data Conversion step after the first, put the outputs from the first conversion into the second and you can map from Unicode text stream [DT_NTEXT] to Unicode string [DT_WSTR] (length=4000). It will warn you of the possibility of truncation, but now you can use the output data from the second conversion.

Answer 2

Just for the records, I ended using a .Net Destination, where this bug doesn't happen.