Itgenoda035 on Active Campaign with DealActivities

I keep getting an error message relating to an invalid JSON for the OData. But when I look into the error message through Bridge Online Monitoring I get a different error:
The remote server returned an error: (500) Internal Server Error.

Error: itgenoda035
Exception:
De verbinding met de website is onverwacht en met geweld verbroken om 06/24/2024 14:05:33 voor redenen zoals onvoldoende rechten, het vastlopen van de website, het opnieuw opstarten van het systeem, het hard afsluiten van een externe host of firewallactie.
Het ophalen van gegevens is mislukt voor alle 3 pogingen gedurende 67 seconden.
Neem contact op met de API-ondersteuning van ActiveCampaign. (https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900).
De ActiveCampaign-server heeft een interne fout geretourneerd, meestal is dat een applicatiefout binnen ActiveCampaign.
Neem contact op met de ondersteuning van ActiveCampaign met de verzamelde oproepgegevens van het native platform of stel een vraag op de Invantive-forums met deze oproepgegevens. (https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900).
The remote server returned an error: (500) Internal Server Error.

I did contact ActiveCampaign, but they do not have enough information as of right now. And gave the option of including someone from ActiveCampaign in the email thread to resolve the issue.

The reason for the itgenoda035 error is that ActiveCampaign repeatedly rejects a connection with a HTTP 500 Internal Server Error on the Active Campaign API server.

In the past, ActiveCampaign server problems have frequently been seen often during night hours in the United States, typically between 12:00 and 16:00 CET. As a workaround you might want to schedule a refresh using PowerBI.com during business hours of ActiveCampaign HQ.

It is not clear what data would be needed, but assistance on internal server errors on ActiveCampaign and other connected platforms can be acquired in the form of a short consult using the chatbot.

As an alternative you might want to try increasing the number of tries by extending the connection string on the data container of the database to include download-error-500-internal-server-error-max-tries with a higher value. Please note however that when the error is consistent, the download can run for significant longer time without making any progress. More documentation on this is available at SQL Driver for ActiveCampaign API - ActiveCampaign API Data Model.

When the problem reproduces also on Invantive Query Tool, you might also want to try to collect the data yourself using the tips on What API calls did Invantive UniversalSQL actually perform?.

Despite running the refresh during US working hours the JSON error persists.

Thanks for the feedback.

It is recommended to use the alternatives given in the previous reply.

The Internal Server Error of ActiveCampaign API server is not considered a bug in the Invantive-software and requires corrective action by ActiveCampaign. In case ActiveCampaign needs reproduction information, this can be collected by following the steps in the last hyperlink in the previous reply.

It seems to be a problem when the data is pivoted. Loading the data works, renaming and merging the data with another table works fine too.
When I go to the pivot step the error message pops up. Any idea on how that can happen?

No, it is highly unlikely that both events relate.

To go for safe by hindering query folding, you might want to add a Table.Buffer step.

Analyzed with consultant.

All pages of data can be retrieved, but the 100 deals starting at 1900 can not be retrieved and consistently reports an error. Tested using:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900'
)

By reducing the page size the cause could further be zoomed into to be the deal at offset 1964 and 1966. Query used:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=1&offset=1964'
)

When queried one-by-one, the queries worked. The JSON payload for a deal is typically 10-20 KB, but for 1964 (deal ID 732) it is 16 MB and for 1966 (deal ID 734) it is 25 MB of JSON.

The payload of deal with ID 732 (offset 1964) was analyzed using:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=1&offset=1964'
)

select j.*
from   nativeplatformscalarrequests@acn t
join   jsontable 
       ( 'dealActivities[*]' 
         passing t.result_text
         columns d_id varchar2 path 'd_id'
         , id varchar2 path 'id'
         , dataType varchar2 path 'dataType'
         , d_stageid varchar2 path 'd_stageid'
         ) j
where    t.transaction_id = 23 /* Taken from query above. */

This revealed that there were 20.064 activities on that deal with data type connection_email:

dataType Count
connection_email 20064
note 3
userid 2
subscriberid 2
1
d_stageid 1
task 1

There are many emails on https://site.eu/app/deals/732, but it does not seem realistic that there are over 20.000 in ActiveCampaign.

Filtering manually on the dataType is possible using:

insert into NativePlatformScalarRequests@acn
( url
) 
values 
( 'https://acme.api-us1.com/api/3/deals?include=dealActivities&limit=100&offset=1900&filters[data_type]=d_stageid'
)

However, even with filter applied, the ActiveCampaign API server does not respond within 60 seconds.

The timeout was increased using:

set http-get-timeout-max-ms@acn 360000

set http-get-timeout-ms@acn 360000

but still the ActiveCampaign API server returned HTTP 500.

Besides these two deals, there are no other deals with extreme large numbers of activities.

Suggested solutions are:

  • Reach out to ActiveCampaign Support to request a bug fix on there server to better handle deals with these numbers of connection emails.
  • Reach out to ActiveCampaign Support to remove all unnecessary connection_email activities from these two tasks.
  • Archive and delete the two deals.

Removing the two deals has not worked.
When refreshing the data, the powerbi error keeps existing. Bridge Online Monitoring does not show an error at the moment.

It is recommended to execute the other suggested solutions.