SSIS Script Component Input0Buffer method no GetName()?

Question

I am looking for a way to obtain my property names in a SSIS data flow task Script Component. I have been searching high and low only finding this. I have been trying to get this code to work, but I am too novice to understand what is happening here and I don't feel it is explained very well(no offense).

The source before this component is using a SQL query joining two tables. Inside the component, I would like to compare column to column. Then call an update method I created to use SqlConnection to perform the update.

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.TableALastName != Row.TableBLastName)

        // Call the update method if the last name did not match.
        this.UpdateRecord("TableBLastName", Row.TableALastName.ToString(), Row.TableAAssociateId.ToString());
    }
}    

private void UpdateRecord(string columnName, string change, string associateId)
{
    SqlConnection sqlConnection;
    sqlConnection = new SqlConnection(this.Variables.Connection);

    string updateQuery = "UPDATE [SomeDataBase].[dbo].[TableB] SET " + columnName + " = " + change + " WHERE [Associate_ID] = " + associateId;

    using (SqlCommand cmd2 = new SqlCommand(updateQuery, sqlConnection))
    {
        sqlConnection.Open();
        cmd2.ExecuteNonQuery();
        sqlConnection.Close();
    }
}

I would like to somehow get the PropertyName of Row.TableBLastName instead of having to hard code "TableBLastName" for each test I am doing, which will be a lot.

The problem is that the input buffer class does not have Property.GetName() This also means I can't add a method to the class to get the property names, as it is regenerated each run.

score 1 · Accepted Answer · answered Mar 20 '18 at 15:23

public Input0_ProcessInputRow(Input0Buffer Row)
        {
            Dictionary<string, List<string>> list = new Dictionary<string, List<string>>();
            List<string> propertyList = new List<string>();
            Type myType = typeof(Input0Buffer);
            PropertyInfo[] allPropInfo = myType.GetProperties();
            List<PropertyInfo> SqlPropInfo = allPropInfo.Where(x => !x.Name.Contains("AM_")).ToList();

            // Loop through all the Sql Property Info so those without AM_
            for (int i = 0; i < SqlPropInfo.Count(); i++)
            {
                List<string> group = new List<string>();
                foreach (var propInfo in allPropInfo)
                {
                    if (propInfo.Name.Contains(SqlPropInfo[i].Name))
                    {
                        // Group the values based on the property
                        // ex. All last names are grouped.
                        group.Add(propInfo.GetValue(Row, null).ToString());
                    }
                }

                // The Key is the Sql's Property Name.
                list.Add(SqlPropInfo[i].Name, group);
            }

            foreach (var item in list)
            {
                // Do a check if there are two values in both SQL and Oracle.
                if (item.Value.Count >= 2)
                {
                    if (item.Value.Count() != item.Value.Distinct().Count())
                    {
                        // Duplicates exist do nothing.
                    }
                    else
                    {
                        // The values are different so update the value[0]. which is the SQL Value.
                        UpdateRecord(item.Key, item.Value[0], Row.AssociateId);
                    }
                }
            }
        }

I separated the values from the two tables so there are two lists values from TableA and TableB. You can prefix the values from TableA with "AM_" or something distinct so you can use reflection to to get the properties with and without the prefix and find out which values belong to which table. Then I just loop through the properties and group the values with the properties from the target value (so those without the prefix "AM_") I then loop through the grouped list and compare the two values and if it's different, update TableA with the TableB values to match them

I see what your doing here, I have added a prefix to one table. This did work! I will need to test more, but I think this works! Thank you PJ! — Clint, Mar 20 '18 at 15:38

score 0 · Answer 2 · answered Mar 16 '18 at 13:25

0

You are already in SSIS so I will propose using that (no matter how quick I usually jump to C# to solve problems)

This is a classic conditional split scenario:

Do your test then run the results into a SQL Update statement.

answered Mar 16 '18 at 13:25

KeithL

5,348
3
19
25

I see what you are actually trying to do, but I am not sure how you would know which columns are supposed to match. – KeithL Mar 16 '18 at 13:29
Keith, I have a source that is a SQL query joining table A to table B on the ID before this script component. So, the script component is generating the Input0Buffer class with the columns form my select as properties. That's how I am comparing the columns. Hope this helps explain. – Clint Mar 16 '18 at 14:36

SSIS Script Component Input0Buffer method no GetName()?

2 Answers2