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: 27
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)
Top
Pinturiccio
Devart Team
Posts: 2420
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.
Top
malchim
Posts: 27
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.
Top
Pinturiccio
Devart Team
Posts: 2420
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.
Top
malchim
Posts: 27
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...
Top
malchim
Posts: 27
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.
Top
Pinturiccio
Devart Team
Posts: 2420
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.
Top
malchim
Posts: 27
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Tue 30 Jul 2019 18:01

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
Top
Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by Shalex » Thu 01 Aug 2019 17:54

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.
Top
malchim
Posts: 27
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Mon 09 Sep 2019 15:11

API 3
Sento table
Top
Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by Shalex » Tue 10 Sep 2019 10:52

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.
Top
malchim
Posts: 27
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Tue 10 Sep 2019 14:39

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?
Top
malchim
Posts: 27
Joined: Tue 22 Nov 2016 16:21

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by malchim » Thu 12 Sep 2019 18:06

Any updates?
Top
Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Using SSISComponent connection to Mailchimp Extremely Slow

Post by Shalex » Thu 12 Sep 2019 18:56

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.
Top
Post Reply

Return to “SSIS Data Flow Components”