SSIS Usage To import Hubspot Contacts To MailChimp Lists
What are SQL server databases?
The Microsoft Structured Query Language (SQL) server database is a Relational Database Management System (RDBMS) that stores a wide variety of data, which can be retrieved by other applications. The SQL Server Integration Services (SSIS) is a platform to build workflow applications and data that integrate with SQL server databases.
The data is stored in the form of rows and columns in RDBMS as the name suggests.
On the other hand, CData is a company that provides the SSIS feature for connecting Cloud Apps, Databases, Data Warehouses and many more. It provides a powerful component for transferring your mass data from one source to another with advantages like codeless integrations and data-centric architectures.
The C Data SSIS component is more helpful for Customer Relationship Management (CRM) that helps companies stay connected with customers. HubSpot and MailChimp are a type of CRM. While HubSpot is an inbound marketing and sales software, MailChimp is an all-in-one integrated marketing platform. HubSpot’s CRM platform has all the integrations and tools you need for marketing, sales, customer services and content management. It will help you create compelling content from the right people and convert visitors into leads and leads into customers.
Mailchimp helps you to manage and talk to your customers, clients and other parties. Its approach to marketing focuses on healthy contact management practices, designed campaigns, and powerful data analysis. With CData SSIS components, the integration capabilities to include data from 100+ SaaS (Software as a Service), Big Data, and NoSQL sources increase. They allow you to easily combine business processes. As data integration processes increase in importance, organizations are looking for ways to simply integrate and manage workflows.
Here you will learn how to create a data flow that adds HubSpot contacts to a MailChimp list using the CData SSIS component of both HubSpot and MailChimp:
1. How to integrate HubSpot with MailChimp
Adding Hubspot contacts to a Mailchimp list requires the email address of the Hubspot contacts. This is because Hubspot takes the contacts email address as a primary key (that can differentiate contacts with one another) and the identity of the Mailchimp list. To get the details, use a CData Hubspot Source component and Mailchimp source component.
2. How to retrieve the Mailchimp list ID
Add a CData Mailchimp Source component to retrieve/fetch the IDs of the Mailchimp list for the new email address.In a Data Flow task add a CData MailChimp Source component.
-
Add a new connection manager by double-clicking on the component.
-
To authenticate the application MailChimp uses either your account APIKey (used to access your MailChimp account) or OAuth. You will find the APIKey in your account settings in MailChimp. By registering an application with a MailChimp account you obtain the OauthClientId, OauthClientSecret, and CallbackURL for OAuth authentication.
-
Set Data access mode to "SQL Command" after configuration of connection.
-
For filtering the name of the MailChimp list, use SQL SELECT statement with the WHERE clause
The SELECT statement is used to retrieve information from a specific field and the WHERE clause is used to filter records.It is used to extract only those records that fulfill a specified condition.
-
Then Click OK.
3. Getting new HubSpot contacts by MailChimp SQL database integration
Add a CData HubSpot Source component to the MailChimp list by fetching the recently added contacts.
1. Add a CData HubSpot source component in a data flow task.
2. Double click the component to add a new connection manager
HubSpot uses the OAuth authentication by default. You can use the credentials by registering an app with hubspot or use the embedded credentials OAuthClientId, OAuthClientSecret, and CallbackURL.
3. Set the data access mode to "SQL Command" after configuring the connection.
4. Set the SQL command text through SELECT statement retrieve the email address of the Contact.
5. Then Click OK.
6. Click on the design surface by navigating back to the Control Flow for the SSIS Project.
7. Click Variables from the SSIS menu.
8. In the Variables pane, for adding a new variable click on “add a new variable.”
9. Set the following Namespace to "User” :
- Data type to "String"
- Name to "Status”
- Value to "subscribed" or "unsubscribed" (depending on your preference)
10. Navigate to the Data Flow and click on the design surface.
11. To open the Expressions property click on the button in the Properties pane.
12. In the appearing Property Expressions Editor, click on an empty row in the property field and select the SQL Statement property of the CData HubSpot Source component from the drop-down menu. Then, click on the button in the row you recently added to display the Expression Builder.
13. In the Expression field, you can create new SQL commands that use the variables available at runtime as input parameters. Make sure that you can enclose the expression in quotes. This expression fetches the contacts that can be recently added in the past 30 days and sets Status as a constant based on the User variable.
4. Combining HubSpot and MailChimp data
In order to combine the Hubspot and MailChimp data, an appropriate connection is in place. Before adding the contacts to a MailChimp list, you must combine the data in a “Union All” component.
1. In the design surface add a Union All component.
2. To get the input for the Union All component you must drag the outputs from the MailChimp and HubSpot Source components.
3. Double-click the Union All Operator and configure the columns as given in a tabular form:
|
Output Column Name |
Union All Input (MailChimp) |
Union All Input (HubSpot) |
| 0.00 | Id | |
| Status | Status |
4. Then click ok.
5. Adding new MailChimp list members
You are now ready to add new HubSpot contacts to the MailChimp list with the data combined and retrieved.
1. In the design surface add a CData MailChimp Destination component.
2. Drag the output to the input of the MailChimp Destination component from the Union All component.
3. Select the CData MailChimp Connection Manager by double-click the MailChimp Destination component
4. Place the “use a table” field to "[ListMembers]" and the Action field to "Insert."
5. On the mappings page, map the columns email by email address, status by status and listId by listId and then click OK.
|
Input |
Destination |
| EmailAddress | |
| Status | Status |
| ListId | ListId |
Conclusion
You can get SQL -like and standard-based access to your data by using CData SSIS Components in SQL Server SSIS projects no matter what the data source is.
This blog is mainly focused on how SSIS Components make data integration simple and reduce the need for any custom code to provide standard relational interfaces. Companies choose CData SSIS Components to obtain standards-based, SQL-like access to data in SQL SSIS projects.
CData provides 30 days’ free access to the CData HubSpot SSIS Components and CData MailChimp SSIS Components.
