Use SQL Server Integration Services to Add HubSpot Contacts to MailChimp Lists

SQL Server Integration Services (SSIS) is a platform for data integration and workflow applications, generally dealing with SQL Server databases. With the CData SSIS Components, you extend the integration capabilities to include data from more than 100 SaaS, Big Data, and NoSQL sources, allowing you to easily consolidate business processes like converting a QuickBooks purchase order to a Salesforce invoice or replicating NetSuite data to a Google BigQuery instance.

In this article, we walk through using the CData SSIS Components for HubSpot and MailChimp to create a data flow that adds new HubSpot contacts to a MailChimp list. If you have not already, download and install the HubSpot SSIS Components and MailChimp SSIS Components.

Connecting to HubSpot and MailChimp

Adding HubSpot contacts to a MailChimp list requires the email address of the HubSpot contact and the ID of the MailChimp list. Use a CData MailChimp Source component and a CData HubSpot Source component to query the needed information.

Getting the MailChimp List ID

Add a CData MailChimp Source component to retrieve the ID of the MailChimp list for the new email addresses.

1. Add a CData MailChimp Source component to the Data Flow task.

2. Double-click the component and add a new connection manager.

   MailChimp uses your account APIKey or OAuth to authenticate the application. The APIKey is generated from the account settings in MailChimp. For OAuth authentication, obtain the OAuthClientId, OAuthClientSecret, and CallbackURL by registering an app with MailChimp.

   See the "Getting Started" chapter in the help documentation for a guide to using OAuth.

3. After configuring the connection, set Data access mode to "SQL Command."
4. Set the SQL command text to a SELECT statement filtering by the name of the MailChimp list:

   SELECT [Id] FROM [Lists] WHERE [Name] = 'SAMPLE'
   

5. Click OK.

Getting New HubSpot Contacts

Add a CData HubSpot Source component to retrieve recently added contacts to be added to the MailChimp list.

1. Add a CData HubSpot Source component to the Data Flow task.

2. Double-click the component and add a new connection manager.

   HubSpot uses the OAuth authentication standard. You can use the embedded OAuthClientId, OAuthClientSecret, and CallbackURL or you can obtain your own by registering an App with HubSpot.

   See the "Getting Started" chapter of the help documentation for a guide to using OAuth.

3. After configuring the connection, set the Data access mode to "SQL Command."
4. Set the SQL command text to a SELECT statement retrieving the email address of the Contact. This query is a placeholder for a parameterized query in the Expression Builder.

   SELECT [Email] FROM [Contacts]
   

5. Click OK.
6. Navigate back to the Control Flow for the SSIS Project and click anywhere on the design surface.
7. From the SSIS menu, click Variables.
8. In the Variables pane, click to add a new variable.
9. Set Namespace to "User," Name to "Status," Data type to "String" and Value to "subscribed" or "unsubscribed" (depending on your preferences).
10. Navigate to the Data Flow and click anywhere on the design surface.
11. In the Properties pane, click the button to open the Expressions property.
12. In the resulting Property Expressions Editor, click an empty row in the Property box and select the SQLStatement property of the CData HubSpot Source component from the drop-down menu. Next, click the button in the row you just added to display the Expression Builder.
13. In the Expression box, you can create new SQL commands that use the variables available at runtime as input parameters. Ensure that you enclose the expression in quotes. This expression retrieves the contacts added in the past 30 days and sets Status as a constant based on the User variable.

    "SELECT [Email], '" + @[User::Status] + "' AS [Status] FROM [Contacts] WHERE Date > '" + (DT_WSTR, 50) DATEADD("day", -30, GETDATE()) + "'"
    

Combining HubSpot and MailChimp Data

After connecting to and retrieving data from HubSpot and MailChimp, combine the data in a Union All component before adding the contacts to a MailChimp list.

1. Add a Union All component to the design surface.
2. Drag the Outputs from the MailChimp and HubSpot Source components to the Input for the Union All component.
3. Double-click the Union All, configure the columns as follows and click OK:

   Output Column Name	Union All Input 1 (MailChimp)	Union All Input 1 (HubSpot)
   ListId	Id	<ignore>
   Email	<ignore>	Email
   Status	<ignore>	Status

Adding New MailChimp List Members

With the data combined and retrieved, you are ready to add new HubSpot contacts to the MailChimp list.

1. Add a CData MailChimp Destination component to the design surface.
2. Drag the Output from the Union All component to the Input for the MailChimp Destination component.
3. Double-click the MailChimp Destination component, select the CData MailChimp Connection Manager.
4. Set the Use a table field to "[ListMembers]" and set the Action field to "Insert."
5. On the Mappings page, map the columns as follows and click OK.

   Input Column	Destination Column
   Email	EmailAddress
   Status	Status
   ListId	ListId

More Information & Free Trial

With the CData Software SSIS Components, you get standards-based, SQL-like access to your data in SQL SSIS projects, no matter the data source. While this article focused on adding new HubSpot contacts to MailChimp lists, the principles could be applied to work with any entities from the 100+ SaaS, Big Data, or NoSQL sources that CData supports. Get started with free, 30-day trials of the CData HubSpot SSIS Components and CData MailChimp SSIS Components. As always, our world-class Support Team is available if you have any questions.