Endpoint

Add or Update Upsert List Members (Bulk Subscribe or Unsubscribe)

Name

upsert_list_members

Description

Add or Update Upsert List Members (Bulk Subscribe or Unsubscribe). [API reference]

Related Tables

ListMembers , ListMembersBulk

Parameters

Parameter

Label

Required

Options

Description

ListId

List Id

YES

The ID of the list for which you like to perform this operation.

SkipMergeValidation

Skip Merge Validation

Option	Value
true	true
false	false

If skip_merge_validation is true, member data will be accepted without merge field values, even if the merge field is usually required. This defaults to false.

SkipDuplicateCheck

Skip Duplicate Check

Option	Value
true	true
false	false

If skip_duplicate_check is true, we will ignore duplicates sent in the request when using the batch sub/unsub on the lists endpoint. The status of the first appearance in the request will be saved. This defaults to false.

UpdateExisting

Update Exising Subscription Status?

Option	Value
true	true
false	false

Whether this batch operation will change existing members' subscription status.

SyncTags

Sync Tags

Option	Value
true	true
false	false

Whether this batch operation will replace all existing tags with tags in request.

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw
Id	`DT_WSTR`	`nvarchar(200)`	200	`False`
ListId	`DT_WSTR`	`nvarchar(200)`	200	`False`
EmailAddress	`DT_WSTR`	`nvarchar(200)`	200	`False`
UniqueEmailId	`DT_WSTR`	`nvarchar(200)`	200	`False`
ContactId	`DT_WSTR`	`nvarchar(200)`	200	`False`
FullName	`DT_WSTR`	`nvarchar(100)`	100	`False`
WebId	`DT_I4`	`int`		`False`
EmailType	`DT_WSTR`	`nvarchar(20)`	20	`False`
Status	`DT_WSTR`	`nvarchar(20)`	20	`False`
ConsentsToOneToOneMessaging	`DT_BOOL`	`bit`		`False`
FirstName	`DT_WSTR`	`nvarchar(50)`	50	`False`
LastName	`DT_WSTR`	`nvarchar(50)`	50	`False`
AddressLine1	`DT_WSTR`	`nvarchar(200)`	200	`False`
AddressLine2	`DT_WSTR`	`nvarchar(200)`	200	`False`
City	`DT_WSTR`	`nvarchar(200)`	200	`False`
State	`DT_WSTR`	`nvarchar(100)`	100	`False`
Zip	`DT_WSTR`	`nvarchar(20)`	20	`False`
Country	`DT_WSTR`	`nvarchar(100)`	100	`False`
Phone	`DT_WSTR`	`nvarchar(50)`	50	`False`
Birthday	`DT_WSTR`	`nvarchar(50)`	50	`False`
StatsAvgOpenRate	`DT_I4`	`int`		`False`
StatsAvgClickRate	`DT_I4`	`int`		`False`
IpSignup	`DT_WSTR`	`nvarchar(200)`	200	`False`
TimestampSignup	`DT_DBTIMESTAMP`	`datetime`		`False`
IpOpt	`DT_WSTR`	`nvarchar(200)`	200	`False`
TimestampOpt	`DT_DBTIMESTAMP`	`datetime`		`False`
MemberRating	`DT_I4`	`int`		`False`
LastChanged	`DT_DBTIMESTAMP`	`datetime`		`False`
Language	`DT_WSTR`	`nvarchar(100)`	100	`False`
Vip	`DT_BOOL`	`bit`		`False`
EmailClient	`DT_WSTR`	`nvarchar(200)`	200	`False`
Latitude	`DT_I4`	`int`		`False`
Longitude	`DT_I4`	`int`		`False`
LocationGmtOff	`DT_I4`	`int`		`False`
LocationDstOff	`DT_I4`	`int`		`False`
CountryCode	`DT_WSTR`	`nvarchar(100)`	100	`False`
TimeZone	`DT_WSTR`	`nvarchar(100)`	100	`False`
Region	`DT_WSTR`	`nvarchar(100)`	100	`False`
Source	`DT_WSTR`	`nvarchar(100)`	100	`False`
TagsCount	`DT_I4`	`int`		`False`
Tags	`DT_WSTR`	`nvarchar(4000)`	4000	`False`
RowStatus	`DT_WSTR`	`nvarchar(50)`	50	`False`
TotalCreated	`DT_I4`	`int`		`False`
TotalUpdated	`DT_I4`	`int`		`False`
ErrorCount	`DT_I4`	`int`		`False`
ErrorMessage	`DT_WSTR`	`nvarchar(4000)`	4000	`False`
ErrorCode	`DT_WSTR`	`nvarchar(255)`	255	`False`
ErrorField	`DT_WSTR`	`nvarchar(255)`	255	`False`
ErrorFieldMessage	`DT_WSTR`	`nvarchar(4000)`	4000	`False`

