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
– VKarthik, Commented Nov 7, 2016 at 4:01
@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
– Nambu14, Commented Nov 7, 2016 at 12:44
@H.Fadlallah thank you for the input but it isn't what I need. — Nambu14
– Nambu14, Commented Nov 7, 2016 at 12:49
I have now understood the problem. I will post the update. I am analysing it. — VKarthik
– VKarthik, Commented Nov 8, 2016 at 2:22

john McTighe · Accepted Answer · 2016-11-08 16:29:14Z

2

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.

answered Nov 8, 2016 at 16:29

john McTighe

1,2817 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nambu14 Over a year ago

Thank you mate! I had to add some Trim() and ToLower() to get the ifs right, but it solved my problem!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related