SSIS Access to SQL. Binding error: The binding status was "DT_NTEXT"

Question

I am trying to get an SSIS package inherited from a previous colleague to execute. The package pulls from an Access database and then puts the data into an SQL database.

One of the fields, let's call it 'Recommendations' is of the type 'memo' in the Access database. The column in the SQL output database is of the type varchar(max).

Error: 0xC002F446 at Data Flow Task, OLE DB Destination [218]: An error occurred while setting up a binding for the "Recommendations" column. The binding status was "DT_NTEXT". The data flow column type is "DBBINDSTATUS_UNSUPPORTEDCONVERSION". The conversion from the OLE DB type of "DBTYPE_IUNKNOWN" to the destination column type of "DBTYPE_WVARCHAR" might not be supported by this provider.

What confused me further is that a different column of type memo, which is also processed as DT_NTEXT, and is also placed into a varchar(max) data type in the SQL db, does not throw an error message. I have tried numerous conversion object types but have yet to successfully execute the package.

Answer 1

In my cases, I should transfer 'text' to 'nvarchar', the original data contains small data though. I could solve this by changing the connection way from OLE to ADO .net.

Answer 2

I was able to reproduce this error by doing the following:

• Change the datatype of the destination column to nvarchar(100)
• Make the incoming row from the dataflow be ntext with a length greater than 100

This causes the destination column to overflow and throw the error that you stated in your problem:

Error: 0xC002F446 at Data Flow Task, OLE DB Destination [2]: An error occurred while setting up a 
binding for the "myCol" column. The binding status was "DT_NTEXT". The data flow column type is 
"DBBINDSTATUS_UNSUPPORTEDCONVERSION". The conversion from the OLE DB type of "DBTYPE_IUNKNOWN" to  
the destination column type of "DBTYPE_WVARCHAR" might not be supported by this provider.

So what I think is happening for you is that the ntext column has a value that exceeds nvarchar(max) causing it to overflow.

In the previous version in which you convert the column to dt_wstr(510) - this works because you are probably truncating the ntext value to a size that will fit in the destination column. If the values do indeed fit into that size, then go with that as the solution. If your source values can be greater, than change the destination column in SQL to something that will fit. This can be ntext, but that is being deprecated, so it would be recommended to change this to varbinary(max).