OLEDB data source limit lenght of string to 4000

Question

 

Hello,

 I have 2 table, "table1" is the source one and the other one "table2" is the destination.

Columns in Table1 and in Table2 are nvarchar(max).

Data loaded from table1 is performed by SSIS OLEDB data flow source, I have found out that opening "Data flow Path Editor" in the Metadata, columns are as: DT_WSTR with lenght 4000.

 

First question:

Why SSIS limit the column to 4000.

 

Then I get some error for this issue, with the error:

input column "col1" (xxxx) and reference column named (coln) have incompatible data type.

 

As written before both columns are string, the problem is that SSIS limit the lenght of the string to 4000.

 

How can I solve this issue?

 

Thank

Answer 1

Hi,

 

The DT_WSTR datatype is defined as:

"A null-terminated Unicode character string with a maximum length of 4000 characters."

 

So that's why SSIS limits the column.

 

I think the best solution is to split the column up into several columns with a varchar(4000)

or try what happens if you change the datatype to DT_TEXT

 

Ellen

Answer 2

Have you tried using DT_NTEXT data type in SSIS:

http://msdn2.microsoft.com/en-us/library/ms141036.aspx

Answer 3

Hi are you developing an appication in c# ?

 if yes then please put  your code if it work i want to refere it My requirement is same as you mentioned !

thanks a lot