Parsing variable length record to table columns where specific column is dynamic in SSIS 2016

Question

I have a table that has 45 columns for tax values

| Tax1 | Tax2 | .......... | Tax 44 | Tax45 |

I read in a variable length positional record that can contain zero to 90 values. The record is structured so that the first 3 characters are the tax code (values 001 - 045) and the next 7 characters are the tax value:

Examples:
0010013.990140005.00

0040002.00

0150001.150320002.200410014.250420012.990430000.500440001.750450004.68

What I would like to do is, for each record:

if ISNULL(record) or LEN(record) < 10 (3 characters for the code, 7 characters for the value)
    quit
else
    determine the amount of 10 character sections
    for each 10 character section
        taxCode = SUBSTRING(record, 1, 3)
        taxValue = SUBSTRING(record, 4, 10)

        table.Tax(taxCode).Value = taxValue (ex: using the first example record, column Tax1 will hold a value of 0013.99, Tax14 will be 0005.00)
    next section

    all other Tax[n] columns will have a value of 0.00
end if

Is there a way to do this without having to create 45 variables, one for each corresponding column?

EDIT: I apologize for the lack of clarity. I receive a flat file from our VMS database. This file has multiple record types per file (ie: IT01, IT02, IT03, IT04, IT05, IT06, IT07). Each record type is on its own line. I read this file into a staging table, which the record type from the data on the line. For example (this is the record type I am referring to in my question):

IT06404034001005.000031013.000

This gets loaded into my staging table as:

RecordType | RecordData                  |
------------------------------------------
IT06       | 404034001005.000031013.000

The RecordData field is then able to be broken down further as:

ItemNumber | RecordData             |
-------------------------------------
404034     |  001005.000031013.000

I am not sure what you mean by the term "read from variable". Are you reading this from a flat file? Also, I would store the data vertically with a key, tax type and tax Amount on each row. — KeithL, Jul 18 '19 at 19:37
I stand by my initial assessment. I would process it vertically and not have 45 tax columns (of which a max of 9 are filled out). I would go 3 columns wide. Key, Tax Code, And tax value. You could easily write them out in your foreach loop. — KeithL, Jul 18 '19 at 20:18
@KeithL I have edited my question to hopefully address your comment. — Kulstad, Jul 18 '19 at 20:19

score 0 · Answer 1 · answered Jul 22 '19 at 13:31

With a little bit of up-front work, I was able to create a script task to do exactly as I needed it to.

Step 1: add a script component. set it up as a transformation

Step 2: define all of the output columns necessary (long and tedious task, but it worked)

Step 3: put the following code in the script

public override void Input0_ProcessInputRow(Input0Buffer Row){
    int sizeOfDataSegment = 11; // size of single record to be parsed (item number/next price)
    string recordDetail = Row.RecordDetail.ToString().Trim();
    string itemNumber = recordDetail.Substring(0, 6);
    //System.Windows.Forms.MessageBox.Show(String.Format("Record Detail: {0}", recordDetail));


    // we need a record for every item number, regardless if there are taxes or not
    Row.Company = Variables.strCompanyName;
    Row.ItemNumber = itemNumber;

    if (recordDetail.Length > 6){
        string taxData = recordDetail.Substring(6);

        if (string.IsNullOrEmpty(taxData)){
        }
        else{
            if (taxData.Length % sizeOfDataSegment == 0){
                int numberOfTaxes = taxData.Length / sizeOfDataSegment;
                //System.Windows.Forms.MessageBox.Show(String.Format("Number of taxe codes: {0}", numberOfTaxes.ToString()));
                int posTaxCode = 0;

                for (int x = 0; x < numberOfTaxes; x++){
                    string taxCode = taxData.Substring(posTaxCode, 3);
                    string taxValue = taxData.Substring(posTaxCode + 3, 8);
                    string outputColumnName = "TaxOut" + Convert.ToInt32(taxCode).ToString();

                    //System.Windows.Forms.MessageBox.Show(String.Format("TaxCode: {0}" + Environment.NewLine + "TaxValue: {1}", taxCode, taxValue));

                    //using taxCode value (ie: 001), find and set the value for the corresponding table column (ie: Tax1)
                    //foreach (System.Reflection.PropertyInfo dataColumn in Row.GetType().GetProperties()){
                    foreach (System.Reflection.PropertyInfo dataColumn in Row.GetType().GetProperties()){
                        if (dataColumn.Name == outputColumnName){
                            if (Convert.ToDecimal(taxValue) < 0){
                                // taxValue is a negative number, and therefore a percentage value
                                taxValue = (Convert.ToDecimal(taxValue) * -1).ToString() + "%";
                            }
                            else{
                                // taxValue is a positive number, and therefore a dollar value
                                taxValue = "$" + Convert.ToDecimal(taxValue).ToString();
                            }
                            dataColumn.SetValue(Row, taxValue);
                        }
                    }

                    posTaxCode += sizeOfDataSegment;
                }
            }
            else{
                System.Windows.Forms.MessageBox.Show(String.Format("Invalid record length({0}): {1}", taxData.Length, taxData));
            }
        }
    }
}

Parsing variable length record to table columns where specific column is dynamic in SSIS 2016

1 Answers1