Get Column Name and Left Pad zeroes RRS feed

  • Question

  • Hi,

     Can you please help with the requirement below,

    In SSIS Script component I am passing all fields of a table . Whenever there is a column named 'abc' or 'bcd'

    I want to left pad 8 zeroes and substring 8 characters from right. The source col is decimal but after transformation it should be string.

    this is my code. can you please help to achieve this requirement.

       public override void Input0_ProcessInputRow(Input0Buffer Row)
            String p_client = "ABC";
            String p_customer = "BCD";

            foreach (PropertyInfo p in Row.GetType().GetProperties())
                 if  (p.Name.Contains(p_client) || p.Name.Contains(p_customer))
                   p.SetValue(RIGHT(("00000000"+ (DT_STR, 8, 1252) CLIENT),8))

    Wednesday, September 12, 2018 2:10 AM

All replies

  • So, given a decimal value you want to get the rightmost 8 characters and then ensure there are 8 0s on the left? That seems like a really odd requirement.

    string someValue = "123456789.98765432";
    //Get the rightmost 8 characters
    var rightmostEight = someValue.Substring(someValue.Length - 8, 8);
    //Pad with 8 zeroes on the left - since you are getting the righmost 8 this means the entire result is 16 digits
    var final = rightmostEight.PadLeft(16, '0');
    If this is a decimal value and you want precision then you'll need to do that first and then pad left with however many zeroes you need to get to the width you want.

    Michael Taylor

    Wednesday, September 12, 2018 2:06 PM
  • Sorry for not stating the requirement clearly. Here it is,

    In source, it is a Decimal of length 8. But the actual value can be less than 8

    so it can have values like 1234, 4567,null etc. but when its moved to target , the cols has to be left padded with zeroes to make it length 8 and it should be a string. hence am left padding 8 zeroes and get 8 digits from right so it gives below results.

    So it changes to 00001234, 00004567,00000000.

    Hope its clear.

    Also, I am looking for the syntax to be used in SSIS script component ,

    i) loop thru each of the input cols and to check if  input column name is ABC 

    ii) if its ABC , then left pad zeroes and get values as mentioned above

    Thanks for your time.

    • Edited by Hello_123456 Thursday, September 13, 2018 12:44 AM
    Thursday, September 13, 2018 12:39 AM
  • Ok, to pad left a string use PadLeft.

    "SomeString".PadLeft(8, '0');

    That will ensure the string is 8 characters or longer. For a null string you'll need to take that into account as well.

    (myPossiblyNullString ?? "").PadLeft(8, '0');

    You mentioned this goes into SSIS so you're limited to what language features were available for .NET 4.5 IIRC.

    I'm not sure why you'd enumerate the input columns looking for "ABC" rather than just getting it directly. I haven't remotely tested this but something along these lines come to mind.

    Row.ABC = ((Row.ABC as string) ?? "").PadLeft(0);

    I believe the auto-generated buffer class that you get has a properly typed property for each of your columns so you can get and set the value directly. If not then it has the corresponding GetString(columnIndex) that you can use. I recommend you post this type of question in the SSIS forums. 

    In the above case I'm assuming your column is actually a string. That is based upon your original code. If it is really a decimal then above code (or your original) wouldn't work at all because it is a decimal. In either case you'll first need to convert the decimal to a string using ToString and then use PadLeft.

    Finally note that PadLeft doesn't truncate so if your column value is > 8 characters (including decimal) then it'll be longer than 8 characters.

    Michael Taylor

    Thursday, September 13, 2018 2:17 AM