SQL Server Varchar To Oracle Varchar2

Hi All,

I'm having an irritating issue transfering data from SQL server to Oracle.

I recieve a fixed width file and insert it into a SQL server staging table. with fields of varchar(30). after insertion, I can run the following:




Code Snippet
select len(contact_surname)
from customer_data






and get varying lengths, 7, 5, 9, etc....

after a very simple data flow which takes the data from the SQL server source and inserts in to the Oracle Destination, the same query in oracle:




Code Snippet
select length(contact_surname)
from customer_data






gives me a length of 30 characters for every record. they've been padded out to 30 characters with spaces. the table in oracle is identical to the SQL server one in every way except for using oracle's varchar2 of course.

there is no extra transformation or anything happening in between systems. I would expect this to happen if the oracle table had fields of typ char() which would pad out to the length of the field, but not for varchar2.

SSIS appears therefore to be appending data in the move to oracle.....

[sarcasm]
or am I missing some magic function in SQL server that will tell me the REAL length of the field..?

[/sarcasm]

it seems awkward to transfer the data and then have to trim every field again.... any thoughts?