how to convert sql rows into list of objects

iworkonline2 1 Reputation point
2021-03-16T18:11:53.99+00:00

Here is the sample data

Vendor-Return-PRocess.png

Vendor-Return-PRocess.png

I have to create SSIS package that will send emails that will contain 1 order per email. So based on the above sample, this package will send 2 send emails.

In my Control Flow I have Execute SQL Task and Script Task. From Execute SQL task the data is stored in a variable of type OBJECT and accessed in the Script Task.

My approach is to create Data table to store the data from OBJECT variable, and then loop through and store each order in a object. then when a new order is read then add the Order object in a list. I don't know how to detect when the current row has a new order.

Here is my C# code.

Code Begin

public class Order
        {
            public string chwhlo { get; set; }
            public string order_number { get; set; }
            public string claim_number { get; set; }
            public string po_number { get; set; }
            public string qty { get; set; }
            public string purchase_order_price { get; set; }
            public string external_charge { get; set; }
            //public List<Container> container { get; set; }
            public List<string> container { get; set; }
        }

public void Main()
        {
            // TODO: Add your code here

            try
            {

                OleDbDataAdapter oleDA = new OleDbDataAdapter();
                //DataSet ds = new DataSet();
                //ds = (System.Data.DataSet)Dts.Variables["User::RecordsObject"].Value;
                //oleDA.Fill(ds,"Mdata");
                DataTable dt = new DataTable();
                oleDA.Fill(dt, Dts.Variables["User::RecordsObject"].Value);
                //dt = ds.Tables["Mdata"];               


                //List<string> lst_mtcamu = new List<string>();
                StringBuilder subject = new StringBuilder();
                Order Ord = new Order();


                //Ord.chwhlo = "1";



                string previous = "-1";
                double purchase_order_price = 0.00;
                double external_charge = 0.00;
                int recordcount = 0;
                List<string> lstOrderNumber = new List<string>();

                List<Order> Ordlist = new List<Order>();
                string temp = null;


                foreach (DataRow row in dt.Rows)
                {

                    //    //string RN, Location, Container, Order_Number, ClaimNumber, PONumber, Qty, purchase_order_price, external_charge;
                    //    //Order Ord = new Order();
                        object[] array = row.ItemArray;

                    //Ordlist.Add(Ord.chwhlo.ToString());

                    if  (lstOrderNumber.Contains(array[3].ToString()) == false)
                    {
                        lstOrderNumber.Add(array[3].ToString());
                        Ord.order_number = array[3].ToString();
                        Ord.claim_number = array[4].ToString();
                        Ord.po_number = array[5].ToString();
                        temp = array[3].ToString();
                        MessageBox.Show(Ord.order_number.ToString());
                        //MessageBox.Show(array[3].ToString());
                        //Ordlist.Add(Ord);
                        //MessageBox.Show(Ordlist.Count.ToString());

                    }

                    if (temp == array[3].ToString())
                    {
                        Ord.container.Add(array[2].ToString());
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            Dts.TaskResult = (int)ScriptResults.Success;
        }

Code End

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,326 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,521 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,629 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Michael Taylor 51,341 Reputation points
    2021-03-16T19:04:35.44+00:00

    My first thought is that you don't need a script task for this. Once you have the SQL, grouped by claim number or whatever you're using to determine uniqueness, then you have the row(s) you need. Using one or more transform tasks you should be able to build out a table structure that has the information needed to then call the Send Mail Task via a foreach container.

    But assuming that you do need a script task then converting from a DataTable to a List<Order> is pretty straightforward. I do question whether it might be overkill though given that you could accomplish the same thing just sticking with the DataTable. But let's work with what you already have. I'm going to assume here that you are running a newer version of SSIS such that you can change the script task to run .NET 3.5 or higher so that LINQ is available. This makes it very easy to do. I'm also testing using the newer compiler so the script task compiler may not accept all this as is.

    To use LINQ you need to:

    • Change the target framework for the script task to .NET 3.5+
    • If not already done ensure the script task has a reference to the System.Core namespace
    //Get the rows grouped by the unique column(s)                
    //For each group create an order that sets the base order information from the first item and the rest as container
    var orders = from r in dt.Rows.OfType&lt;DataRow&gt;()
                         group r by r.Field&lt;string&gt;(&#34;Order_Number&#34;) into g
                         select new Order() {
                                order_number = g.Key,
                                claim_number = g.FirstOrDefault().Field&lt;string&gt;(&#34;ClaimNumber&#34;),
                                po_number = g.FirstOrDefault().Field&lt;string&gt;(&#34;po_number&#34;),
                                container = g.Select(x =&gt; x.Field&lt;string&gt;(&#34;container&#34;)).ToList()
                          };
    
    0 comments No comments