SSIS: Cannot create an OLE DB accessor. Verify tha

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 had a similar issue. I had a SQL field that changed from NVARCHAR(MAX) to NVARCHAR(4000), but gave me the same error you describe. Unbelievably frustrating. I was able to fix it by unmapping the column in the OLE DB destination that incorrectly lists the field as NTEXT. Then, go through each SSIS operation that came before the OLE DB destination and select Show Advanced Editor...then click refresh. After doing this for every prior step, I remapped the column and SSIS finally picked up that the column was now a DT_WSTR.

Answer 2

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 3

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

Answer 4

I had this same problem working from an ACCESS database so I used Derived Column, and made all the necessary fields (DT_STR, <>, <>) (with the length being the size of the table column and the code page being 1252). Not only did this work in ACCESS but also worked for an EXCEL source as well.

Hope this helps.

Answer 5

I ended up by using the .NET Destination but the real issue was that the target table columns were outdated.

Try to refresh them either by changing destination to another table and then to the previous one or directly by removing that action and then adding it back again with the correct mappings.