{"id":4537,"date":"2018-08-04T10:29:59","date_gmt":"2018-08-04T10:29:59","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4537"},"modified":"2025-04-08T15:47:07","modified_gmt":"2025-04-08T15:47:07","slug":"read-constant-contact-data-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-constant-contact-data-ssis\/","title":{"rendered":"How to read Constant Contact data in SSIS \u2013 Call REST API \/ Load to SQL Server"},"content":{"rendered":"

Introduction<\/h2>\n

 <\/p>\n

\"Constant<\/a>In this blog, we will learn\u00a0How to read Constant Contact data in SSIS and load into SQL Server Table<\/strong>, along with few other topics such as how to generate an API Token using Mashery Developer Account for Constant Contact REST API Call<\/strong>, how to read all Contacts from Constant Contact with SSIS and finally How to add new Contact in Constant Contact Contact List Collection.<\/p>\n

We will go through the steps to read Contact data from Constant Contact and Load into SQL Server using SSIS JSON Source.<\/a><\/p>\n

In nutshell,\u00a0this post will focus on how to call Constant Contact API using SSIS. If you are MailChimp user and curious how to perform data integration in SSIS then check this article about reading MailChimp data in SSIS<\/a>.<\/p>\n

So let’s get started.<\/p>\n

Prerequisite
\n<\/span><\/h2>\n
    \n
  1. First, you will need to have SSIS installed<\/li>\n
  2. Secondly, make sure to have SSDT<\/li>\n
  3. Finally, do not forget to install\u00a0ZappySys\u00a0SSIS PowerPack<\/a><\/li>\n<\/ol>\n

    What is Constant Contact<\/h2>\n

    Keeping in touch with customers on a consistent basis is one key to maintaining current clients and bringing in new ones. A number of companies exist that provide businesses with a way to contact clients regularly through snail mail, email or social media. Constant Contact offers online marketing services for businesses to stay in touch with clients.<\/p>\n

    Getting Started<\/h2>\n

    In order to start, we will show several examples. ZappySys includes an SSIS REST API Task<\/a>\u00a0and SSIS JSON Source<\/a>\u00a0that will help you to call Constant Contact API to read Constant Contact data, retrieve lists of Contacts data from Constant\u00a0Contact, Add new Contact in Constant Contact with SSIS and do many more actions using REST API. To learn more about Constant Contact API check this help file<\/a>.<\/p>\n

    Setup your Mashery Developer Account<\/h3>\n

    To access Constant Contact data using Mashery Developer Account you need to register a MasheryID with Constant Contact.\u00a0Follow the steps mentioned below to register Mashery Developer Account for Constant Contact Access:<\/p>\n

      \n
    1. first of all, you can directly login to the Constant Contact site<\/a> with your credentials, and redirect to the Mashrey Developer Console<\/a>\u00a0to register a MasheryID.<\/li>\n
    2. thereafter Fillup the form and click on Register button\n
    3. If you have already registered on Mashery Developer Account, then you just have to log in using your Credentials.\n
    4. So, when you are done with the Mashery, you can go to MyApplication Page<\/a>\u00a0to create a new application.<\/li>\n<\/ol>\n

      Create Constant Contact App (for OAuth)<\/h3>\n

      To access Constant Contact data using REST API call you need to create a Mashery app.\u00a0Follow the steps mentioned below to register Constant Contact OAuth App:<\/p>\n

        \n
      1. First of all, you can directly login to the Mashery Developer Account<\/a> with your credentials, and redirect to the Constant Contact My Application’s\u00a0Page for creating an app.<\/li>\n
      2. also, you can open the\u00a0MyApps Page<\/a>.\n
      3. Click on Create a New Application button while, the new Application form appears, Fill it up.\n
      4. Once you are done with the Creation of an App certainly, you can edit the Application.
        \nEnter the Redirect URL there.<\/p>\n
      5. furthermore, Go to Keys tab and you have your App’s Lists there with App’s Key as Client ID and Secret as Client Secret.\n
        \"Constant<\/a>

        Constant Contact: Apps Keys<\/p><\/div><\/li>\n<\/ol>\n

        finally, we are ready with an App. Let\u00b4s start with an example. In this example, we will show how to retrieve all Contacts data from Constant Contact and add a new Contact in Contact Collection List on Constant contact with SSIS.<\/p>\n

        Load Constant Contact data to SQL Server using SSIS JSON Source<\/h3>\n

        Now once you registered OAuth app in the Constant Contact website we can move to SSIS Part.<\/p>\n

        Configure JSON Source Component<\/h4>\n
          \n
        1. First of all, Create a new SSIS Project or Open Existing Project in Visual Studio\u00a0 (File > New > Project > Business Intelligence > Integration Services)<\/li>\n
        2. Once SSIS Package is open, Goto Data Flow Section, Drag and Drop ZS JSON Source Component<\/a> from the SSIS Toolbox.\n
        3. furthermore, Double click on that Component to Edit the Configurations. Select File path or web URL from the Access Mode drop-down.
          \nEnter Web URL like this:<\/p>\n
          https:\/\/api.constantcontact.com\/v2\/contacts?status=ALL&limit=100&api_key=<<<Your_App_Key>>><\/pre>\n
          here,\u00a0We have used 2 Parameters.<\/strong><\/p>\n
          * status<\/strong>\u00a0is the filter the response based on the contact status property: ALL, ACTIVE, UNCONFIRMED, OPTOUT, or REMOVED.
          \n<\/strong>* limit <\/strong>is the number of contacts to return per page, 1-500, default 50 (For more See this\u00a0          Constant Contact API Documentation for getting Contacts Request API<\/a>)
          \n*\u00a0<<<Your_App_Key>>> <\/strong>is your App Key, So you need to add your app key.<\/p>\n
          You can use Variable too in your Request URL, to make URL Dynamic<\/strong>, See the below-attached Screenshot.
          \nTo make URL dynamic first create a SSIS variable like below screenshot and then change URL to use           Variable Placeholder<\/a> like this<\/p>\n
          https:\/\/api.constantcontact.com\/v2\/contacts?status=ALL&limit=100&api_key={{User::AppKey}}<\/strong><\/em><\/p>\n
          \"SSIS<\/a>
          SSIS User Variables: Create a new Variable and set Value<\/p><\/div>\n
          \"SSIS<\/a>
          SSIS JSON Source Component: Enter the Request URL<\/p><\/div><\/li>\n<\/ol>\n
          Configure OAuth Connection Manager<\/h4>\n
            \n
          1. Check Use Credentials Option and select a\u00a0new ZS-OAuth Connection from the drop-down.<\/li>\n
          2. Configure the new OAuth Connection for Constant Contact as below.
            \nBasically, you need to configure the configurations of App on the General tab (i.e. Client ID, Client Secret, Authorization URL, Access Token URL)<\/p>\n
            \"SSIS<\/a>
            SSIS OAuth Connection Manager: General Tab Configurations<\/p><\/div>\n
            also, most importantly, you need to Enter your Callback URL in Advanced Tab.<\/p>\n
            \"SSIS<\/a>
            SSIS OAuth Connection Manager: Advance Tab Configurations.<\/p><\/div><\/li>\n<\/ol>\n
            JSON Source Configuration for Pagination<\/h4>\n
              \n
            1. \u00a0now, we are going to set Pagination related configurations for Constant Contact Getting all Contacts request. Here in our example, we are setting Page size as 5. while We need to read the next page information from the response.
              \nSo, we can set the status=ALL, Limit=100<\/strong> and also we can specify Next Link Expression something like this: $.meta.pagination.next_link <\/strong>and Suffix for Next URL:\u00a0?api_key={{User::AppKey}}
              \n<\/strong><\/strong><\/p>\n
            2. furthermore Select the Filter to set the Filter. seems like you are all OK with the configurations, Click on Preview button to see the response data.\n
            3. finally, Click on OK button to save JSON Source settings.<\/li>\n<\/ol>\n
              Configure OLE DB Destination Component for dumping Contacts data into SQL<\/h4>\n
                \n
              1. first of all, Drag any destination (i.e. OLEDB Destination) from your SSIS toolbox. For demo purpose, we will load Contacts data into SQL Server Table.\n
              2. Configure OLEDB Destination (Select Connection and Target Table). You can Create a new Table for the destination by clicking a NEW button next to Table drop-down.\n
              3. almost you are done with the configurations, just need to configure mappings, So for that Click on the Columns Tab to configure Mappings. also, Click on OK button to Save the configurations of OLE DB Destination Component.\n
              4. furthermore, You can see the requests in Fiddler<\/a>\u00a0to see the Paginated requests flows.\n
              5. finally, Execute your package to Load Contacts data from Constant Contact to SQL Server.<\/li>\n<\/ol>\n
                Add a new Contact on Constant Contact (Use SSIS REST API Task)<\/h2>\n
                Now let’s look at one more example to call Constant Contact REST API. This time we will add a new contact using REST API call in SSIS. For this, we will use\u00a0SSIS REST API Task<\/a><\/p>\n
                  \n
                1. First\u00a0of All, Goto Control Flow Section, Drag and Drop\u00a0SSIS REST API Task<\/a>\u00a0from the SSIS Toolbox.\n
                2. furthermore, Double click on that Component to Edit the Configurations and Configure the task like the below-attached screesnshot.\n
                  Request URL: \r\nhttps:\/\/api.constantcontact.com\/v2\/contacts?action_by=ACTION_BY_OWNER&api_key=<<<YOUR_APP_KEY>>>\r\n\r\nhere, action_by Can be ACTION_BY_OWNER or ACTION_BY_VISITOR (from the API Document,Click here for more Informaion<\/a>)\r\nSample Request Body: \r\n{\r\n\t\"addresses\": [{\r\n\t\t\"line1\": \"4928 Small Street\",\r\n\t\t\"city\": \"New York\",\r\n\t\t\"address_type\": \"PERSONAL\",\r\n\t\t\"state_code\": \"MA\",\r\n\t\t\"country_code\": \"us\",\r\n\t\t\"postal_code\": \"10022\"\r\n\t}],\r\n\t\"lists\": [{\r\n\t\t\"id\": \"1652428712\"\r\n\t}],\r\n\t\"email_addresses\": [{\r\n\t\t\"email_address\": \"hbshah@zappysys.com\"\r\n\t}],\r\n\t\"prefix_name\": \"Mr.\",\r\n\t\"first_name\": \"Hardik\",\r\n\t\"last_name\": \"Shah\",\r\n\t\"job_title\": \"Tech Supporter\",\r\n\t\"company_name\": \"ZappySys\",\r\n\t\"home_phone\": \"555-555-5555\",\r\n\t\"work_phone\": \"555-555-5555\",\r\n\t\"cell_phone\": \"555-555-5555\",\r\n\t\"fax\": \"555-555-5555\",\r\n\t\"custom_fields\": [{\r\n\t\t\"name\": \"CustomField1\",\r\n\t\t\"value\": \"Has control of $25 million budget\"\r\n\t}]\r\n}<\/pre>\n
                  \"REST<\/a>
                  REST API Task: Configurations<\/p><\/div><\/li>\n
                3. now you are done with the configurations. you can do Test Request\/Response. See the below-attached request\/response screen.\n
                4. finally, you have successfully added a contact. furthermore, Goto your Constant Contact Account on its site, you can see the latest Contact added which is added by us with the use of ZappySys and SSIS.\n
                5. though, You are done with adding a contact in Constant Contact.\u00a0similarly, you can Update\/Delete particular Contact in Contact Collection List.<\/li>\n<\/ol>\n
                  Conclusion<\/span><\/h2>\n
                  In this blog, we learned how to Setup your Mashery Developer Account, Create Constant Contact App for OAuth, Get all Contacts data from Constant Contact and add a new contact on Constant Contact with SSIS using\u00a0ZS JSON Source Component<\/a>\u00a0and\u00a0SSIS REST API Task<\/a> in a very simple way. You can achieve many more functionalities with the use of both of these tools. Check our blogs\/articles on REST API Task\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-rest-api-task\/<\/a>\u00a0and JSON Source Component\u00a0<\/strong>https:\/\/zappysys.com\/blog\/tag\/ssis-json-source\/<\/a> to find out what\u00a0these tools<\/em>\u00a0are capable of more.<\/p>\n
                  References<\/span><\/h2>\n
                  Finally, you can use the following links for more information about the use of Constant Contact API with our tools:<\/p>\n