How to use a string content as a variable name in a SSIS C# script

Question

Hello everyone and thanks for your time and answers in advanced. Let me give you some context first: I'm working in a bank in a metrics project. We are re-engineering all the ETL processes and microstrategy dashboards of the commerce sector, they use a lot of non IT data sources and we have to map that info to IT centralized sources in SQL Server 2008R2 servers. For the ETL we are using SSIS.

I have an ETL for loans. Inside a data flow I gather all the information I need about loans, then from one particular table I got all the conditions that needs to be tested to classify the loan. The conditions table has this form:

sk_condition_name: varchar
sk_whatever: ...
...
where_clause: varchar(900)

In the "where_clause" column I have a where clause (duh!) that test some columns from the loan like this:

loan_type = x AND client_tipe = y AND loan_rate = z

Before I get deeper in this, I need to say that the example I'm giving is about loans, but the same goes for all the products the bank sell, like insurance or investment funds... And the conditions to classify the product can change in time. And one specific loan can be classified in multiple ways at the same time, each positive clasification writes a row in a specific table, that's why I need an asynchronous Script Component.

Where I was? Right, loans.. So in the ETL in get all the loans data and those where_clauses, in a C# Script Component we separate the clause with regular expressions, so we end up with 2 strings for every check that the clause was doing, using the example above I would end up with 3 pair of strings ("loan_type", "x"), ("client_type","y") and ("loan_rate",z).

And this is where the problem comes

I can't find a way in the script to use the first string content as the name of the row column, something like this is what I mean:

if Row.(string1.content()) = string2 then ...

Now the limitations:

• It's a bank, they don't like new things, so the tools I can use are those of SSIS.
• Changes in the model might be out of discussion, are out of discussion.
• I need this to be completely dynamic, no hardcoded conditions because of the changing nature of this conditions.
• I've search a lot for a solution to this but found non that works. This is my last resource.

I hope I have been decently clear in this, my first post ever.

Please please please help me!

Thank you very much!!

EDIT 1: To clarify..

My end result is generating a new row to be inserted in one particular table for each condition that tested positive. The information to be inserted and the target table are irrelevant to the problema in hands. The loan type, client and rate are just examples of what conditions test. My problema is that I can't use the string content as the name for the row's column.

Answer 1

You can do this with Reflection. Add "using System.Reflection;" to the namespaces - then you can interate with the following code:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{

    string sColumn = "Name";
    string sFind = "John";

    foreach (PropertyInfo p in Row.GetType().GetProperties())
    {

        string sval;

        if (p.Name.ToString() == sColumn)
        {

            sval = p.GetValue(Row, null).ToString();

            if (sval != sFind) 
            {
                //Do Stuff
            }

        }


    }

}

In This example I have hard-coded String1 (the Column to Check) to Name and String2 (the Value to Check) to John.