How do I trim leading zeroes from a numeric field in my fklat file before loading to SQL table?

u can do this using derived column Ltrim(Replace([ColumnName], '0', ' '))

I thought Ltrim was for string fields? My field is numeric, precision 7, scale 2.

so cast it as a string: Ltrim(Replace(CAST([ColumnName] as varchar(10)), '0', ' ')) -Tab Alleman

Careful! The answer above is not yielding the correct result. E.g. when string 002002 used even the internal 0s get removed/replaced ! In the derived column you need to use the SSIS Expressions, not SQL. An expression like following is likely needed because since you have leading zeros the variable is of type string, not numeric: (DT_WSTR,10)(DT_I4)[StringVar] There you convert to to int and then back to string, thus effectively removing the leading 0s. Arthur My Blog

A data conversion component can be used.Nitesh Rai- Please mark the post as answered if it answers your question

But it is defined as numeric in my Mainframe program. PIC 9 (5) V99.

But once it hits the flat file it becomes "text".Arthur My Blog

So the expression would be (DT_WSTR, 7)(DT_NUMERIC)[ColumnName] ?

How did you define your column in the Flat File Source?Arthur My Blog

numeric, precision 7, scale 2.

And you get 0s when you see the data?Arthur My Blog

Every record goes to my Data Convert Error file with: -1071607673,270 270 is supposed to be the column but my files records are only 200 bytes so I don't know why it is saying column 270?

Can you capture a screenshot of this and publish it here (for how to see the general forum notes)?Arthur My Blog

A screenshot of ?

Enable data viewers to see what do you get at every stage. Capture screen-shots. Why don't you declare this column as string?Arthur My Blog

Should I have my codepage in the connection manager editor sset to 1140 (IBM EBCDIC - US) instead of the default 1252 (ANSI - Latin I) ?

I started to recall another topic by you. I told you there that mainframe machines typically produce EBCDIC formatted files. You need to know in what encoding and format you are getting the source file. If in EBCDIC, then you need to convert to ASCII before anything.Arthur My Blog

Ok, I have confirmed the source is in ASCII format, so what codepage do I chhose in the connection manager?

Use the default code page.Arthur My Blog

Please note: that the asking for help person posted into a new thread at http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/bdd56e41-e0cb-4d7f-9f4c-1255cdda6377Arthur My Blog