If the column you are looking for is missing, consider customizing Mailchimp Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Raw	Description
EmailAddress	`DT_WSTR`	`nvarchar(200)`	200	`False`
Status	`DT_WSTR`	`nvarchar(80)`	80	`False`	Subscriber's current status. Possible values: subscribed, unsubscribed, cleaned, pending, or transactional.
EmailType	`DT_WSTR`	`nvarchar(20)`	20	`False`	Type of email this member asked to get ('html' or 'text').
FirstName	`DT_WSTR`	`nvarchar(255)`	255	`False`
LastName	`DT_WSTR`	`nvarchar(255)`	255	`False`
AddressLine1	`DT_WSTR`	`nvarchar(200)`	200	`False`
AddressLine2	`DT_WSTR`	`nvarchar(200)`	200	`False`
City	`DT_WSTR`	`nvarchar(200)`	200	`False`
State	`DT_WSTR`	`nvarchar(100)`	100	`False`
Zip	`DT_WSTR`	`nvarchar(20)`	20	`False`
Country	`DT_WSTR`	`nvarchar(100)`	100	`False`
Phone	`DT_WSTR`	`nvarchar(50)`	50	`False`
Birthday	`DT_WSTR`	`nvarchar(50)`	50	`False`
MergeFields	`DT_WSTR`	`nvarchar(4000)`	4000	`True`	A dictionary of merge fields where the keys are the merge tags.
Interests	`DT_WSTR`	`nvarchar(4000)`	4000	`True`	The key of this object's properties is the ID of the interest in question.
Language	`DT_WSTR`	`nvarchar(10)`	10	`False`	If set/detected, the subscriber's language.
Vip	`DT_BOOL`	`bit`		`False`	VIP status for subscriber.
Latitude	`DT_WSTR`	`nvarchar(4000)`	4000	`False`	The location latitude.
Longitude	`DT_WSTR`	`nvarchar(4000)`	4000	`False`	The location longitude.
MarketingPermissions	`DT_WSTR`	`nvarchar(4000)`	4000	`True`	The marketing permissions for the subscriber.
IpSignup	`DT_WSTR`	`nvarchar(200)`	200	`False`	IP address the subscriber signed up from.
TimestampSignup	`DT_DBTIMESTAMP`	`datetime`		`False`	The date and time the subscriber signed up for the list in ISO 8601 format.
IpOpt	`DT_WSTR`	`nvarchar(200)`	200	`False`	The IP address the subscriber used to confirm their opt-in status.
TimestampOpt	`DT_DBTIMESTAMP`	`datetime`		`False`	The date and time the subscriber confirmed their opt-in status in ISO 8601 format.
Tags	`DT_WSTR`	`nvarchar(4000)`	4000	`True`	The tags that are associated with a member.

Required columns that you need to supply are bolded.

Examples

SSIS

Use Mailchimp Connector in API Source component to read data or in API Destination component to read/write data:

Upsert rows into ListMembers table using API Destination

This Endpoint belongs to ListMembers table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Mailchimp

ListMembers

Upsert

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Bulk Add / Update MailChimp Subscibers (members) from Microsoft SQL Server Table - Subscribe / Unsubscribe

UPSERT INTO ListMembers
SOURCE('MSSQL', 'Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true'
,'select ''brucewayne10@gmail.com'' EmailAddress,''first1'' as FirstName, ''subscribed'' Status 
UNION ALL
select ''test55@gmail.com'' EmailAddress,''first1'' FirstName, ''subscribed'' Status 
')
WITH(ListId='a4d24015f8')

 --//column name alias must match with InputColumns of ListMembers

Bulk Add / Update Subscibers (list members) from CSV File - Subscribe / Unsubscribe

UPSERT INTO ListMembers
SOURCE('ODBC', 'Driver={ZappySys CSV Driver};DataPath=c:\subscribers.csv'
,'select col1 as EmailAddress,col2 as FirstName, col3 as Status from $') --//column name alias must match with InputColumns of ListMembers
WITH(ListId='a4d24015f8')

Bulk Add / Update Subscibers (list members) from any ODBC Source - Subscribe / Unsubscribe

UPSERT INTO ListMembers
SOURCE('ODBC', 'DSN=MyOdbcDsn
,'select col1 as EmailAddress,col2 as FirstName, col3 as Status from sometable') --//column name alias must match with InputColumns of ListMembers
WITH(ListId='a4d24015f8')

upsert_list_members endpoint belongs to ListMembers , ListMembersBulk table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Bulk Add / Update MailChimp Subscibers (members) from Microsoft SQL Server Table - Subscribe / Unsubscribe

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO ListMembers
SOURCE(''MSSQL'', ''Data Source=localhost;Initial Catalog=tempdb;Integrated Security=true''
,''select ''''brucewayne10@gmail.com'''' EmailAddress,''''first1'''' as FirstName, ''''subscribed'''' Status 
UNION ALL
select ''''test55@gmail.com'''' EmailAddress,''''first1'''' FirstName, ''''subscribed'''' Status 
'')
WITH(ListId=''a4d24015f8'')

 --//column name alias must match with InputColumns of ListMembers';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Bulk Add / Update Subscibers (list members) from CSV File - Subscribe / Unsubscribe

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO ListMembers
SOURCE(''ODBC'', ''Driver={ZappySys CSV Driver};DataPath=c:\subscribers.csv''
,''select col1 as EmailAddress,col2 as FirstName, col3 as Status from $'') --//column name alias must match with InputColumns of ListMembers
WITH(ListId=''a4d24015f8'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

Bulk Add / Update Subscibers (list members) from any ODBC Source - Subscribe / Unsubscribe

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO ListMembers
SOURCE(''ODBC'', ''DSN=MyOdbcDsn
,''select col1 as EmailAddress,col2 as FirstName, col3 as Status from sometable'') --//column name alias must match with InputColumns of ListMembers
WITH(ListId=''a4d24015f8'')';

EXEC (@MyQuery) AT [LS_TO_MAILCHIMP_IN_GATEWAY];

upsert_list_members endpoint belongs to ListMembers , ListMembersBulk table(s), and can therefore be used via those table(s).