SSIS - How to Remove Leading,Trailing OR Both Zeros from String in SSIS Package

Scenario:

Sometimes we get the data from flat file and we want to remove Leading Zero, Trailing Zeros OR Leading and trailing both before insert into destination.

Solution:

I assumed that data we are getting is string type, coming from the source (Excel, Flat file etc.)
Use the Derived column Transformation to remove Leading/Trailing OR Both zero from the string. After removing Zeros you can
Cast to Any data type you want such as Numeric, Integer, float etc.

Here are expressions to remove Leading Zeros, Trailing Zeros or Both from string column.


Leading Zeros:
(DT_WSTR,50)(DT_I8)[YourInputColumn]


Trailing Zeros:
REVERSE((DT_WSTR,50)(DT_I8)REVERSE([YourInputColumn]))


Leading and Trailing Zeros:
REVERSE((DT_WSTR,50)(DT_I8)REVERSE((DT_WSTR,50)(DT_I8)[YourInputColumn]))