Page 1 of 1
Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Thu 30 May 2019 14:29
by malchim
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)
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Fri 31 May 2019 14:53
by Pinturiccio
Please specify the MailChimp entity, from which you loaded data. And specify the API version that is used in your connection: v2 or v3.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Mon 03 Jun 2019 14:55
by malchim
We are connecting to the SentTo Table the API Version is 3. We are also having the same issue with the campaigns table.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Mon 03 Jun 2019 15:55
by Pinturiccio
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.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Mon 03 Jun 2019 17:26
by malchim
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...
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Mon 03 Jun 2019 18:08
by malchim
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.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Wed 05 Jun 2019 15:35
by Pinturiccio
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.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Tue 30 Jul 2019 18:01
by malchim
Hi we were wondering if you could help on an issue we are having.
As far as we can tell we are connecting to our Radio account and our CHC Connecticut Account using API Version 3.
We are using the Devart SSIS Component to connect to Mailchimp and download our data.
We are connecting to the Listmembers table for both accounts. We are seeing different fields for this table when looking at the
Connecticut Account compared to the Radio Account.
We can’ t find a reason why this is happening.
The fields all appear to be custom fields that we are missing in the Connecticut Account.
For the Connecticut account we see the following fields
ID
Email
List Id
Email Hash
Email Type
Status
UniqueEmailID
Language
VIP
SignupiP
Signup Timestamp
OptinIP
Optin Timestamp
MemberRating
EmailClient
UnsubscribeReason
Interests
Tags
TagsCount
Location_Latitude
Location_Longitude
Location_GMTOffset
Location_DSTSOffset
Location_Timezone
Location_CountryCode
AverageOpen_Rate
AverageClick_Rate
LastNoteId
LastUpdate
CreatedDate
For the Radio Account We see the following
ID
Email
List Id
Email Hash
Email Type
Status
UniqueEmailID
Language
VIP
SignupiP
Signup Timestamp
OptinIP
Optin Timestamp
MemberRating
EmailClient
UnsubscribeReason
Interests
Tags
TagsCount
Location_Latitude
Location_Longitude
Location_GMTOffset
Location_DSTSOffset
Location_Timezone
Location_CountryCode
AverageOpen_Rate
AverageClick_Rate
LastNoteId
City
First Name
Last Name
Middle
Zip Code
Address
Address 2
Organization
State
Title
LastUpdate
CreatedDate
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Thu 01 Aug 2019 17:54
by Shalex
1. Check the value of the Merge Fields Detection Behavior connection string parameter (via Devart MailChimp Connection Manager Editor > Advanced) in both connection strings, is it the same?
Refer to:
*
https://www.devart.com/dotconnect/mailc ... tring.html
*
https://www.devart.com/dotconnect/mailc ... ustom.html
2. If some custom fields (merge tags) are missing in the Connecticut account, you can create them via the web interface of MailChimp.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Mon 09 Sep 2019 15:11
by malchim
API 3
Sento table
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Tue 10 Sep 2019 10:52
by Shalex
malchim wrote: ↑Mon 09 Sep 2019 15:11
API 3
Sento table
Please give us a detailed description of the current issue you have encountered.
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Tue 10 Sep 2019 14:39
by malchim
When attempting to retrieve data from the sento table we are experiencing the following.
If we use a parameter such as select * from sento where lastopen >= '7/1/2019' we are noticing the query does not resolve.
If we use the query without a parameter it will pull over 6 million records and mailchimp will close the connection before it resolves. Is my syntax for the query using the parm valid?
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Thu 12 Sep 2019 18:06
by malchim
Any updates?
Re: Using SSISComponent connection to Mailchimp Extremely Slow
Posted: Thu 12 Sep 2019 18:56
by Shalex
malchim wrote: ↑Tue 10 Sep 2019 14:39If we use a parameter such as select * from sento where lastopen >= '7/1/2019' we are noticing the query does not resolve.
1. Are you getting any error? If so, specify it exact text.
malchim wrote: ↑Tue 10 Sep 2019 14:39If we use the query without a parameter it will pull over 6 million records and mailchimp will close the connection before it resolves.
2. Please open Devart MailChimp Connection Manager Editor, press the Advanced button, increase your value of Default Command Timeout.
malchim wrote: ↑Tue 10 Sep 2019 14:39Is my syntax for the query using the parm valid?
3. '7/1/2019' is a constant (not parameter). Usage of parameters is described at
viewtopic.php?f=60&t=34065.