ntext columns truncated to 32K when processed by Integration Services

CE31572


I have an Integration services application that retrieves big strings (over 32k) from an ODBC data source and stores them in a SQL server 2005 database as ntext.
Somehow the strings are truncated to 32K. The app only copies the data from the ODBC datasource to the (OLEDB) target.
Also, some of the special characters in the input text are not properly translated.

Any ideas

MatthewRoche

Are you able to get "correct" results (all data, no problems with special characters) using the same ODBC driver and OLE DB provider with a different application (not SSIS) accessing the data

Is there an updated ODBC driver available for this source, or better yet, a native OLE DB provider

CE

I will test the ODBC driver directly (it is a Sybase 64 bit ODBC driver). I don't have an OLEDB driver for 64 bit servers.

CE

yes. It works fine. (retrieving from C#)

MatthewRoche

Are you testing from C# using the same ODBC/OLE DB combination as you're using from SSIS

Just making sure - although I have not seen your specific problem, back in the early days of .NET I saw a good number of "weird problems" (for lack of a better term) that came from using the OLE DB provider for ODBC before there was a native ADO.NET ODBC provider, so it seems possible to me that the underlying cause could be something similar here...

CE

yes. it is the same ODBC driver in both.

MatthewRoche

CE31572 wrote:

yes. it is the same ODBC driver in both.

I apologize for not asking my last question more clearly. Are you accessing the data through the same stack: the same OLE DB provider and the same ODBC driver.

CE

No. I wrote a C# app that compared the source (through the ODBC driver) with the destination.
(The original stack used SSIS OLEDB. C# uses the SQL Client)

This is how I discovered the differences

Phil Brammer

CE31572 wrote:

No. I wrote a C# app that compared the source (through the ODBC driver) with the destination. (The original stack used SSIS OLEDB. C# uses the SQL Client) This is how I discovered the differences

Right, so there are three different methods of connecting right there.

ODBC, OLE DB, and the SQL Client (which uses what ADO I don't know.)

MatthewRoche

CE31572 wrote:

No. I wrote a C# app that compared the source (through the ODBC driver) with the destination. (The original stack used SSIS OLEDB. C# uses the SQL Client) This is how I discovered the differences

What I am attempting to do is to narrow down where the problem resides. Please take this action:

• Update your C# application (or make a new one, whatever) to use the same OLDE DB provider AND the same ODBC driver as you are using through SSIS when you encounter this error.
• Run the updated (or new, whatever) application to see if the same truncation behavior occurs

This will tell us this:

• If the error behavior exists in your updated (or new, whatever) application, then we have verified that the problem is not SSIS, and is introduced by either the ODBC driver, or the OLE DB provider, or the combination of the two.
• If the error does not exist in your updated (or new, whatever) application, then we have verified that the problem is somewhere outside the connectivity stack, perhaps in SSIS.

Without you taking this action, I am not certain how else to assist you, as to this point we have been comparing apples to oranges.

CE

I used ODBC (sybase driver) for the source Sybase database AND SQL client for the output of the Integration Services