SSIS stands for SQL Server Integration Services and is a platform for building data integrations & workflow applications that integrate with SQL Server databases. As data integration processes become increasingly important, organizations are looking for ways to simplify their business processes.

In this article, we'll show you how to design a data flow that adds new HubSpot contacts to a MailChimp list using CData SSIS Components for HubSpot and MailChimp.

Connecting to HubSpot and MailChimp

The email address of the HubSpot contact and the ID of the MailChimp list are required when adding HubSpot contacts to a MailChimp list. To get the information you need, use a CData MailChimp Source component and a CData HubSpot Source component.

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.

1. After configuring the connection, set Data access mode to "SQL Command."

2. Set the SQL command text to a SELECT statement filtering by the name of the MailChimp list:

view source

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

1. Click OK.

Getting New HubSpot Contacts

Add a CData HubSpot Source component to collect 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.

1. After configuring the connection, set the Data access mode to "SQL Command."

2. 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.

view source

SELECT [Email] FROM [Contacts]

1. Click OK.

2. Navigate back to the Control Flow for the SSIS Project and click anywhere on the design surface.

3. From the SSIS menu, click Variables.

4. In the Variables pane, click to add a new variable.

5. Set Namespace to "User," Name to "Status," Data type to "String" and Value to "subscribed" or "unsubscribed" (depending on your preferences).

1. Navigate to the Data Flow and click anywhere on the design surface.

2. In the Properties pane, click the button to open the Expressions property.

3. 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.

4. 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.

1. Double-click the Union All, configure the columns as follows and click OK:

Output Column Name: ListId | Email | Status
Union All Input 1 (MailChimp): Id | ignore | ignore
Union All Input 1 (HubSpot): | Email | 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.

1. Double-click the MailChimp Destination component, select the CData MailChimp Connection Manager.

2. Set the Use a table field to "[ListMembers]" and set the Action field to "Insert."

3. On the Mappings page, map the columns as follows and click OK.

       Input Column      |      Destination Column 
          Email                   EmailAddress
         Status                     Status
         ListId                     ListId 
   

More Information

Data integration becomes simple, and any developer can complete with minimum code when paired with a connector that supports standard relational interfaces. Companies choose the CData Software SSIS Components, to obtain standards-based, SQL-like access to data in SQL SSIS projects.

While this article focused mainly on adding new HubSpot contacts to MailChimp lists, the principles can be applied to any entities from the 100+ SaaS, Big Data, or NoSQL sources that CData supports.