SSIS- Data Reader Source (data type ntext) to OLE DB Destination (data type [varchar] (8000))

Question

question

RoxyReg asked Apr 28, '10

SSIS- Data Reader Source (data type ntext) to OLE DB Destination (data type [varchar] (8000))

I have been trying to bring in fields from Lotus Notes db (this is my Data Reader Source). The datatype is ntext. My destination table is SQL server. I am having issues converting ntext to varchar (8000).

I used Data Conversion Transformation to first convert unicode into unicode string(DT_WSTR); then second time to convert DT_WSTR to DT_STR. But it will not let me allow the length to exceed 4000 in SSIS in Data Conversion Transformation. The fields have more than 4000. As a result, I am getting this error:
"Data Conversion failed while converting column "xyz" to column "Copy of xyz". The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target page".

Then, I also tried to: First to change ntext to Unicode text stream [DT_NTEXT]; then second to change DT_NTEXT to text stream DT_TEXT. However, The mapped fields in destination table in SQL serverhave datatype of [varchar] (8000) NULL. When I run the package, I get error code 20276:
Cannot create an OLE DB accessor. Verufy that the column metadata is valid.
I get this error even when I go to the Data Reader Source, Data Conversion Transformation and OLE DB destination and go to properties and set "Validate External Metadata" to False.

Any help is greatly appreciated!!

ssis lotus-notes

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-04-28T16:20:06Z

Queue answered Apr 28, '10

Can you once try getting into the advanced editor and editing the properties there? Suppose that may work out...

If it does no good help, then currently i can only think of writing a script component for this.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-28T16:46:03Z

RoxyReg answered Apr 28, '10

Where/how do I write a script component for this?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-04-28T19:12:36Z

Oleg answered Apr 28, '10

If your source data type is ntext then it means that it does not have 8000 bytes limitation, and therefore, converting it to varchar(8000) is not good for 2 reasons: ntext hints that there might be some unicode characters in the source data and 8000 bytes might not be enough to store it. Converting the data to nvarchar(4000) is not good either due to the 4000 characters limitation. The best option is to convert ntext of the source to nvarchar(max) in the destination. nvarchar(max) is the prefered data type to ntext used in previous versions of SQL Server. The advantages of nvarchar(max) are:

The limit on the number of characters is huge, consistent with ntext (about 2 billion bytes)
It is possible to use any varchar applicable functions directly on nvarchar(max) which is not possible to do with ntext.

Text, ntext and image data types are going to be removed from the future versions of SQL Server and therefore, it is a good time to start using varchar(max), nvarchar(max) and binary(max) instead.

Oleg

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2014-04-03T13:47:44Z

PrabhakarMSBI answered Apr 3, '14

For converting ntext to varchar you can use the Derived column SSIS Data transformation and update the expression, as shown below (DT_STR,8000)(Actual_ColumnName)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question