locked
Convert tab delimited strings into a table format in C# RRS feed

  • Question

  • User1793652459 posted

    Hello,

    I have some code that takes the SQL data from variables in a SSIS package and shows data in a tab delimited format.  Sometimes things don't lineup so I would like to convert it
    to a table format. Here is the tab delimited format code and it's result.

    Variables varCollection = null;
    string header = string.Empty;
    string message = string.Empty;

    Dts.VariableDispenser.LockForWrite("User::Message");
    Dts.VariableDispenser.LockForWrite("User::ObjectName");
    Dts.VariableDispenser.LockForWrite("User::ObjectType");

    Dts.VariableDispenser.GetVariables(ref varCollection);

    //Set the header message for the query result
    if (varCollection["User::Message"].Value == string.Empty)
    {
    var lastMonthYear = DateTime.Now.AddMonths(-1).ToString("Y");
    var currentYear = DateTime.Now.Year;

    header = "SQL Query Results:\n\n";

    header += string.Format("{0}\t\t\t\t\t\t{1}\n", "Object Name", "Object Type");
    varCollection["User::Message"].Value = header;
    }

    //Format the query result with tab delimiters
    message = string.Format("{0}\t{1}\n",
    varCollection["User::ObjectName"].Value,
    varCollection["User::ObjectType"].Value );

    varCollection["User::Message"].Value = varCollection["User::Message"].Value + message;
    varCollection.Unlock();


    //Here is how the result looks like which I send in an email:

    Object Name-------Object Type
    sp.MyProcedure----SQL_STORED_PROCEDURE
    dbo.mytable-----SQL_USER_TABLE

    I started the code for a table but am stuck on the looping part, where I need to see how many rows do I need to append. I can only do hard coded rows.

    DataTable table = new DataTable();
    table.Columns.Add("Object Name", typeof(string));
    table.Columns.Add("Object Type", typeof(string));

    // Here is how I can add hardcoded 2 rows but not sure how to add dynamic rows based upon the Dts.VariableDispenser collection, which could be 1- 50 in number.
    table.Rows.Add("sp.MyProcedure", "SQL_STORED_PROCEDURE");
    table.Rows.Add("dbo.mytable", "SQL_USER_TABLE");

    If you have any suggestions then please let me know. Thanks

    Tuesday, August 29, 2017 7:54 PM

Answers