The expression could have a result value that exceeds the maximum size of a DT_WSTR

Question

Post reply

The expression could have a result value that exceeds the maximum size of a DT_WSTR

rams.kola

Mr or Mrs. 500

Points: 563
More actions
May 29, 2012 at 6:47 am

#392367

Hi,
i have one select query i.e from oracle so i take DataReaderSource and integrating my oracle query to the DataReaderSource using expression. When i am executing package it is throwing exception at DataReaderSource i.e "The expression could have a result value that exceeds the maximum size of a DT_WSTR". The problem is my oracle query containg more than 4000 characters.. so DT_WSTR size is only 4000 characters. so it exceeds the size of DT_WSTR...so what i have to do..????
Regards,
Ram

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Look into DT_NTEXT.

Integration Services 2005 Data Types

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

rams.kola Mr or Mrs. 500 Points: 563 More actions · Answer 2

I PASSED ORACLE QUERY USING EXPRESSION..SO BY DEFAULT THE EXPRESSION IN QUERY IS TAKING AS DT_WSTR DATATYPE...SO HOW CAN WE CHANGE TO DT_NEXT....

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

That is a derivation based on the query you're sending. The Oracle driver is reading that a Unicode string of length 4000 will be returned from the query. You'll need to modify the query you're sending to Oracle so that it represents a longer string data type.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

rams.kola Mr or Mrs. 500 Points: 563 More actions · Answer 4

is there any other option with out modifying the oracle query......???

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 5

You could try using an OLE DB Source instead of a DataReader source. Or you could try a different Oracle driver.

I have to ask though, why can't you change the query?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

akin.akinwumi Right there with Babe Points: 790 More actions · Answer 6

I think without changing the Oracle source query you need to modify the default output property that SSIS presents the columns from the data source.

Select the datareadersource that contains the query, go to the properties tab and select ' Show advanced Editor'.

In the advanced editor go to the 'Input and OutPut Properties' then change the DataType on the column to DT_TEXT or DT_NTEXT everywhere the column appears in the Source Output & Source Error Output. 🙂

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

akin.akinwumi (6/15/2012)
I think without changing the Oracle source query you need to modify the default output property that SSIS presents the columns from the data source.

That was my initial suggestion, to look into DT_NTEXT. It's possible I am misreading it but from the OP's later posts it appears the issue has to do with the data type coming back from Oracle, in which case it would need to be overridden in the query itself.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato