Using Replace function in Derived Column -- SSIS

Hello Experts,

I am trying to remove leading zeroes off of a source column when loading the data to a sql server table. I have to handle this in SSIS and hence using derived column transformation.

Initially I tried this:

(DT_STR,30,1252)REPLACE(LTRIM(REPLACE([Column 1],"0","")),"","0")

But, the above expression is removing all the zeroes from the record no matter where the zero is.

For Example: If I have "0012052400", it is transforming it into "12524"

I tried doing this:

(DT_STR,30,1252)(DT_I8)[Column 1]

This is giving me correct results, But if I get any text in that column then my package will fail. As the Column 1 has text and it cannot be converted to INT.

Please help me with a work around for this.

Thanks in advance!
ravichand-sqlAsked:
Who is Participating?
 
ravichand-sqlConnect With a Mentor Author Commented:
This will work,

(DT_STR,30,1252)REPLACE(LTRIM(REPLACE([Column 1],"0","")),"","0")
0
 
ravichand-sqlAuthor Commented:
Never mind! The former solution in my description works.
0
 
Alpesh PatelAssistant ConsultantCommented:
Convert that Column to Numeric datatype. It will automatically remove leading zero. and then convert it to string (varchar/nvarchar)
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
ravichand-sqlAuthor Commented:
PatelAlpesh,

Thanks for your reply.

Process will fail if I have any character data instead of numeric in the source column.

This will work:

(DT_STR,30,1252)REPLACE(LTRIM(REPLACE([Column 1],"0","")),"","0")


Thank you.
0
 
Jason Yousef, MSSr. BI  DeveloperCommented:
Hi,
Are you expecting an exact number of leading zeros, or it varies?  one zero, 2 zeros ...etc... ?
0
 
ravichand-sqlAuthor Commented:
Huslayer,

I got the fix for this question and mentioned it in my above comments. Thank you very much for your time. I appreciate it!!

Thanks!!!
0
 
ravichand-sqlAuthor Commented:
This solution did not work for me intially but this was not the issue causing problem. I had that other issue resolved and this code worked just fine.
0
All Courses

From novice to tech pro — start learning today.