Dynamics Community Forum Thread Details

The process seems to be running fine, but then after 15 mins aprox, I get a
(502) Bad Gateway.

Im on 365 V9, using SSIS

Here's the code I'm using

using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using Microsoft.Xrm.Sdk; using Microsoft.Xrm.Sdk.Client; using Microsoft.Xrm.Sdk.Messages; using Microsoft.Xrm.Sdk.Query; using Microsoft.Crm.Sdk.Messages; using System.Net; using System.Collections.Generic; using System.Web.Services.Protocols; using System.ServiceModel; using System.ServiceModel.Description; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { List<object> entityQueueUpdate = new List<object>(); List<object> entityQueueInsert = new List<object>(); bool pbCancel = true; int insertCount = 0; int updateCount = 0; OrganizationServiceProxy _service; Uri organizationURL; Uri homeRelmURL; ClientCredentials credentials; ClientCredentials deviceCreds; public override void PreExecute() { base.PreExecute(); credentials = new ClientCredentials(); credentials.UserName.UserName = Variables.CrmUsername; credentials.UserName.Password = Variables.CrmPassword; deviceCreds = Microsoft.Xrm.Tooling.Connector.DeviceIdManager.LoadOrRegisterDevice(); organizationURL = new Uri(Variables.CrmServiceURL); homeRelmURL = null; ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; _service = new OrganizationServiceProxy(organizationURL, homeRelmURL, credentials, deviceCreds); _service.Timeout = new TimeSpan(0, 20, 0); } public override void PostExecute() { base.PostExecute(); Variables.insertCount += insertCount; Variables.updateCount += updateCount; entityQueueUpdate.Clear(); entityQueueInsert.Clear(); } public override void Input0_ProcessInput(Input0Buffer Buffer) { base.Input0_ProcessInput(Buffer); if (Buffer.EndOfRowset()) { ProcessQueue(null, true, false); ProcessQueue(null, true, true); } } public void ProcessQueue(Entity dEnt, bool endOfRec, bool updateRec) { if (!endOfRec && updateRec) { entityQueueUpdate.Add(dEnt); } if (!endOfRec && !updateRec) { entityQueueInsert.Add(dEnt); } try { if (endOfRec || entityQueueInsert.Count > 600) { ExecuteMultipleRequest insertBatchRequest = new ExecuteMultipleRequest() { Settings = new ExecuteMultipleSettings() { ContinueOnError = true, ReturnResponses = true }, Requests = new OrganizationRequestCollection() }; foreach (Entity e in entityQueueInsert) { CreateRequest insertRequest = new CreateRequest(); insertRequest.Target = e; insertBatchRequest.Requests.Add(insertRequest); } ExecuteMultipleResponse executionInsertResponses; try { executionInsertResponses = (ExecuteMultipleResponse)_service.Execute(insertBatchRequest); } catch { _service = new OrganizationServiceProxy(organizationURL, homeRelmURL, credentials, deviceCreds); _service.Timeout = new TimeSpan(0, 20, 0); executionInsertResponses = (ExecuteMultipleResponse)_service.Execute(insertBatchRequest); } foreach (ExecuteMultipleResponseItem resp in executionInsertResponses.Responses) { if (resp.Response != null) { insertCount++; } else if (resp.Fault != null) { ErrorOutputBuffer.AddRow(); ErrorOutputBuffer.errorMessage = string.Format("Dynamics CRM batch row processing error: {0}", resp.Fault.Message); } } entityQueueInsert.Clear(); } if (endOfRec || entityQueueUpdate.Count > 500) { ExecuteMultipleRequest updateBatchRequest = new ExecuteMultipleRequest() { Settings = new ExecuteMultipleSettings() { ContinueOnError = true, ReturnResponses = true }, Requests = new OrganizationRequestCollection() }; foreach (Entity e in entityQueueUpdate) { UpdateRequest updateRequest = new UpdateRequest(); updateRequest.Target = e; updateBatchRequest.Requests.Add(updateRequest); } ExecuteMultipleResponse executionUpdateResponses; try { executionUpdateResponses = (ExecuteMultipleResponse)_service.Execute(updateBatchRequest); } catch { _service = new OrganizationServiceProxy(organizationURL, homeRelmURL, credentials, deviceCreds); _service.Timeout = new TimeSpan(0, 20, 0); executionUpdateResponses = (ExecuteMultipleResponse)_service.Execute(updateBatchRequest); } foreach (ExecuteMultipleResponseItem resp in executionUpdateResponses.Responses) { if (resp.Response != null) { updateCount++; } else if (resp.Fault != null) { ErrorOutputBuffer.AddRow(); ErrorOutputBuffer.errorMessage = string.Format("Dynamics CRM batch row processing error: {0}", resp.Fault.Message); } } entityQueueUpdate.Clear(); } } catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> f) { ComponentMetaData.FireWarning(0, ComponentMetaData.Name, string.Format("Fault Exception: Dynamics CRM batch processing error: {0}", f.Detail.Message), String.Empty, 0); ErrorOutputBuffer.AddRow(); ErrorOutputBuffer.errorMessage = string.Format("Fault Exception: Dynamics CRM batch processing error: {0}", f.Message); } catch (SoapException s) { ComponentMetaData.FireWarning(0, ComponentMetaData.Name, string.Format("Soap Exception: Dynamics CRM batch processing error: {0}", s.Message), String.Empty, 0); ErrorOutputBuffer.AddRow(); ErrorOutputBuffer.errorMessage = string.Format("Soap Exception: Dynamics CRM batch processing error: {0}", s.Message); } catch (Exception e) { ComponentMetaData.FireWarning(0, ComponentMetaData.Name, string.Format("Exception: Dynamics CRM batch processing error: {0}", e.Message), String.Empty, 0); ErrorOutputBuffer.AddRow(); ErrorOutputBuffer.errorMessage = string.Format("Exception: Dynamics CRM batch processing error: {0}", e.Message); } finally { //_service.Dispose(); } //} //catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> f) //{ // ComponentMetaData.FireError(0, ComponentMetaData.Name, "Fault Exception: " + f.Message, "", 0, out pbCancel); //} //catch (SoapException s) //{ // ComponentMetaData.FireError(0, ComponentMetaData.Name, "Soap Exception: " + s.Message, "", 0, out pbCancel); //} //catch (Exception e) //{ // ComponentMetaData.FireError(0, ComponentMetaData.Name, "Exception: " + e.Message, "", 0, out pbCancel); //} } public override void Input0_ProcessInputRow(Input0Buffer Row) { #region aaaa Entity dEnt = new Entity("account"); try { // Update to dynamic way to retrieve this value dEnt["ownerid"] = new EntityReference("systemuser", new Guid("02404C30-0D12-E611-80E0-FC15B4286A7C")); // CRM System User (PROD) dEnt["plus__customerid"] = Row.pluscustomerid; if (!Row.accountid_IsNull) { dEnt["accountid"] = Row.accountid; } if (!Row.name_IsNull) { dEnt["name"] = Row.name; } if (!Row.plusdivision_IsNull) { dEnt["plus__division"] = Row.plusdivision.ToString(); } if (!Row.plussalesorg_IsNull) { dEnt["plus__salesorg"] = Row.plussalesorg.ToString(); } if (!Row.plusfendersquirerep_IsNull) { dEnt["plus__fendersquirerep"] = new EntityReference("systemuser", Row.plusfendersquirerep); } else { dEnt["plus__fendersquirerep"] = null; } if (!Row.plusfenderterritory_IsNull) { dEnt["plus_fenderterritory"] = Row.plusfenderterritory; } else { dEnt["plus_fenderterritory"] = null; } if (!Row.plusspecialtyrep_IsNull) { dEnt["plus__specialtyrep"] = new EntityReference("systemuser", Row.plusspecialtyrep); } else { dEnt["plus__specialtyrep"] = null; } if (!Row.plusspecialtyterritory_IsNull) { dEnt["plus_specialtyterritory"] = Row.plusspecialtyterritory; } else { dEnt["plus_specialtyterritory"] = null; } if (!Row.pluscentralorderblock_IsNull) { dEnt["plus__centralorderblock"] = Row.pluscentralorderblock; } if (!Row.plussalesareablock_IsNull) { dEnt["plus_salesareablock"] = Row.plussalesareablock; } #region Address Information if (!Row.address1line1_IsNull) { dEnt["address1_line1"] = Row.address1line1; } if (!Row.address1line2_IsNull) { dEnt["address1_line2"] = Row.address1line2; } if (!Row.address1city_IsNull) { dEnt["address1_city"] = Row.address1city; } if (!Row.address1stateorprovince_IsNull) { dEnt["address1_stateorprovince"] = Row.address1stateorprovince; } if (!Row.address1postalcode_IsNull) { dEnt["address1_postalcode"] = Row.address1postalcode; } #endregion #region Contact Information if (!Row.emailaddress1_IsNull) { dEnt["emailaddress1"] = Row.emailaddress1; } if (!Row.telephone1_IsNull) { dEnt["telephone1"] = Row.telephone1; } if (!Row.fax_IsNull) { dEnt["fax"] = Row.fax; } #endregion if (!Row.plustaxid_IsNull) { dEnt["plus__taxid"] = Row.plustaxid; } Money creditLimit = new Money(0); if (!Row.pluscreditlimit_IsNull) { creditLimit = new Money(Row.pluscreditlimit); dEnt["plus__creditlimit"] = creditLimit; //dEnt["plus__creditlimit"] = new Money(Row.pluscreditlimit); } else { dEnt["plus__creditlimit"] = null; } if (!Row.paymenttermoptionsetvalue_IsNull) { dEnt["plus__paymentterms"] = new OptionSetValue(Row.paymenttermoptionsetvalue); } if (!Row.transactioncurrencyid_IsNull) { dEnt["transactioncurrencyid"] = new EntityReference("transactioncurrency", Row.transactioncurrencyid); } if (!Row.riskcategoryoptionsetvalue_IsNull) { dEnt["plus__riskcategory"] = new OptionSetValue(Row.riskcategoryoptionsetvalue); } Money creditBalance = new Money(0); if (!Row.pluscreditbalance_IsNull) { creditBalance = new Money(Convert.ToDecimal(Row.pluscreditbalance)); dEnt["plus__creditbalance"] = creditBalance; //dEnt["plus__creditbalance"] = new Money(Convert.ToDecimal(Row.pluscreditbalance)); } else { dEnt["plus__creditbalance"] = null; } //if (!Row.plusavailablecredit_IsNull) //{ // dEnt["plus__availablecredit"] = new Money(Row.plusavailablecredit); //} //else //{ // dEnt["plus__availablecredit"] = null; //} dEnt["plus__availablecredit"] = new Money(creditLimit.Value - creditBalance.Value); if (!Row.creditstatusoptionsetvalue_IsNull) { dEnt["plus__creditstatus"] = new OptionSetValue(Row.creditstatusoptionsetvalue); } if (!Row.pluscreditrep_IsNull) { dEnt["plus__creditrep"] = new EntityReference("systemuser", Row.pluscreditrep); } else { dEnt["plus__creditrep"] = null; } if (!Row.creditperferenceoptionsetvalue_IsNull) { dEnt["plus__creditpreference"] = new OptionSetValue(Row.creditperferenceoptionsetvalue); } if (!Row.plussalesoffice_IsNull) { dEnt["plus_salesoffice"] = Row.plussalesoffice; } if (!Row.plusgroupoptionsetvalue_IsNull) { dEnt["plus_group"] = new OptionSetValue(Row.plusgroupoptionsetvalue); } #endregion if (!Row.accountid_IsNull) { dEnt["accountid"] = Row.accountid; ProcessQueue(dEnt, false, true); } else { ProcessQueue(dEnt, false, false); } } catch (Exception e) { ErrorOutputBuffer.AddRow(); ErrorOutputBuffer.errorMessage = string.Format("Exception: Row preprocessing error: {0}", e.Message); } } public override void CreateNewOutputRows() { } }