I am converting an Oracle CLOB defined field to SQL Max defined field. My input Oracle CLOB defined field contains datat larger than 8000 bytes for example SSIS brings in a field with 85,000 characters but only converts 100 bytes. In the SSIS Data Flow Advanced Editor the OLE DB Source Output External Columns shows Data Type as "text stream[DT_TEXT] and Length of 0 (zero). The SQL Command field in the Advanced Editor has been tested with a single query Select * from table_name of which all 85,000 characters are converted. However I use the subquery that I need, only 100 characters are converted from the field that contains any data larger than 100 characters. I can provide additional info but my main stab at this is to find out if anyone has experience using multi-query SQL in the SQL Command window of SSIS data flow and has experienced the data loss that I am experiencing? thanks
New info: if I run this query my LXDESC field is truncated to 100 bytes.
select * from (
SELECT LXOID, LXKIND, LXDESC
FROM v6dp1.LXDESC_67206C5B
WHERE lxoid = ' -2122313890')
if I remove the top query and only run the below query, I get all the data
SELECT LXOID, LXKIND, LXDESC
FROM v6dp1.LXDESC_67206C5B
WHERE lxoid = ' -2122313890'
Sounds very similar to existing question: OLE DB SOURCE truncation