Post reply

Error in SSIS Script Task

  • October 29, 2018 at 4:01 am

    #409224

    Hi everyone,

    I have a issue with this Script Task, well actually it's two: Why does opening a package containing the following script might result in an OOM Exception (just loading it, not running it yet)?
    And what is the reason this doesn't work?

    #region Help:  Introduction to the script task
    /* The Script Task allows you to perform virtually any operation that can be accomplished in
     * a .Net application within the context of an Integration Services control flow.
     *
     * Expand the other regions which have "Help" prefixes for examples of specific ways to use
     * Integration Services features within this script task. */
    #endregion


    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    #endregion

    namespace ST_a202dbc2ac304fa38c81f7f707bd097d
    {
        /// <summary>
        /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
        /// or parent of this class.
        /// </summary>
     [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
     public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
     {
            #region Help:  Using Integration Services variables and parameters in a script
            /* To use a variable in this script, first ensure that the variable has been added to
     */

            #endregion

            #region Help:  Firing Integration Services events from a script
            /* This script task can fire events for logging purposes.
             */
            #endregion

            #region Help:  Using Integration Services connection managers in a script
            /* Some types of connection managers can be used in this script task.  See the topic
            */
            #endregion

      public void Main()
      {
                // TODO: Add your code here
                bool fireAgain = false;

                String connectionString = Dts.Variables["$Project::SCRIPT_CONNECTION_STRING"].Value.ToString();
                String statement = "select  f.PK_Sales, f.FK_PrevSales, f.Commission_ID, f.ModelTypeFull_ID, f.Vehicle_Brand_Code, f.Effective_DateTime, f.Effective_Date, f.LifecycleStatus_ID, f.PrevSales_LC_ID, f.BusinessTypeGroup_ID, f.PrevSales_BTG_ID, f.Ordering_Dealer_ID, f.Ordering_Dealer_BTG_ID, f.Invoicing_Dealer_ID, f.Retailing_Dealer_ID, f.Retailing_Dealer_BTG_ID, f.Disposability_ID, f.LastVehicleStatus, f.LastChangePerDay, f.StorageTime, f.EndDate, f.ProductGroup_ID, f.Floor_Variant_ID, f.Cabin_Width_ID, f.Value, f.Ordering_Dealer_ID_Prev, dbo.fCompareDealerOrg(f.Retailing_Dealer_ID, f.Ordering_Dealer_ID_Prev) as RelevantDealerGroupChange from STG_F_Sales f order by f.PK_Sales desc";

                OleDbConnection sqlConn;
                OleDbConnection sqlConnInsert;
                OleDbCommand sqlComm;
                OleDbDataReader dataReader;

                try
                {

                   
                    sqlConn = new OleDbConnection(connectionString);
                    sqlConn.Open();

                    sqlConnInsert = new OleDbConnection(connectionString);
                    sqlConnInsert.Open();

                    sqlComm = new OleDbCommand(statement, sqlConn);
                    dataReader = sqlComm.ExecuteReader();

                    dataReader.Read();
                    processData(dataReader, sqlConnInsert);

                    dataReader.Close();
                    sqlConn.Close();
                    Dts.Events.FireInformation(3, "Process Values", "Processing Done.", "", 0, ref fireAgain);

                }
                catch (Exception ex)
                {
                    MessageBox.Show("Can not open connection ! " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data);
                    Dts.Events.FireInformation(3, "Process Values", "Processing Problem: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
                }


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

            private void processData(OleDbDataReader InputRow, OleDbConnection sqlConnInsert)
            {

                // initialize variables (business logic)
                bool VPrevLastVehicleStatus = false;
                int VOpenStockOrderMBCH = 0;
                int VOpenStockOrderBTG4 = 0;
                int VTotalOrdersDealer = 0;
                int VTotalOrdersMBCH = 0;
                int VTotalStockMBCH = 0;
                int VTotalStockDealer = 0;
                bool VDone = false;
                bool VVAFDone = false;
                DateTime VStartDateStockMBCH = DateTime.MinValue;
                DateTime VStartDateStockDealer = DateTime.MinValue;
                DateTime VStartDateStockDealer2 = DateTime.MinValue;
                int VRelevantDealerChanges = 0;
                bool VFactoryInvoiceCheck = true;
                int VTotalWholesale = 0;
                int VTotalRetail = 0;
                bool VFistWsBtgPassed = false;


                // variables for flow control, statement generation
                String insertStatement = "";
                bool lastRow = false;


                while (!lastRow)
                {
                   
                    // Input Row Fields
                    Int16 LastVehicleStatus = Convert.ToInt16(InputRow.GetBoolean(17));
                    Int32 LifecycleStatus_ID = InputRow.GetInt32(7);
                    Int32 Value = Convert.ToInt32(InputRow.GetInt64(24));
                    Int32 Ordering_Dealer_BTG_ID = InputRow.GetInt32(12);
                    Int16 PrevSales_BTG_ID = InputRow.GetInt16(10);
                    Int32 PrevSales_LC_ID = InputRow.GetInt32(8);
                    Int16 BusinessTypeGroup_ID = InputRow.GetInt16(9);
                    DateTime Effective_Date = InputRow.GetDateTime(6);
                    int RelevantDealerGroupChange = InputRow.GetInt32(26);

                    // flush generated rows
                    insertStatement = writeRows(sqlConnInsert, insertStatement, false);

                    //-------------------------------------------------------------
                    // InitializeRunningTotals
                    if (VPrevLastVehicleStatus == true)
                    {
                        VVAFDone = false;
                        VDone = false;
                        VFactoryInvoiceCheck = true;
                        VOpenStockOrderMBCH = 0;
                        VOpenStockOrderBTG4 = 0;
                        VTotalOrdersMBCH = 0;
                        VTotalOrdersDealer = 0;
                        VTotalStockMBCH = 0;
                        VTotalStockDealer = 0;
                        VTotalWholesale = 0;
                        VTotalRetail = 0;
                        VStartDateStockDealer2 = DateTime.MinValue;
                        VRelevantDealerChanges = 0;
                        VFistWsBtgPassed = false;
                       
                        // additional initializations
                        VStartDateStockMBCH = DateTime.MinValue;
                        VStartDateStockDealer = DateTime.MinValue;

                       
                    }

                    if (LastVehicleStatus == 1)
                    {
                        VPrevLastVehicleStatus = true;
                    }
                    else
                    {
                        VPrevLastVehicleStatus = false;
                    }
                   


                    //-------------------------------------------------------------
                    // VAFClearance
                    int VAFClearance;
                    if (LifecycleStatus_ID == 12)
                    {
                        VVAFDone = true;
                        VAFClearance = 1;
                    }
                    else
                    {
                        VAFClearance = 0;
                    }

                    //-------------------------------------------------------------
                    // Retail
                    int Retail;
                    if (LifecycleStatus_ID == 7 || LifecycleStatus_ID == 107)
                    {
                        Retail = Value;
                    }
                    else if (LifecycleStatus_ID == 10)
                    {
                        Retail = -1;
                    }
                    else
                    {
                        Retail = 0;
                    }

                    //-------------------------------------------------------------
                    // TotalRetail
                    int TotalRetail;
                    VTotalRetail = VTotalRetail + Retail;
                    TotalRetail = VTotalRetail;

                    //-------------------------------------------------------------
                    // Wholesale
                    int Wholesale;
                    if (LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103)
                    {
                        Wholesale = Value;
                    }
                    else if (LifecycleStatus_ID == 6)
                    {
                        Wholesale = -1;
                    }
                    else
                    {
                        Wholesale = 0;
                    }

                    //-------------------------------------------------------------
                    // TotalWholesale
                    int TotalWholesale;
                    VTotalWholesale = VTotalWholesale + Wholesale;
                    TotalWholesale = VTotalWholesale;

                    //-------------------------------------------------------------
                    // StockOrderMBCH
                    int StockOrderMBCH;
                    if (LifecycleStatus_ID == 23) StockOrderMBCH = Value;
                    else if (LifecycleStatus_ID == 33) StockOrderMBCH = -Value;
                    else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 101) && Ordering_Dealer_BTG_ID == 3) StockOrderMBCH = Value;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID != 3 && Ordering_Dealer_BTG_ID == 3) StockOrderMBCH = 1;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 3 && Ordering_Dealer_BTG_ID != 3) StockOrderMBCH = -1;
                    else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 3) StockOrderMBCH = -1;
                    else StockOrderMBCH = 0;

                    //-------------------------------------------------------------
                    // StockOrderBTG4
                    int StockOrderBTG4;
                    if (LifecycleStatus_ID == 24) StockOrderBTG4 = Value;
                    else if (LifecycleStatus_ID == 34) StockOrderBTG4 = Value * -1;
                    else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 101) && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = Value;
                    else if ((LifecycleStatus_ID == 7 || LifecycleStatus_ID == 107) && PrevSales_BTG_ID == 4 && (PrevSales_LC_ID  == 1 || PrevSales_LC_ID == 2 || PrevSales_LC_ID == 11)) StockOrderBTG4 = -1;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID != 4 && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = 1;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 4 && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = -1;
                    else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = -1;
                    else StockOrderBTG4 = 0; 

                    //-------------------------------------------------------------
                    // OrderBTG1
                    int OrderBTG1;
                    if (LifecycleStatus_ID == 21) OrderBTG1 = Value;
                    else if (LifecycleStatus_ID == 31) OrderBTG1 = Value * -1;
                    else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 100) && Ordering_Dealer_BTG_ID == 1) OrderBTG1 = Value;
                    else if (LifecycleStatus_ID == 11 && Ordering_Dealer_BTG_ID == 1 && PrevSales_BTG_ID != 1) OrderBTG1 = 1;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 1 && Ordering_Dealer_BTG_ID != 1) OrderBTG1 = -1;
                    else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 1) OrderBTG1 = -1;
                    else OrderBTG1 = 0;

                    //-------------------------------------------------------------
                    // OrderBTG2
                    int OrderBTG2;
                    if (LifecycleStatus_ID == 22) OrderBTG2 = Value;
                    else if (LifecycleStatus_ID == 32) OrderBTG2 = Value * -1;
                    else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 100) && Ordering_Dealer_BTG_ID == 2) OrderBTG2 = Value;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID != 2 && Ordering_Dealer_BTG_ID == 2) OrderBTG2 = 1;
                    else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 2 && Ordering_Dealer_BTG_ID != 2) OrderBTG2 = -1;
                    else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 2 ) OrderBTG2 = -1;
                    else OrderBTG2 = 0;

                    //-------------------------------------------------------------
                    // Done
                    bool Done;
                    if(VVAFDone)
                    {
                        VDone = true;
                        Done = true;
                    }
                    else if(TotalRetail > 0)
                    {
                        VDone = true;
                        Done = true;
                    }
                    else if(TotalWholesale <= 0 || TotalRetail <= 0)
                    {
                      VDone = false;
                      Done = false;
                    }
                    else if(TotalWholesale <= 0 && BusinessTypeGroup_ID == 2){
                      VDone = false;
                      Done = false;
                    }
                    Done = VDone;
     

                    //-------------------------------------------------------------
                    // FactoryInvoice
                    int FactoryInvoice;
                    if (VDone) FactoryInvoice = 0;
                    else if(LifecycleStatus_ID == 102 && Ordering_Dealer_BTG_ID != 4) FactoryInvoice = Value;
                    else if(LifecycleStatus_ID == 2 && Ordering_Dealer_BTG_ID != 4 && VFactoryInvoiceCheck) FactoryInvoice = 1;
                    else FactoryInvoice = 0;

                    //-------------------------------------------------------------
                    // StockEntryMBCH
                    int StockEntryMBCH;
                    if ((LifecycleStatus_ID == 102 || LifecycleStatus_ID == 6) && Ordering_Dealer_BTG_ID != 4) StockEntryMBCH = Value;
                    else if (LifecycleStatus_ID ==  2 && Ordering_Dealer_BTG_ID != 4 && VFactoryInvoiceCheck) StockEntryMBCH = Value;
                    else if (LifecycleStatus_ID ==  3 && Ordering_Dealer_BTG_ID != 4) StockEntryMBCH = -1;
                    else if (LifecycleStatus_ID == 12 && PrevSales_LC_ID == 2) StockEntryMBCH = -1;
                    else if (LifecycleStatus_ID == 103 && Ordering_Dealer_BTG_ID == 4) StockEntryMBCH = Value * -1;
                    else StockEntryMBCH = 0;

                    //-------------------------------------------------------------
                    // StockEntryDealer
                    int StockEntryDealer;
                    if (LifecycleStatus_ID == 3 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2)) StockEntryDealer = 1;
                    else if ((LifecycleStatus_ID == 7 || LifecycleStatus_ID ==  6) && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2)) StockEntryDealer = -1;
                    else if (LifecycleStatus_ID == 10 && Ordering_Dealer_BTG_ID == 2) StockEntryDealer = 1;
                    else if (LifecycleStatus_ID == 12 && PrevSales_BTG_ID == 2) StockEntryDealer = -1;
                    else if (LifecycleStatus_ID == 103 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2)) StockEntryDealer = Value;
                    else if (LifecycleStatus_ID == 107 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2) && Value == -1) StockEntryDealer = 1;
                    else if (LifecycleStatus_ID == 107 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2) && Value == 1) StockEntryDealer = -1;
                    else StockEntryDealer = 0;


                    //-------------------------------------------------------------
                    // WarrantyNotificationCard
                    int WarrantyNotificationCard;
                    if (LifecycleStatus_ID == 9 || LifecycleStatus_ID == 109)
                    {
                        WarrantyNotificationCard = Value;
                    }
                    else
                    {
                        WarrantyNotificationCard = 0;
                    }

                    //-------------------------------------------------------------
                    // FirstRegistration
                    int FirstRegistration;
                    if (LifecycleStatus_ID == 8 || LifecycleStatus_ID == 108){
                        FirstRegistration = Value;
                    }
                    else{
                        FirstRegistration = 0;   
                    }

                    //-------------------------------------------------------------
                    // FirstRegistrationAstra
                    int FirstRegistrationAstra;
                    if (LifecycleStatus_ID == 13){
                        FirstRegistrationAstra = Value;
                    }
                    else {
                        FirstRegistrationAstra = 0;
                    }


                    //-------------------------------------------------------------
                    // OpenStockOrderMBCH
                    int OpenStockOrderMBCH;
                    if (VDone || (VAFClearance > 0)) {
                        VOpenStockOrderMBCH = 0;
                    }
                    else{
                        VOpenStockOrderMBCH = VOpenStockOrderMBCH + StockOrderMBCH;
                    }
                    OpenStockOrderMBCH = VOpenStockOrderMBCH;

                    //-------------------------------------------------------------
                    // OpenStockOrderBTG4
                    int OpenStockOrderBTG4;
                    if (VDone || (VAFClearance > 0)) {
                        VOpenStockOrderBTG4 = 0;
                    }
                    else{
                        VOpenStockOrderBTG4 = VOpenStockOrderBTG4 + StockOrderBTG4;
                    }
                    OpenStockOrderBTG4 = VOpenStockOrderBTG4;

                    //-------------------------------------------------------------
                    // TotalOrdersMBCH
                    int TotalOrdersMBCH;
                    if (VAFClearance > 0){
                        VTotalOrdersMBCH = 0;
                    }  
                    else if (Ordering_Dealer_BTG_ID == 2) {
                        VTotalOrdersMBCH = VTotalOrdersMBCH + OrderBTG2 - Wholesale;
                    }
                    else{
                        VTotalOrdersMBCH = VTotalOrdersMBCH + OrderBTG2;
                    }
                    TotalOrdersMBCH = VTotalOrdersMBCH;

                    //-------------------------------------------------------------
                    // TotalOrdersDealer
                    int TotalOrdersDealer;
                    if (VAFClearance > 0){
                        VTotalOrdersDealer = 0;
                    }
                    else if (Ordering_Dealer_BTG_ID == 1) {
                        VTotalOrdersDealer = VTotalOrdersDealer + OrderBTG1 - Wholesale;
                    }
                    else{
                        VTotalOrdersDealer = VTotalOrdersDealer + OrderBTG1;
                    }
                    TotalOrdersDealer = VTotalOrdersDealer;

                    //-------------------------------------------------------------
                    // TotalStockMBCH
                    int TotalStockMBCH;
                    if (VAFClearance > 0) {
                        VTotalStockMBCH  = 0;                 
                    } 
                    else{
                        VTotalStockMBCH = VTotalStockMBCH + StockEntryMBCH;
                    }
                    TotalStockMBCH = VTotalStockMBCH;

                    //-------------------------------------------------------------
                    // TotalStockDealer
                    int TotalStockDealer;
                    if (VAFClearance > 0) {
                        VTotalStockDealer = 0;
                    }
                    else{
                        VTotalStockDealer = VTotalStockDealer + StockEntryDealer;
                    }
                    TotalStockDealer = VTotalStockDealer;

                    //-------------------------------------------------------------
                    // StartDateStockMBCH
                    DateTime StartDateStockMBCH;
                    if ((LifecycleStatus_ID == 2 || LifecycleStatus_ID == 102) && BusinessTypeGroup_ID != 4)
                    {
                        VStartDateStockMBCH = Effective_Date;
                        StartDateStockMBCH = Effective_Date;
                    }
                    else if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && BusinessTypeGroup_ID != 4)
                    {
                        VStartDateStockMBCH = DateTime.MinValue;
                        StartDateStockMBCH = DateTime.MinValue;
                    }
                    else if ((LifecycleStatus_ID == 6 || LifecycleStatus_ID == 106) && BusinessTypeGroup_ID != 4)
                    {
                        VStartDateStockMBCH = Effective_Date;
                        StartDateStockMBCH = Effective_Date;
                    }
                    else StartDateStockMBCH = VStartDateStockMBCH;

                    //-------------------------------------------------------------
                    // StartDateStockDealer
                    DateTime StartDateStockDealer;
                    if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && BusinessTypeGroup_ID != 4)
                    {
                        VStartDateStockDealer = Effective_Date;
                        StartDateStockDealer = Effective_Date;
                    }
                    else if ((LifecycleStatus_ID == 6 || LifecycleStatus_ID == 106) && BusinessTypeGroup_ID != 4)
                    {
                        VStartDateStockDealer = DateTime.MinValue;
                        StartDateStockDealer = DateTime.MinValue;
                    }
                    StartDateStockDealer = VStartDateStockDealer;

                    //-------------------------------------------------------------
                    // StartDateStockDealer2
                    DateTime StartDateStockDealer2;
                    VRelevantDealerChanges = VRelevantDealerChanges + RelevantDealerGroupChange;
                    if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && BusinessTypeGroup_ID != 4)
                    {
                        if(VStartDateStockDealer2.Equals(DateTime.MinValue))
                        {
                            VStartDateStockDealer2 = Effective_Date;
                            VRelevantDealerChanges = 0;
                            StartDateStockDealer2 = Effective_Date;
                        }
                        else if(VRelevantDealerChanges > 0)
                        {
                            VStartDateStockDealer2 = Effective_Date;
                            StartDateStockDealer2 = Effective_Date;  
                        }
                        else StartDateStockDealer2 = VStartDateStockDealer2;
                    }
                    else if ((LifecycleStatus_ID == 6 || LifecycleStatus_ID == 106) && BusinessTypeGroup_ID != 4)
                    {
                        VRelevantDealerChanges = 0;
                        StartDateStockDealer2 = DateTime.MinValue;
                    }
                    else StartDateStockDealer2 = VStartDateStockDealer2;

                    //-------------------------------------------------------------
                    // UpdateFactoryInvoiceCheck
                    if(LifecycleStatus_ID == 2 && Ordering_Dealer_BTG_ID != 4 && VFactoryInvoiceCheck) {
                        VFactoryInvoiceCheck = false;
                    }


                    //-------------------------------------------------------------
                    // FirstWsBtg1
                    int FirstWsBtg1;
                    if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && Ordering_Dealer_BTG_ID == 1 && VFistWsBtgPassed == false)
                    {
                        VFistWsBtgPassed = true;
                        FirstWsBtg1 = 1;
                    }
                    else FirstWsBtg1 = 0; 


                    // update insert statement
                   // insertStatement = insertStatement + "(" + Retailing_Dealer_ID + ", " + PrevSales_BTG_ID + ", NULL, " + Invoicing_Dealer_ID + ", " + Ordering_Dealer_ID + ", " + Update_LifecycleStatus_ID + ", " + Update_BTG_ID + ", " + PrevSales_LC_ID + ", " + (int)InputRow.GetInt64(0) + "),";

                    try
                    {

                        insertStatement = insertStatement +
                        "(" + InputRow.GetInt64(0) + //<PK_Sales, bigint,>
                        ", " + (InputRow.IsDBNull(1) ? "NULL" : Convert.ToString(InputRow.GetInt64(1))) + //<FK_PrevSales, bigint,>
                        ", '" + InputRow.GetString(2) + "'" +//<Commission_ID, nvarchar(255),>
                        ", '" + InputRow.GetString(3) + "'" +//<ModelTypeFull_ID, nvarchar(255),>
                        ", " + (InputRow.IsDBNull(4) ? "NULL" : Convert.ToString(InputRow.GetInt32(4))) + //<Vehicle_Brand_Code, int,>
                        ", '" + InputRow.GetDateTime(5).ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<Effective_DateTime, datetime,>
                        ", '" + InputRow.GetDateTime(6).ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<Effective_Date, datetime,>
                        ", " + InputRow.GetInt32(7) + //<LifecycleStatus_ID, int,>
                        ", " + InputRow.GetInt32(8) + //<PrevSales_LC_ID, int,>
                        ", " + InputRow.GetInt16(9) + //<BusinessTypeGroup_ID, smallint,>
                        ", " + InputRow.GetInt16(10) + //<PrevSales_BTG_ID, smallint,>
                        ", " + InputRow.GetInt32(11) + //<Ordering_Dealer_ID, int,>
                        ", " + InputRow.GetInt32(12) + //<Ordering_Dealer_BTG_ID, int,>
                        ", " + InputRow.GetInt32(13) + //<Invoicing_Dealer_ID, int,>
                        ", " + InputRow.GetInt32(14) + //<Retailing_Dealer_ID, int,>
                        ", " + InputRow.GetInt32(15) + //<Retailing_Dealer_BTG_ID, int,>
                        ", " + InputRow.GetInt32(16) + //<Disposability_ID, int,>
                        ", " + Convert.ToInt32(InputRow.GetBoolean(17)) + //<LastVehicleStatus, smallint,>
                        ", " + Convert.ToInt32(InputRow.GetBoolean(18)) + //<LastChangePerDay, smallint,>
                        ", NULL " +//<StorageTime, bigint,>
                        ", " + (StartDateStockMBCH.Equals(DateTime.MinValue) ? "NULL" : "'" + StartDateStockMBCH.ToString("yyyyMMdd HH:mm:ss.fff") + "'") + //<StartDateStockMBCH, datetime,>
                        ", " + (StartDateStockDealer.Equals(DateTime.MinValue) ? "NULL" : "'" + StartDateStockDealer.ToString("yyyyMMdd HH:mm:ss.fff") + "'") + //<StartDateStockDealer, datetime,>
                        ", '" + InputRow.GetDateTime(20).ToString("s") + "'" + //<EndDate, datetime,>
                        ", " + InputRow.GetInt64(24) + //<Value, bigint,>
                        ", " + StockOrderMBCH + //<StockOrderMBCH, bigint,>
                        ", " + StockOrderBTG4 + //<StockOrderBTG4, bigint,>
                        ", " + OrderBTG1 + //<OrderBTG1, bigint,>
                        ", " + OrderBTG2 + //<OrderBTG2, bigint,>
                        ", " + FactoryInvoice + //<FactoryInvoice, bigint,>
                        ", " + Wholesale + //<Wholesale, bigint,>
                        ", " + WarrantyNotificationCard + //<WarrantyNotificationCard, bigint,>
                        ", " + Retail + //<Retail, bigint,>
                        ", " + FirstRegistration + //<FirstRegistration, bigint,>
                        ", " + StockEntryMBCH + //<StockEntryMBCH, bigint,>
                        ", " + StockEntryDealer + //<StockEntryDealer, bigint,>
                        ", " + OpenStockOrderMBCH + //<OpenStockOrderMBCH, bigint,>
                        ", " + OpenStockOrderBTG4 + //<OpenStockOrderBTG4, bigint,>
                        ", " + TotalOrdersMBCH + //<TotalOrdersMBCH, bigint,>
                        ", " + TotalOrdersDealer + //<TotalOrdersDealer, bigint,>
                        ", " + TotalStockMBCH + //<TotalStockMBCH, bigint,>
                        ", " + TotalStockDealer + //<TotalStockDealer, bigint,>
                        ", NULL " + //<StorageTimeClass, bigint,>
                        ", " + VAFClearance + //<VAFClearance, smallint,>
                        ", " + Convert.ToInt32(Done) + //<Done, bit,>
                        ", " + FirstRegistrationAstra + //<FirstRegistrationAstra, bigint,>
                        ", '" + InputRow.GetString(21) + "'" + //<ProductGroup_ID, varchar(255),>
                        ", " + InputRow.GetInt32(23) + //<Cabin_Width_ID, int,>
                        ", '" + InputRow.GetString(22) + "'" + //<Floor_Variant_ID, varchar(255),>
                        ", " + (InputRow.IsDBNull(25) ? "NULL" : Convert.ToString(InputRow.GetInt32(25))) + //<Ordering_Dealer_ID_Prev, int,>
                        ", " + InputRow.GetInt32(26) + //<RelevantDealerGroupChange, int,>
                        ", " + (StartDateStockDealer2.Equals(DateTime.MinValue) ? "NULL" : "'" + StartDateStockDealer2.ToString("yyyyMMdd HH:mm:ss.fff") + "'") + //<StartDateStockDealer2, datetime,>
                        ", " + FirstWsBtg1 + //<FirstWsBtg1, smallint,>
                        "),";

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Statement construction error: " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data);
                        //Dts.Events.FireInformation(3, "Insert Values", "Statement construction error: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
                    }

                    // fetch next Row, Attach Row to Output Buffer
                    lastRow = !(InputRow.Read());

                }


                // handle final commit
                insertStatement = writeRows(sqlConnInsert, insertStatement, true);

            }

            private String writeRows(OleDbConnection sqlConnInsert, string insertStatement, bool forceCommit)
            {
                //throw new NotImplementedException();

                if (!insertStatement.Equals(""))
                {
                    if (forceCommit || insertStatement.Length > 2000)
                    {
                        insertStatement = insertStatement.Remove(insertStatement.Length - 1);
                        MessageBox.Show(insertStatement);
                        OleDbCommand ic = new OleDbCommand(insertStatement, sqlConnInsert);
                        ic.ExecuteNonQuery();
                    }
                    else
                    {
                        return insertStatement;
                    }

                }


                return "INSERT INTO  dbo.STG_F_Sales_Totals ( PK_Sales , FK_PrevSales , Commission_ID , ModelTypeFull_ID , Vehicle_Brand_Code , Effective_DateTime , Effective_Date , LifecycleStatus_ID , PrevSales_LC_ID , BusinessTypeGroup_ID , PrevSales_BTG_ID , Ordering_Dealer_ID , Ordering_Dealer_BTG_ID , Invoicing_Dealer_ID , Retailing_Dealer_ID , Retailing_Dealer_BTG_ID , Disposability_ID , LastVehicleStatus , LastChangePerDay , StorageTime , StartDateStockMBCH , StartDateStockDealer , EndDate , Value , StockOrderMBCH , StockOrderBTG4 , OrderBTG1 , OrderBTG2 , FactoryInvoice , Wholesale , WarrantyNotificationCard , Retail , FirstRegistration , StockEntryMBCH , StockEntryDealer , OpenStockOrderMBCH , OpenStockOrderBTG4 , TotalOrdersMBCH , TotalOrdersDealer , TotalStockMBCH , TotalStockDealer , StorageTimeClass , VAFClearance , Done , FirstRegistrationAstra , ProductGroup_ID , Cabin_Width_ID , Floor_Variant_ID , Ordering_Dealer_ID_Prev , RelevantDealerGroupChange , StartDateStockDealer2 , FirstWsBtg1 ) VALUES ";
            }


            #region ScriptResults declaration
            /// <summary>
            /// This enum provides a convenient shorthand within the scope of this class for setting the
            /// result of the script.
            ///
            /// This code was generated automatically.
            /// </summary>
            enum ScriptResults
            {
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            };
            #endregion

     }
    }

    it errors out on me with the following exception when running in debug mode:

    I've checked all the columns and their data types are correct and the connection string which is passed looks fine to me aswell. The whole thing is running on SQL Server 2012.

    Connection String from Project Variable: Data Source=DevServer1;Initial Catalog=DB1;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;

    any help is highly appreciated.

  • October 29, 2018 at 6:10 am

    #2011503

    Your ScriptResults enum is missing.
    Why are you doing this in a script task rather than a stored procedure? This logic belongs in the database.

    Try this:

    #region Help: Introduction to the script task
    /* The Script Task allows you to perform virtually any operation that can be accomplished in
    * a .Net application within the context of an Integration Services control flow.
    *
    * Expand the other regions which have "Help" prefixes for examples of specific ways to use
    * Integration Services features within this script task. */
    #endregion

    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    using System.Data.OleDb;
    #endregion

    namespace ST_a202dbc2ac304fa38c81f7f707bd097d
    {
      /// <summary>
      /// ScriptMain is the entry point class of the script. Do not change the name, attributes,
      /// or parent of this class.
      /// </summary>
      [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
      public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
      {
       #region Help: Using Integration Services variables and parameters in a script
       /* To use a variable in this script, first ensure that the variable has been added to
    */

       #endregion

       #region Help: Firing Integration Services events from a script
       /* This script task can fire events for logging purposes.
       */
       #endregion

       #region Help: Using Integration Services connection managers in a script
       /* Some types of connection managers can be used in this script task. See the topic
       */
       #endregion

       public void Main()
       {
        // TODO: Add your code here
        bool fireAgain = false;

        String connectionString = Dts.Variables["$Project::SCRIPT_CONNECTION_STRING"].Value.ToString();
        String statement = "select f.PK_Sales, f.FK_PrevSales, f.Commission_ID, f.ModelTypeFull_ID, f.Vehicle_Brand_Code, f.Effective_DateTime, f.Effective_Date, f.LifecycleStatus_ID, f.PrevSales_LC_ID, f.BusinessTypeGroup_ID, f.PrevSales_BTG_ID, f.Ordering_Dealer_ID, f.Ordering_Dealer_BTG_ID, f.Invoicing_Dealer_ID, f.Retailing_Dealer_ID, f.Retailing_Dealer_BTG_ID, f.Disposability_ID, f.LastVehicleStatus, f.LastChangePerDay, f.StorageTime, f.EndDate, f.ProductGroup_ID, f.Floor_Variant_ID, f.Cabin_Width_ID, f.Value, f.Ordering_Dealer_ID_Prev, dbo.fCompareDealerOrg(f.Retailing_Dealer_ID, f.Ordering_Dealer_ID_Prev) as RelevantDealerGroupChange from STG_F_Sales f order by f.PK_Sales desc";

        OleDbConnection sqlConn;
        OleDbConnection sqlConnInsert;
        OleDbCommand sqlComm;
        OleDbDataReader dataReader;

        try
        {

          sqlConn = new OleDbConnection(connectionString);
          sqlConn.Open();

          sqlConnInsert = new OleDbConnection(connectionString);
          sqlConnInsert.Open();

          sqlComm = new OleDbCommand(statement, sqlConn);
          dataReader = sqlComm.ExecuteReader();

          dataReader.Read();
          processData(dataReader, sqlConnInsert);

          dataReader.Close();
          sqlConn.Close();
          Dts.Events.FireInformation(3, "Process Values", "Processing Done.", "", 0, ref fireAgain);

        }
        catch (Exception ex)
        {
          MessageBox.Show("Can not open connection ! " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data);
          Dts.Events.FireInformation(3, "Process Values", "Processing Problem: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
        }

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

       private void processData(OleDbDataReader InputRow, OleDbConnection sqlConnInsert)
       {

        // initialize variables (business logic)
        bool VPrevLastVehicleStatus = false;
        int VOpenStockOrderMBCH = 0;
        int VOpenStockOrderBTG4 = 0;
        int VTotalOrdersDealer = 0;
        int VTotalOrdersMBCH = 0;
        int VTotalStockMBCH = 0;
        int VTotalStockDealer = 0;
        bool VDone = false;
        bool VVAFDone = false;
        DateTime VStartDateStockMBCH = DateTime.MinValue;
        DateTime VStartDateStockDealer = DateTime.MinValue;
        DateTime VStartDateStockDealer2 = DateTime.MinValue;
        int VRelevantDealerChanges = 0;
        bool VFactoryInvoiceCheck = true;
        int VTotalWholesale = 0;
        int VTotalRetail = 0;
        bool VFistWsBtgPassed = false;

        // variables for flow control, statement generation
        String insertStatement = "";
        bool lastRow = false;

        while (!lastRow)
        {

          // Input Row Fields
          Int16 LastVehicleStatus = Convert.ToInt16(InputRow.GetBoolean(17));
          Int32 LifecycleStatus_ID = InputRow.GetInt32(7);
          Int32 Value = Convert.ToInt32(InputRow.GetInt64(24));
          Int32 Ordering_Dealer_BTG_ID = InputRow.GetInt32(12);
          Int16 PrevSales_BTG_ID = InputRow.GetInt16(10);
          Int32 PrevSales_LC_ID = InputRow.GetInt32(8);
          Int16 BusinessTypeGroup_ID = InputRow.GetInt16(9);
          DateTime Effective_Date = InputRow.GetDateTime(6);
          int RelevantDealerGroupChange = InputRow.GetInt32(26);

          // flush generated rows
          insertStatement = writeRows(sqlConnInsert, insertStatement, false);

          //-------------------------------------------------------------
          // InitializeRunningTotals
          if (VPrevLastVehicleStatus == true)
          {
           VVAFDone = false;
           VDone = false;
           VFactoryInvoiceCheck = true;
           VOpenStockOrderMBCH = 0;
           VOpenStockOrderBTG4 = 0;
           VTotalOrdersMBCH = 0;
           VTotalOrdersDealer = 0;
           VTotalStockMBCH = 0;
           VTotalStockDealer = 0;
           VTotalWholesale = 0;
           VTotalRetail = 0;
           VStartDateStockDealer2 = DateTime.MinValue;
           VRelevantDealerChanges = 0;
           VFistWsBtgPassed = false;

           // additional initializations
           VStartDateStockMBCH = DateTime.MinValue;
           VStartDateStockDealer = DateTime.MinValue;

          }

          if (LastVehicleStatus == 1)
          {
           VPrevLastVehicleStatus = true;
          }
          else
          {
           VPrevLastVehicleStatus = false;
          }

          //-------------------------------------------------------------
          // VAFClearance
          int VAFClearance;
          if (LifecycleStatus_ID == 12)
          {
           VVAFDone = true;
           VAFClearance = 1;
          }
          else
          {
           VAFClearance = 0;
          }

          //-------------------------------------------------------------
          // Retail
          int Retail;
          if (LifecycleStatus_ID == 7 || LifecycleStatus_ID == 107)
          {
           Retail = Value;
          }
          else if (LifecycleStatus_ID == 10)
          {
           Retail = -1;
          }
          else
          {
           Retail = 0;
          }

          //-------------------------------------------------------------
          // TotalRetail
          int TotalRetail;
          VTotalRetail = VTotalRetail + Retail;
          TotalRetail = VTotalRetail;

          //-------------------------------------------------------------
          // Wholesale
          int Wholesale;
          if (LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103)
          {
           Wholesale = Value;
          }
          else if (LifecycleStatus_ID == 6)
          {
           Wholesale = -1;
          }
          else
          {
           Wholesale = 0;
          }

          //-------------------------------------------------------------
          // TotalWholesale
          int TotalWholesale;
          VTotalWholesale = VTotalWholesale + Wholesale;
          TotalWholesale = VTotalWholesale;

          //-------------------------------------------------------------
          // StockOrderMBCH
          int StockOrderMBCH;
          if (LifecycleStatus_ID == 23) StockOrderMBCH = Value;
          else if (LifecycleStatus_ID == 33) StockOrderMBCH = -Value;
          else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 101) && Ordering_Dealer_BTG_ID == 3) StockOrderMBCH = Value;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID != 3 && Ordering_Dealer_BTG_ID == 3) StockOrderMBCH = 1;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 3 && Ordering_Dealer_BTG_ID != 3) StockOrderMBCH = -1;
          else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 3) StockOrderMBCH = -1;
          else StockOrderMBCH = 0;

          //-------------------------------------------------------------
          // StockOrderBTG4
          int StockOrderBTG4;
          if (LifecycleStatus_ID == 24) StockOrderBTG4 = Value;
          else if (LifecycleStatus_ID == 34) StockOrderBTG4 = Value * -1;
          else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 101) && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = Value;
          else if ((LifecycleStatus_ID == 7 || LifecycleStatus_ID == 107) && PrevSales_BTG_ID == 4 && (PrevSales_LC_ID == 1 || PrevSales_LC_ID == 2 || PrevSales_LC_ID == 11)) StockOrderBTG4 = -1;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID != 4 && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = 1;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 4 && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = -1;
          else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 4) StockOrderBTG4 = -1;
          else StockOrderBTG4 = 0;

          //-------------------------------------------------------------
          // OrderBTG1
          int OrderBTG1;
          if (LifecycleStatus_ID == 21) OrderBTG1 = Value;
          else if (LifecycleStatus_ID == 31) OrderBTG1 = Value * -1;
          else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 100) && Ordering_Dealer_BTG_ID == 1) OrderBTG1 = Value;
          else if (LifecycleStatus_ID == 11 && Ordering_Dealer_BTG_ID == 1 && PrevSales_BTG_ID != 1) OrderBTG1 = 1;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 1 && Ordering_Dealer_BTG_ID != 1) OrderBTG1 = -1;
          else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 1) OrderBTG1 = -1;
          else OrderBTG1 = 0;

          //-------------------------------------------------------------
          // OrderBTG2
          int OrderBTG2;
          if (LifecycleStatus_ID == 22) OrderBTG2 = Value;
          else if (LifecycleStatus_ID == 32) OrderBTG2 = Value * -1;
          else if ((LifecycleStatus_ID == 1 || LifecycleStatus_ID == 100) && Ordering_Dealer_BTG_ID == 2) OrderBTG2 = Value;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID != 2 && Ordering_Dealer_BTG_ID == 2) OrderBTG2 = 1;
          else if (LifecycleStatus_ID == 11 && PrevSales_BTG_ID == 2 && Ordering_Dealer_BTG_ID != 2) OrderBTG2 = -1;
          else if (LifecycleStatus_ID == 12 && Ordering_Dealer_BTG_ID == 2) OrderBTG2 = -1;
          else OrderBTG2 = 0;

          //-------------------------------------------------------------
          // Done
          bool Done;
          if (VVAFDone)
          {
           VDone = true;
           Done = true;
          }
          else if (TotalRetail > 0)
          {
           VDone = true;
           Done = true;
          }
          else if (TotalWholesale <= 0 || TotalRetail <= 0)
          {
           VDone = false;
           Done = false;
          }
          else if (TotalWholesale <= 0 && BusinessTypeGroup_ID == 2)
          {
           VDone = false;
           Done = false;
          }
          Done = VDone;

          //-------------------------------------------------------------
          // FactoryInvoice
          int FactoryInvoice;
          if (VDone) FactoryInvoice = 0;
          else if (LifecycleStatus_ID == 102 && Ordering_Dealer_BTG_ID != 4) FactoryInvoice = Value;
          else if (LifecycleStatus_ID == 2 && Ordering_Dealer_BTG_ID != 4 && VFactoryInvoiceCheck) FactoryInvoice = 1;
          else FactoryInvoice = 0;

          //-------------------------------------------------------------
          // StockEntryMBCH
          int StockEntryMBCH;
          if ((LifecycleStatus_ID == 102 || LifecycleStatus_ID == 6) && Ordering_Dealer_BTG_ID != 4) StockEntryMBCH = Value;
          else if (LifecycleStatus_ID == 2 && Ordering_Dealer_BTG_ID != 4 && VFactoryInvoiceCheck) StockEntryMBCH = Value;
          else if (LifecycleStatus_ID == 3 && Ordering_Dealer_BTG_ID != 4) StockEntryMBCH = -1;
          else if (LifecycleStatus_ID == 12 && PrevSales_LC_ID == 2) StockEntryMBCH = -1;
          else if (LifecycleStatus_ID == 103 && Ordering_Dealer_BTG_ID == 4) StockEntryMBCH = Value * -1;
          else StockEntryMBCH = 0;

          //-------------------------------------------------------------
          // StockEntryDealer
          int StockEntryDealer;
          if (LifecycleStatus_ID == 3 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2)) StockEntryDealer = 1;
          else if ((LifecycleStatus_ID == 7 || LifecycleStatus_ID == 6) && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2)) StockEntryDealer = -1;
          else if (LifecycleStatus_ID == 10 && Ordering_Dealer_BTG_ID == 2) StockEntryDealer = 1;
          else if (LifecycleStatus_ID == 12 && PrevSales_BTG_ID == 2) StockEntryDealer = -1;
          else if (LifecycleStatus_ID == 103 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2)) StockEntryDealer = Value;
          else if (LifecycleStatus_ID == 107 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2) && Value == -1) StockEntryDealer = 1;
          else if (LifecycleStatus_ID == 107 && (Ordering_Dealer_BTG_ID == 1 || Ordering_Dealer_BTG_ID == 2) && Value == 1) StockEntryDealer = -1;
          else StockEntryDealer = 0;

          //-------------------------------------------------------------
          // WarrantyNotificationCard
          int WarrantyNotificationCard;
          if (LifecycleStatus_ID == 9 || LifecycleStatus_ID == 109)
          {
           WarrantyNotificationCard = Value;
          }
          else
          {
           WarrantyNotificationCard = 0;
          }

          //-------------------------------------------------------------
          // FirstRegistration
          int FirstRegistration;
          if (LifecycleStatus_ID == 8 || LifecycleStatus_ID == 108)
          {
           FirstRegistration = Value;
          }
          else
          {
           FirstRegistration = 0;
          }

          //-------------------------------------------------------------
          // FirstRegistrationAstra
          int FirstRegistrationAstra;
          if (LifecycleStatus_ID == 13)
          {
           FirstRegistrationAstra = Value;
          }
          else
          {
           FirstRegistrationAstra = 0;
          }

          //-------------------------------------------------------------
          // OpenStockOrderMBCH
          int OpenStockOrderMBCH;
          if (VDone || (VAFClearance > 0))
          {
           VOpenStockOrderMBCH = 0;
          }
          else
          {
           VOpenStockOrderMBCH = VOpenStockOrderMBCH + StockOrderMBCH;
          }
          OpenStockOrderMBCH = VOpenStockOrderMBCH;

          //-------------------------------------------------------------
          // OpenStockOrderBTG4
          int OpenStockOrderBTG4;
          if (VDone || (VAFClearance > 0))
          {
           VOpenStockOrderBTG4 = 0;
          }
          else
          {
           VOpenStockOrderBTG4 = VOpenStockOrderBTG4 + StockOrderBTG4;
          }
          OpenStockOrderBTG4 = VOpenStockOrderBTG4;

          //-------------------------------------------------------------
          // TotalOrdersMBCH
          int TotalOrdersMBCH;
          if (VAFClearance > 0)
          {
           VTotalOrdersMBCH = 0;
          }
          else if (Ordering_Dealer_BTG_ID == 2)
          {
           VTotalOrdersMBCH = VTotalOrdersMBCH + OrderBTG2 - Wholesale;
          }
          else
          {
           VTotalOrdersMBCH = VTotalOrdersMBCH + OrderBTG2;
          }
          TotalOrdersMBCH = VTotalOrdersMBCH;

          //-------------------------------------------------------------
          // TotalOrdersDealer
          int TotalOrdersDealer;
          if (VAFClearance > 0)
          {
           VTotalOrdersDealer = 0;
          }
          else if (Ordering_Dealer_BTG_ID == 1)
          {
           VTotalOrdersDealer = VTotalOrdersDealer + OrderBTG1 - Wholesale;
          }
          else
          {
           VTotalOrdersDealer = VTotalOrdersDealer + OrderBTG1;
          }
          TotalOrdersDealer = VTotalOrdersDealer;

          //-------------------------------------------------------------
          // TotalStockMBCH
          int TotalStockMBCH;
          if (VAFClearance > 0)
          {
           VTotalStockMBCH = 0;
          }
          else
          {
           VTotalStockMBCH = VTotalStockMBCH + StockEntryMBCH;
          }
          TotalStockMBCH = VTotalStockMBCH;

          //-------------------------------------------------------------
          // TotalStockDealer
          int TotalStockDealer;
          if (VAFClearance > 0)
          {
           VTotalStockDealer = 0;
          }
          else
          {
           VTotalStockDealer = VTotalStockDealer + StockEntryDealer;
          }
          TotalStockDealer = VTotalStockDealer;

          //-------------------------------------------------------------
          // StartDateStockMBCH
          DateTime StartDateStockMBCH;
          if ((LifecycleStatus_ID == 2 || LifecycleStatus_ID == 102) && BusinessTypeGroup_ID != 4)
          {
           VStartDateStockMBCH = Effective_Date;
           StartDateStockMBCH = Effective_Date;
          }
          else if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && BusinessTypeGroup_ID != 4)
          {
           VStartDateStockMBCH = DateTime.MinValue;
           StartDateStockMBCH = DateTime.MinValue;
          }
          else if ((LifecycleStatus_ID == 6 || LifecycleStatus_ID == 106) && BusinessTypeGroup_ID != 4)
          {
           VStartDateStockMBCH = Effective_Date;
           StartDateStockMBCH = Effective_Date;
          }
          else StartDateStockMBCH = VStartDateStockMBCH;

          //-------------------------------------------------------------
          // StartDateStockDealer
          DateTime StartDateStockDealer;
          if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && BusinessTypeGroup_ID != 4)
          {
           VStartDateStockDealer = Effective_Date;
           StartDateStockDealer = Effective_Date;
          }
          else if ((LifecycleStatus_ID == 6 || LifecycleStatus_ID == 106) && BusinessTypeGroup_ID != 4)
          {
           VStartDateStockDealer = DateTime.MinValue;
           StartDateStockDealer = DateTime.MinValue;
          }
          StartDateStockDealer = VStartDateStockDealer;

          //-------------------------------------------------------------
          // StartDateStockDealer2
          DateTime StartDateStockDealer2;
          VRelevantDealerChanges = VRelevantDealerChanges + RelevantDealerGroupChange;
          if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && BusinessTypeGroup_ID != 4)
          {
           if (VStartDateStockDealer2.Equals(DateTime.MinValue))
           {
            VStartDateStockDealer2 = Effective_Date;
            VRelevantDealerChanges = 0;
            StartDateStockDealer2 = Effective_Date;
           }
           else if (VRelevantDealerChanges > 0)
           {
            VStartDateStockDealer2 = Effective_Date;
            StartDateStockDealer2 = Effective_Date;
           }
           else StartDateStockDealer2 = VStartDateStockDealer2;
          }
          else if ((LifecycleStatus_ID == 6 || LifecycleStatus_ID == 106) && BusinessTypeGroup_ID != 4)
          {
           VRelevantDealerChanges = 0;
           StartDateStockDealer2 = DateTime.MinValue;
          }
          else StartDateStockDealer2 = VStartDateStockDealer2;

          //-------------------------------------------------------------
          // UpdateFactoryInvoiceCheck
          if (LifecycleStatus_ID == 2 && Ordering_Dealer_BTG_ID != 4 && VFactoryInvoiceCheck)
          {
           VFactoryInvoiceCheck = false;
          }

          //-------------------------------------------------------------
          // FirstWsBtg1
          int FirstWsBtg1;
          if ((LifecycleStatus_ID == 3 || LifecycleStatus_ID == 103) && Ordering_Dealer_BTG_ID == 1 && VFistWsBtgPassed == false)
          {
           VFistWsBtgPassed = true;
           FirstWsBtg1 = 1;
          }
          else FirstWsBtg1 = 0;

          // update insert statement
          // insertStatement = insertStatement + "(" + Retailing_Dealer_ID + ", " + PrevSales_BTG_ID + ", NULL, " + Invoicing_Dealer_ID + ", " + Ordering_Dealer_ID + ", " + Update_LifecycleStatus_ID + ", " + Update_BTG_ID + ", " + PrevSales_LC_ID + ", " + (int)InputRow.GetInt64(0) + "),";

          try
          {

           insertStatement = insertStatement +
           "(" + InputRow.GetInt64(0) + //<PK_Sales, bigint,>
           ", " + (InputRow.IsDBNull(1) ? "NULL" : Convert.ToString(InputRow.GetInt64(1))) + //<FK_PrevSales, bigint,>
           ", '" + InputRow.GetString(2) + "'" +//<Commission_ID, nvarchar(255),>
           ", '" + InputRow.GetString(3) + "'" +//<ModelTypeFull_ID, nvarchar(255),>
           ", " + (InputRow.IsDBNull(4) ? "NULL" : Convert.ToString(InputRow.GetInt32(4))) + //<Vehicle_Brand_Code, int,>
           ", '" + InputRow.GetDateTime(5).ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<Effective_DateTime, datetime,>
           ", '" + InputRow.GetDateTime(6).ToString("yyyyMMdd HH:mm:ss.fff") + "'" + //<Effective_Date, datetime,>
           ", " + InputRow.GetInt32(7) + //<LifecycleStatus_ID, int,>
           ", " + InputRow.GetInt32(8) + //<PrevSales_LC_ID, int,>
           ", " + InputRow.GetInt16(9) + //<BusinessTypeGroup_ID, smallint,>
           ", " + InputRow.GetInt16(10) + //<PrevSales_BTG_ID, smallint,>
           ", " + InputRow.GetInt32(11) + //<Ordering_Dealer_ID, int,>
           ", " + InputRow.GetInt32(12) + //<Ordering_Dealer_BTG_ID, int,>
           ", " + InputRow.GetInt32(13) + //<Invoicing_Dealer_ID, int,>
           ", " + InputRow.GetInt32(14) + //<Retailing_Dealer_ID, int,>
           ", " + InputRow.GetInt32(15) + //<Retailing_Dealer_BTG_ID, int,>
           ", " + InputRow.GetInt32(16) + //<Disposability_ID, int,>
           ", " + Convert.ToInt32(InputRow.GetBoolean(17)) + //<LastVehicleStatus, smallint,>
           ", " + Convert.ToInt32(InputRow.GetBoolean(18)) + //<LastChangePerDay, smallint,>
           ", NULL " +//<StorageTime, bigint,>
           ", " + (StartDateStockMBCH.Equals(DateTime.MinValue) ? "NULL" : "'" + StartDateStockMBCH.ToString("yyyyMMdd HH:mm:ss.fff") + "'") + //<StartDateStockMBCH, datetime,>
           ", " + (StartDateStockDealer.Equals(DateTime.MinValue) ? "NULL" : "'" + StartDateStockDealer.ToString("yyyyMMdd HH:mm:ss.fff") + "'") + //<StartDateStockDealer, datetime,>
           ", '" + InputRow.GetDateTime(20).ToString("s") + "'" + //<EndDate, datetime,>
           ", " + InputRow.GetInt64(24) + //<Value, bigint,>
           ", " + StockOrderMBCH + //<StockOrderMBCH, bigint,>
           ", " + StockOrderBTG4 + //<StockOrderBTG4, bigint,>
           ", " + OrderBTG1 + //<OrderBTG1, bigint,>
           ", " + OrderBTG2 + //<OrderBTG2, bigint,>
           ", " + FactoryInvoice + //<FactoryInvoice, bigint,>
           ", " + Wholesale + //<Wholesale, bigint,>
           ", " + WarrantyNotificationCard + //<WarrantyNotificationCard, bigint,>
           ", " + Retail + //<Retail, bigint,>
           ", " + FirstRegistration + //<FirstRegistration, bigint,>
           ", " + StockEntryMBCH + //<StockEntryMBCH, bigint,>
           ", " + StockEntryDealer + //<StockEntryDealer, bigint,>
           ", " + OpenStockOrderMBCH + //<OpenStockOrderMBCH, bigint,>
           ", " + OpenStockOrderBTG4 + //<OpenStockOrderBTG4, bigint,>
           ", " + TotalOrdersMBCH + //<TotalOrdersMBCH, bigint,>
           ", " + TotalOrdersDealer + //<TotalOrdersDealer, bigint,>
           ", " + TotalStockMBCH + //<TotalStockMBCH, bigint,>
           ", " + TotalStockDealer + //<TotalStockDealer, bigint,>
           ", NULL " + //<StorageTimeClass, bigint,>
           ", " + VAFClearance + //<VAFClearance, smallint,>
           ", " + Convert.ToInt32(Done) + //<Done, bit,>
           ", " + FirstRegistrationAstra + //<FirstRegistrationAstra, bigint,>
           ", '" + InputRow.GetString(21) + "'" + //<ProductGroup_ID, varchar(255),>
           ", " + InputRow.GetInt32(23) + //<Cabin_Width_ID, int,>
           ", '" + InputRow.GetString(22) + "'" + //<Floor_Variant_ID, varchar(255),>
           ", " + (InputRow.IsDBNull(25) ? "NULL" : Convert.ToString(InputRow.GetInt32(25))) + //<Ordering_Dealer_ID_Prev, int,>
           ", " + InputRow.GetInt32(26) + //<RelevantDealerGroupChange, int,>
           ", " + (StartDateStockDealer2.Equals(DateTime.MinValue) ? "NULL" : "'" + StartDateStockDealer2.ToString("yyyyMMdd HH:mm:ss.fff") + "'") + //<StartDateStockDealer2, datetime,>
           ", " + FirstWsBtg1 + //<FirstWsBtg1, smallint,>
           "),";

          }
          catch (Exception ex)
          {
           MessageBox.Show("Statement construction error: " + ex.Message + " : " + ex.Source + " : " + ex.StackTrace + " : " + ex.Data);
           //Dts.Events.FireInformation(3, "Insert Values", "Statement construction error: " + ex.Message + ": " + ex.StackTrace, "", 0, ref fireAgain);
          }

          // fetch next Row, Attach Row to Output Buffer
          lastRow = !(InputRow.Read());

        }

        // handle final commit
        insertStatement = writeRows(sqlConnInsert, insertStatement, true);

       }

       private String writeRows(OleDbConnection sqlConnInsert, string insertStatement, bool forceCommit)
       {
        //throw new NotImplementedException();

        if (!insertStatement.Equals(""))
        {
          if (forceCommit || insertStatement.Length > 2000)
          {
           insertStatement = insertStatement.Remove(insertStatement.Length - 1);
           MessageBox.Show(insertStatement);
           OleDbCommand ic = new OleDbCommand(insertStatement, sqlConnInsert);
           ic.ExecuteNonQuery();
          }
          else
          {
           return insertStatement;
          }

        }

        return "INSERT INTO dbo.STG_F_Sales_Totals ( PK_Sales , FK_PrevSales , Commission_ID , ModelTypeFull_ID , Vehicle_Brand_Code , Effective_DateTime , Effective_Date , LifecycleStatus_ID , PrevSales_LC_ID , BusinessTypeGroup_ID , PrevSales_BTG_ID , Ordering_Dealer_ID , Ordering_Dealer_BTG_ID , Invoicing_Dealer_ID , Retailing_Dealer_ID , Retailing_Dealer_BTG_ID , Disposability_ID , LastVehicleStatus , LastChangePerDay , StorageTime , StartDateStockMBCH , StartDateStockDealer , EndDate , Value , StockOrderMBCH , StockOrderBTG4 , OrderBTG1 , OrderBTG2 , FactoryInvoice , Wholesale , WarrantyNotificationCard , Retail , FirstRegistration , StockEntryMBCH , StockEntryDealer , OpenStockOrderMBCH , OpenStockOrderBTG4 , TotalOrdersMBCH , TotalOrdersDealer , TotalStockMBCH , TotalStockDealer , StorageTimeClass , VAFClearance , Done , FirstRegistrationAstra , ProductGroup_ID , Cabin_Width_ID , Floor_Variant_ID , Ordering_Dealer_ID_Prev , RelevantDealerGroupChange , StartDateStockDealer2 , FirstWsBtg1 ) VALUES ";
       }

       #region ScriptResults declaration
       /// <summary>
       /// This enum provides a convenient shorthand within the scope of this class for setting the
       /// result of the script.
       ///
       /// This code was generated automatically.
       /// </summary>
       enum ScriptResults
       {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
       };
       #endregion
      }
    }

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply