Solved

Using Replace function in Derived Column -- SSIS

Posted on 2013-06-10
7
7,218 Views
Last Modified: 2016-02-11
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!
0
Comment
Question by:ravichand-sql
  • 5
7 Comments
 

Author Comment

by:ravichand-sql
ID: 39235514
Never mind! The former solution in my description works.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39237388
Convert that Column to Numeric datatype. It will automatically remove leading zero. and then convert it to string (varchar/nvarchar)
0
 

Author Comment

by:ravichand-sql
ID: 39237736
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 39240867
Hi,
Are you expecting an exact number of leading zeros, or it varies?  one zero, 2 zeros ...etc... ?
0
 

Author Comment

by:ravichand-sql
ID: 39240954
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
 

Accepted Solution

by:
ravichand-sql earned 0 total points
ID: 39271611
This will work,

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

Author Closing Comment

by:ravichand-sql
ID: 39286376
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now