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.

Nez

Is the data type for recommendations varchar(max) in sql? I'm surprised the memo column did not throw a unicode conversion error. – Hegira

Yes. Just realised my original post included a similar error for a different column. Sorry for any confusion. – Nez

So when I try to replicate this issue, I have an ntext column going into a varchar(max) column at the oledb destination - but it throws the error that it cannot convert between unicode and non-unicode types. Are you sure that your sql table is varchar(max)? Can you try converting the ntext column to text or converting the sql table to nvarchar(max)? – Hegira

Found a different version of the same package where a conversion to DT_WSTR of length 510 is done by a Data Conversion object. It seems to execute fine with this change, but I'm not entirely sure why. – Nez

SSIS mapping is the most hopeless of Microsofts implementations. Copying between 2 SQL Server databases of exactly the same version and exactly the same tables? Oh, we don't support our own Microsoft column types natively and you have to do some micky mouse operations between them to get it to work properly. It's a total joke. – Fulviah

@Fulviah This was almost 8 years ago for me, and I still remember how frustrating it was. If you're dealing with this you have my utmost condolences. – Nez

Answer 1

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).

Hegira

Answer 2

1. Change your data type in SQL nvarchar(100)
2. Use Derived Column to write expression that will convert the memo into unicode (DT_WSTR,4000)((DT_NTEXT)message)

3. Map with new derived column name

Fencesitter

This worked for me in a similar scenario with an Azure Datawarehouse destination table – Armistice

I changed the output table to nvarchar declaration using an oledb ms sql connection it was still a dt_ntext nightmare. When i switched to ADO.Net, everything worked. – Princedom

Answer 3

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.

Outside

This is a really big suggestion and James is being modest and too soft spoken in his reply. If you use OLEDB (I think in VS 2019 ssis, I don't remember prior versions working this way) your life will be a dt_ntext nightmare. If you use ADO.Net things will work as you expect. – Princedom

Again Microsoft making simple transfer of character fields a nightmare (first there was unicode, now there is dt_ntext). The next time someone tells you Artificial Intelligence is going to take our (developer) jobs, remember this thread. Microsoft can't even figure out what a simple character field is. – Princedom

Answer 4

this issue is due to ' (apostrophe ) in the data. remove the record that has apostrophe example: community's instead type communitys

Canvass

Answer 5

I was getting this error message when importing from a MySql db.

The fix was to change the column from nvarchar(500) to nvarchar(Max).

Donohoe