SSIS AND Derived Column

  • I have a dataflow task which pulls data from SQL table and dumps to csv file.

    I am having problem stipping off leading zero in csv file for the zipcode field. I tried the following syntax RIGHT("0" + (DT_STR,5,1252)ZipCode,5) in Derived column but its not working either.

    Any problem would be greatly appreciated.

  • If you really appreciate problems, I have some really juicy ones for you :laugh:

    Can you provide sample data - source and required output - please? I'm English & not really familiar with zip codes ... Is there a reason for storing the leading zeroes in the SQL Server table, by the way?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • For e.g, this is the example of zip code 07302. When going to csv, it becomes 7302.

    Zip code is standard 5 digit. Leading zero must present to make the zipcode valid.

  • OK, I think an easy way to do this is to put in a data conversion transformation and convert from your current string field to an integer - this will strip out the leading zeros without you needing to do any extra work at all.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • But I need the leading zero.

    07302 becomes 7302 in csv file But I need 07302 in csv file. Am I making sense here?

  • Ah - you said you were having a problem stripping off leading zeros and that confused me, but now I understand.

    So you need something like (completely untested)

    LEFT('00000', 5 - LEN(Zip)) + Zip

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • In the derived column? I am getting syntax error.

  • Do I have to do everything? I said it was untested and expected that you could work it out ...

    Turns out there is no LEFT function available, so you'll have to use SUBSTRING instead.

    SUBSTRING( "00000", 1, 5-LEN(zip)) + Zip

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply