Using SSISComponent connection to Mailchimp Extremely Slow

Discussion of issues, suggestions and bugs of Devart SSIS Data Flow Components, our product line for building SSIS-based ETL solutions, performing data access to popular cloud applications and databases.
Post Reply
malchim
Posts: 23
Joined: Tue 22 Nov 2016 16:21

Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Thu 30 May 2019 14:29

Hi I am wondering why our downloads from Mailchimp are so slow. It is pulling roughly 2000 records a minute. WE also are getting this error message NCAListMemberTags:Error: Devart.Data.MailChimp.MailChimpException (0x80004005) ---> System.Net.WebException: The remote server returned an error: (503) Server Unavailable.
at System.Net.HttpWebRequest.GetResponse()
at Devart.Data.Rest.d.in(String A_0, String A_1, Stream A_2, String A_3, Dictionary`2 A_4, a A_5, Nullable`1 A_6, Nullable`1 A_7, Nullable`1 A_8, String A_9)
at Devart.Data.bb.a[a](Boolean A_0, Int32 A_1, Func`1 A_2)
at Devart.Data.bb.a[a](Boolean A_0, Func`1 A_1)
at Devart.Data.MailChimp.g.ic(aa A_0, o A_1)
at Devart.Data.MailChimp.i.io()
at Devart.Data.b1.bpx()
at Devart.Data.bs.bpx()
at Devart.Data.bs.bpx()
at Devart.Data.bp.Read()
at Devart.SSIS.SqlShim.SourceComponent.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPrimeOutput(IDTSManagedComponentWrapper100 wrapper, Int32 outputs, Int32[] outputIDs, IDTSBuffer100[] buffers, IntPtr ppBufferWirePacket)

Pinturiccio
Devart Team
Posts: 2406
Joined: Wed 02 Nov 2011 09:44

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by Pinturiccio » Fri 31 May 2019 14:53

Please specify the MailChimp entity, from which you loaded data. And specify the API version that is used in your connection: v2 or v3.

malchim
Posts: 23
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Mon 03 Jun 2019 14:55

We are connecting to the SentTo Table the API Version is 3. We are also having the same issue with the campaigns table.

Pinturiccio
Devart Team
Posts: 2406
Joined: Wed 02 Nov 2011 09:44

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by Pinturiccio » Mon 03 Jun 2019 15:55

The SentTo object is read in the following way: first we read all the parent records from the Campaigns object and then perform a separate request for each Campaign to read its SentTo. If you have a lot of campaigns, it can take a long time. For more information, please refer to https://developer.mailchimp.com/documen ... s/sent-to/

The Campaigns records can be read slowly as well, especially if you select ContentHtml or ContentText fields, because these fields are also read via a separate request for each record. If your select query doesn't contain these fields, the query will take less time.

malchim
Posts: 23
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Mon 03 Jun 2019 17:26

Ok I am wondering why pulling from the Sentto table produces this error?
Here is my sql

SELECT CampaignId
,Email
,EmailHash
,Listd
,Status
,OpenCcount
,LastOpen
FROM SentTo



RADSentTo:Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on m12 returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.


RADSentTo:Error: Devart.Data.MailChimp.MailChimpException (0x80004005): <!DOCTYPE html>
<html role='document'>
<head>
<meta charset='utf-8'>
<meta content='en' name='language'>
<meta content='IE=edge' http-equiv='X-UA-Compatible'>
<meta content='width=device-width, initial-scale=1' name='viewport'>
<title>403</title>
<style type='text/css'>
h1{font-family:"Helvetica Neue",Arial,Helvetica,sans-serif;-webkit-font-smoothing:antialiased}h1{text-rendering:optimizeLegibility}h1{letter-spacing:-0.03em;font-size:40px;line-height:1.2;font-weight:bold}p{font-size:16px;line-height:1.6875;font-weight:normal}html,body{background:#f2f2f2;font-family:"Helvetica Neue",Arial,Helvetica,sans-serif;color:#3a3a3a;margin:0;padding:0;text-align:center}a{color:#52bad5;text-decoration:none}a:hover{text-decoration:underline;color:#2c9ab7}h1{margin:0}p{margin:6px 0 0 0}.content{display:inline-block;text-align:left;width:100%;max-width:520px;padding-top:20vmin}.freddie{width:25%;max-width:108px;height:0;padding-bottom:25%;margin:18px au...

malchim
Posts: 23
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Mon 03 Jun 2019 18:08

Hi I just noticed the mailchimp server returned a message with a description of blocked. It says it looks like there is something suspicious please contact Mailchimp with the reference id.

Pinturiccio
Devart Team
Posts: 2406
Joined: Wed 02 Nov 2011 09:44

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by Pinturiccio » Wed 05 Jun 2019 15:35

This error occurs on the MailChimp side when numerous requests are sent to their server. In this case Akamai is blocking access to MailChimp from a particular IP.

All MailChimp API requests from your IP at a certain point in time are taken into account. We can recommend not to run many SSIS tasks against MailChimp at the same time.

Post Reply