Progettazione dell'integrazione dei dati utilizzando Microsoft SSIS

1

Sto lavorando a un progetto di integrazione dei dati, in cui ho bisogno di estrarre i dati dal sorgente di Oracle e caricarli in un file XML. Il requisito è quello di ottenere l'elenco dei clienti e foreach cliente creare un file xml con i dati dei clienti e dei prodotti associati e i dati relativi al prodotto. Il modo in cui ho progettato come segue.

  1. L'uso di execute sql task ottiene l'elenco dei clienti e lo tiene nella variabile object.

  2. Uso del ciclo di loop del ciclo foreach attraverso ogni cliente.

  3. All'interno del ciclo foreach, ho come compito di script di connettermi a oracle usando odp.net e ottenere i dati richiesti e scriverlo su xml usando la classe xmlwriter. Il codice all'interno dell'attività di script è inferiore a

-

 try
            {
                using (XmlWriter writer = XmlWriter.Create("FilePath" + customer + "_" + serviceType + ".xml", settings))
                {
                    //XML Header
                    writer.WriteStartDocument();
                    writer.WriteStartElement("ATOBTO"); //end
                    writer.WriteStartElement("ATOBTOSSet"); //end
                    writer.WriteStartElement("ATOBTHEADER"); //end
                    writer.WriteStartElement("ACTIONID");
                    writer.WriteString(DateTime.Now.ToString("yyyyMMddHHmmss"));
                    writer.WriteEndElement();
                    writer.WriteStartElement("ATOBTSOURCE");
                    writer.WriteString("MOI");
                    writer.WriteEndElement();

                    //Connect To Oracle DB using ODP.net Driver this driver buffers data hence requires minimum RAM usage
                    con = new OracleConnection(moiConnection);
                    con.Open();

                    // Execute PL/SQL For Customer---Only one row will be returned
                    OracleCommand cmdCustomer = con.CreateCommand();
                    cmdCustomer.CommandText = sqlCustomer;
                    OracleDataReader readerCustomer = cmdCustomer.ExecuteReader();

                    while (readerCustomer.Read())
                    {
                        writer.WriteStartElement("ATOBTCUSTOMER");

                        WriteFullElementString(writer, "CUSTOMERID", readerCustomer["CUSTOMERID"].ToString());
                        WriteFullElementString(writer, "CUSTOMERNUMBER", readerCustomer["CUSTOMERNUMBER"].ToString());
                        WriteFullElementString(writer, "CUSTOMERNAME", readerCustomer["CUSTOMERNAME"].ToString());
                        WriteFullElementString(writer, "CUSTOMERSINCE", readerCustomer["CUSTOMERSINCE"].ToString());
                        WriteFullElementString(writer, "CUSTOMERSOURCE", readerCustomer["CUSTOMERESOURCE"].ToString());
                        WriteFullElementString(writer, "SERVICEGROUP", readerCustomer["SERVICEGROUP"].ToString());
                        WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME","SERVICECATEGORY", readerCustomer["SERVICECATEGORY"].ToString());
                        WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME", "PORTALNUMBER", readerCustomer["PORTALNUMBER"].ToString());
                        WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME", "SERVICE_CODE", readerCustomer["SERVICECODE"].ToString());
                        WriteSpec(writer, "ATOOBTCUSTOMERSPEC", "ATSPECIFICATIONNAME", "SERVICE_DESCRIPTION", readerCustomer["SERVICEGROUP"].ToString());

                        writer.WriteEndElement(); //ATOBTCUSTOMER
                    }
                    // Clean up CustomerCommand
                    readerCustomer.Dispose();
                    cmdCustomer.Dispose();


                    // Execute PL/SQL For CircuitCI --  100 to 1000's of rows can be returned
                    OracleCommand cmdCircuitCI = con.CreateCommand();
                    cmdCircuitCI.CommandText = sqlCircuitCi;
                    cmdCircuitCI.InitialLOBFetchSize = -1;
                    OracleDataReader readerCircuitCI = cmdCircuitCI.ExecuteReader();
                    readerCircuitCI.FetchSize = cmdCircuitCI.RowSize * 100;

                    //Write CircuitCI XML
                    while (readerCircuitCI.Read())
                    {
                        writer.WriteStartElement("ATOBTCI"); //end
                        WriteFullElementString(writer, "CICLASSIFICATION", "6103");
                        WriteFullElementString(writer,"CINUM", readerCircuitCI["CINUM"].ToString());

                        //CLOB DATA--Call method to clean up invalid xml charecters
                        string notes = CleanInvalidXmlChars(readerCircuitCI["notes"].ToString());
                        WriteFullElementString(writer, "CIDESCRIPTION_LONGDESCRIPTION", notes);
                        WriteFullElementString(writer, "CUSTDEV", readerCircuitCI["CUSTDEV"].ToString());
                        WriteFullElementString(writer, "INSTALLEDDATE", readerCircuitCI["INSTALLEDDATE"].ToString());
                        WriteFullElementString(writer, "STATUS", "OPERATING"); 

                        // Execute PL/SQL For ServiceAttributes -- Less than 10 to 30 rows for each serviceid 
                        OracleCommand cmdServAttr = con.CreateCommand();
                        cmdServAttr.CommandText = sqlServiceAttr + " AND SERVICEID=" + readerCircuitCI["SERVICEID"].ToString();
                        OracleDataReader readerServAttr = cmdServAttr.ExecuteReader();
                        readerServAttr.FetchSize = cmdServAttr.RowSize * 100;

                        //Write ServiceAttributes XML
                        while (readerServAttr.Read())
                        {
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", readerServAttr["Name"].ToString(), readerServAttr["Value"].ToString());
                        }

                        // Clean up ServAttrCommand
                        readerServAttr.Dispose();
                        cmdServAttr.Dispose();

                        // Execute PL/SQL For CircuitAttributes  Less than 10 to 30 rows for each circuitid
                        OracleCommand cmdCircAttr = con.CreateCommand();
                        cmdCircAttr.CommandText = sqlCircuitAttr + " AND CIRCUITID=" + readerCircuitCI["CIRCUITID"].ToString();
                        OracleDataReader readerCircAttr = cmdCircAttr.ExecuteReader();
                        readerCircAttr.FetchSize = cmdCircAttr.RowSize * 100;
                        //Write CircAttr XML
                        while (readerCircAttr.Read())
                        {
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", readerCircAttr["Name"].ToString(), readerCircAttr["Value"].ToString());
                        }

                        // Clean up CircAttrCommand
                        readerCircAttr.Dispose();
                        cmdCircAttr.Dispose();

                        // Execute PL/SQL For ContractSLA -- One row for each serviceid
                        OracleCommand cmdContractSLA = con.CreateCommand();
                        cmdContractSLA.CommandText = sqlContractSla + " AND SERVICEID=" + readerCircuitCI["SERVICEID"].ToString();
                        OracleDataReader readerContractSLA = cmdContractSLA.ExecuteReader();
                        readerContractSLA.FetchSize = cmdContractSLA.RowSize * 100;
                        //Write ContractSLA XML
                        while (readerContractSLA.Read())
                        {
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "HDCONTROL", readerContractSLA["HDCONTROL"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "MTTRTARGET", readerContractSLA["MTTRTARGET"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "RURALMTTR", readerContractSLA["RURALMTTR"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "SLAINFOURL", readerContractSLA["SLAINFOURL"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "SLASIGNED", readerContractSLA["SLASIGNED"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "UPDATEINTERVALMINUTES", readerContractSLA["URBANMTTR"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "URBANMTTR", readerContractSLA["MTTRTARGET"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "SEGMENT", readerContractSLA["MTTRTARGET"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "STATUS", readerContractSLA["MTTRTARGET"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "CONTRACTTYPEKEY", readerContractSLA["MTTRTARGET"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "DESCRIPTION", readerContractSLA["MTTRTARGET"].ToString());
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "CONTRACTNUMBER__CONTRACT_NAME_", readerContractSLA["MTTRTARGET"].ToString()); //CONTRACTCOVERAGE
                            WriteSpec(writer, "ATSPECIFICATION", "ATSPECIFICATIONNAME", "CONTRACTCOVERAGE", readerContractSLA["MTTRTARGET"].ToString());
                        }

                        // Clean up ContractSLA Command
                        readerContractSLA.Dispose();
                        cmdContractSLA.Dispose();

                        writer.WriteEndElement();  //Close Root XML Element
                    }

                    // Clean up CircuitCI Command
                    readerCircuitCI.Dispose();
                    cmdCircuitCI.Dispose();

                } // Dispose XMLWriter
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Dts.Events.FireError(18, "Error in Creating XML", ex.Message, "", 0);
            }

            finally { con.Dispose(); } //Close DB Connection

            Dts.TaskResult = (int)ScriptResults.Success;
        }

            public static void WriteFullElementString(XmlWriter writer, string localName, string value)
            {
                if (value != "" && value!=null)
                {
                    writer.WriteStartElement(localName);
                    writer.WriteString(value);
                    writer.WriteFullEndElement();
                }
            }

            public static void WriteSpec(XmlWriter writer, string specName, string localName, string localValue, string value)
            {
                if (value != "" && value != null)
                {
                    writer.WriteStartElement(specName);
                    writer.WriteElementString(localName, localValue);
                    writer.WriteElementString("VALUE", value);
                    writer.WriteFullEndElement();
                }
            }



        //Method to clean Invalid XML Charecters
        public static string CleanInvalidXmlChars(string text)
        {
            string regex = @"[^\x09\x0A\x0D\x20-\uD7FF\uE000-\uFFFD\u10000-\u10FFFF]";
            return Regex.Replace(text, regex, "");
        }

Il problema che sto affrontando con questo approccio è quando i dati del cliente sono enormi (circa 200mb), l'attività di script è molto molto lenta, potenzialmente impiega più di 5 ore.

Ho cercato di ottimizzare il codice il più possibile con un miglioramento delle prestazioni minimo o nullo. È questo l'approccio giusto per questo progetto o esistono altre soluzioni efficaci per questo?

    
posta sab 16.09.2018 - 16:21
fonte

1 risposta

0

Ogni volta che esegui il ciclo su un datareader e all'interno di quel ciclo esegui un'altra selezione e scorri in quella direzione. Non sei ottimale con le tue prestazioni.

Tuttavia, probabilmente anche SSIS non ti sta aiutando. hai un altro compito SSIS prima di questo che seleziona tutti i clienti e lo esegue una volta per ciascuno?

La soluzione migliore è prendere questo codice e trasformarlo in un semplice programma stand-alone, diciamo solo un'app console per semplicità.

Collegalo e ottieni tutte le informazioni per un cliente singolo dal database. Una volta che ha i dati, disconnettersi dal db e scrivere il file.

Ora hai solo bisogno di una tabella aggiuntiva per tenere traccia di quali clienti sono stati elaborati e su quali sono ancora da lavorare. Utilizzare questo per consentire di eseguire il programma su due computer contemporaneamente. Aggiungi un po 'di codice all'inizio per catturare un cliente che non è stato ancora elaborato e un po' alla fine per contrassegnarlo come completo e per inviare il file ad un sito ftp centrale.

Una volta che hai ora puoi eseguire questo programma su tutti i computer a cui hai accesso. Il tuo database funzionerà al 100% ma risponderà solo alle query, non alla scrittura di XML e questo è ciò che sa fare meglio.

Il carico del looping tra i dati e la scrittura di file XML sarà distribuito su tutti quei computer. Puoi fare alcuni test per vedere quante istanze sono il numero migliore da eseguire su un singolo computer.

    
risposta data 16.09.2018 - 18:49
fonte

Leggi altre domande sui tag