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

7
votes

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.

5
sql-serverssis
which data type source are you passing to that destination column.? – Kishore
Very iritating error. If I remember correctly, I had to force refresh metadata of each source/transformation MANUALLY in flow to get it work. Force refresh source means: select some other table, save, select old table again, save. – OzrenTkalcecKrznaric
@Kishore There is no source, the offending column in destination have no source :-( – Oscar
@OzrenTkalcecKrznaric I have even deleted the whole data flow and recreated it with the same results! Also tried changing manually the data type in the Advanced Editor, without luck.. – Oscar
@Oscar When you say there is no source is this just an ignored non mapped column, that happens to be in the destination table, but is not actually being populated by the SSIS package? If so have you looked whether is is set to allow null values? – Peter_R

5 Answers

5
votes

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.

1
votes

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

1
votes

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.

0
votes

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.

0
votes

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.