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.

Your question isn't fully clear. What is the end result that you are striving for?Are you trying to get loan_type, client_type and loan_rate as three seperate columns? When you say C# Script, are you referring to Script Task or Script Component? — VKarthik, Nov 07 '16 at 04:01
Check this [link](http://stackoverflow.com/questions/40434886/how-can-i-get-the-column-value-in-script-component-in-ssis) i think this is what you are looking for — Hadi, Nov 07 '16 at 08:13
@VKarthik 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 type, client and rate are just examples of what conditions test. My problema is that I cant use one string content as variable name for the row's column. And it is a Script Component. Thank! — Nambu14, Nov 07 '16 at 12:44
@H.Fadlallah thank you for the input but it isn't what I need. — Nambu14, Nov 07 '16 at 12:49
I have now understood the problem. I will post the update. I am analysing it. — VKarthik, Nov 08 '16 at 02:22
Just so that we are on same page I am assuming your source data to be in the form Col1,Col2,..Coln,Condition1,Condition2..ConditionN. For a given row, the combination of conditions may vary and a particular row only will get inserted if the conditions match. Is that right? — VKarthik, Nov 08 '16 at 03:46
The source data is in the form of col1, col2, etc.. The conditions are all together in one column, I split them with regular expressions, and they refer to multiple columns. Then, as you said, the combinations of conditions may vary and a particular row will get inserted for every condition that matches — Nambu14, Nov 08 '16 at 12:26

score 2 · Accepted Answer · answered Nov 08 '16 at 16:29

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.

Thank you mate! I had to add some Trim() and ToLower() to get the ifs right, but it solved my problem! — Nambu14, Nov 09 '16 at 19:32

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

1 Answers1