Handling lossy data before character- set migration

I'd go straight SSIS myself. Besides having configuration, logging and error handling out of the box, you can get some really nice performance out of it.

You could probably save quite a bit of time by using something like EzAPI to script out the majority of the packages and then tweak the remaining 10%.

I recently used that approach to script out replicating ~150 tables from our Informix instance to SQL Server. Saved me a ton of time by getting the basics set up, all I had to do was fix some data type incompatibility between the systems. I'm behind on blogging about EzAPI but I could put together a sample if you're interested in the approach.

Edit

I put together an demo of replicating tables with SSIS EzAPI. You'd need to modify the values in ReplicateTables and then modify the query in GenerateTableList to ensure it's identifying all the applicable tables.

The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.

By way of example, this query shows you that the Euro symbol (0x80 in WE8ISO8859P1) becomes 2 bytes in AL32UTF8:

SQL>  select length(convert(chr(128),'AL32UTF8','WE8ISO8859P1')) from dual
  2  /
LENGTH(CONVERT(CHR(128),'AL32UTF8','WE8ISO8859P1'))
---------------------------------------------------
                                                  2
SQL>

To list all characters that will be affected by the change, you can use the following query:

with n as 
(
select level as c from dual
connect by level <= 255
)
select c as "WE8ISO8859P1 value",
       '"'||chr(c)||'"' as Character, 
       length(convert(chr(c),'AL32UTF8','WE8ISO8859P1')) as "New length"
from n
where length(convert(chr(c),'AL32UTF8','WE8ISO8859P1'))>1;

Unfortunately the maximum length a CHAR or VARCHAR string can be in Oracle is 4000 bytes. The only option available to you if characterset conversion pushes you over this limit is to convert the columns to use the CLOB datatype, but we warned - CLOBs are difficult to deal with and can present challenges.