DSXchange

I have an varchar in source which converted to decimal by using cast function and another field is fload which i rounded to decimal using Round function and every thing else simple mapping, no transofrmations
if I put Update action as insert new update existing then the process is going on for ever and ever with warnings, but if I select update action as insert with out cleaning then a fatal error is occuring which says

QL_Tier..X_TIER.Out_Insert_TIER: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO STG_TIER(TierLevel, ClientID, EmpCount, EmpRate, RegionKey, status_ind) VALUES (?,?,?,?,?,?)
SQLSTATE=22003, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC SQL Server Driver]Numeric value out of range

Could some one help or suggest please

There's probably an adjacent message in the log showing you the values that were sent to replace the parameter markers in the INSERT statement.
Using your knowledge of the metadata, identify which numeric value was out of range. For example:

• TinyInt can not be larger than 127.
  
  SmallInt can not be larger than 32767.
  
  Integer can not be larger than 2147483647.

Ray Wurlod !
thanks for your suggestion, i observed all my input data is decimal but size 18 but output had been set to 4, i need to talk to my lead regarding this on monday, may be this is one prob, what i did is increased the size in my out put stage, but not in database, which i do not have permissin, it performed little better but lot of warnings, still the job is going on i will update you

You will never be able to squeeze a DECIMAL(18) number into a DECIMAL(4) column, unless the value of the number happens to be sufficiently small (good luck rather than good management). You have two, and only two, choices if you want to prevent the warnings..

• The target column must be made capable of handling larger numbers (that is, changed to DECIMAL(18)).
  
  The DataStage job must reject rows in which the value is too large for DECIMAL(4).