Hello,
I have encountered this kind of problem on several projects and I am sure it is from the dynamic schema.
In fact, the dynamic schema, despite its power, has this flaw.
It does not process columns of type max or varchar (max)
It sets a default value, it depends on the DBMS but on MSSQL it is 255. So, we try to insert a binary in this small column sometimes, it returns a "truncate data" type error or otherwise nothing depending on the config of DBMS
Here is an approach:
- First loading: You get the DDL from the source DBMS, you convert the types of columns and you create the table (With a tDBRow - you can also add Integrity Constraints or indexes with the same component - something that does not manage not the dynamic schema). Then you use the tDBInput -> tMap -> tDBOutput to insert only the data and not create the table. In the Action on the output component table parameter, leave "by default"
-Delta loading: ie the table already exists and you want to add a column. You get the two DDLs, you perform a comparison and you generate the delta. You create the new columns then the same as the previous step, you insert the data without creating the table
Here is if you have additional questions, do not hesitate 🙂 you will find examples of dynamic jobs in the site.
PS: you can use a java routines which takes as parameter a column with its type (ex: mysql) and which returns the formatting in plsql compatible with oracle. In this routine you will have multiple case when block code in java and you will do the conversion of all types
