{"id":291,"date":"2016-01-08T14:42:45","date_gmt":"2016-01-08T14:42:45","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=291"},"modified":"2025-03-19T05:25:27","modified_gmt":"2025-03-19T05:25:27","slug":"get-data-from-mailchimp-in-ssis-using-rest-api-call","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/","title":{"rendered":"How to get data from MailChimp in SSIS with REST API"},"content":{"rendered":"

Introduction<\/h2>\n
NOTE: UPDATE:<\/strong>\u00a0ZappySys has released a brand new\u00a0API Connector for SSIS Mailchimp Connector<\/a>\u00a0which makes it much simpler to Read \/ write MailChimp Campaigns, Lists, Members, Reports\u00a0in SSIS<\/strong>\u00a0compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n

Please visit\u00a0this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in\u00a0SSIS API Source<\/a>\u00a0\/\u00a0SSIS API Destination<\/a>\u00a0OR\u00a0API ODBC Driver<\/a>\u00a0(for non-SSIS Apps such as Excel, Power BI, Informatica).<\/div><\/div>\n

\"\"<\/a>In this article you will learn how to automate\/integrate MailChimp API calls inside SSIS<\/strong> without any coding efforts. Historically anytime you want to access API or Automate workflow you have to use SDKs or write custom script which can be very time consuming and expensive effort. If you are looking for drag and drop approach rather than coding for your MailChimp API integration then this article is perfect for your need.<\/p>\n

To call MailChimp REST API inside SSIS we you will need to download SSIS PowerPack<\/a>.\u00a0Following SSIS task\/components can help you to call any API and parse JSON response into table format (i.e. Rows and Columns) which you can write to any Destination later (i.e. Load MailChimp data into SQL Server or other way around).<\/p>\n

\n\n\n\n\n
\"Custom<\/td>\nJSON Source Connector (File, REST API)<\/a><\/td>\n<\/tr>\n
\"Custom<\/td>\nREST API Webservice Task<\/a><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

Calling MailChimp API two different ways in SSIS<\/h2>\n

MailChimp API offers two different ways to authenticate your API requests.<\/p>\n

    \n
  1. Use OAuth 2.0 Authorization<\/li>\n
  2. Use Basic Authorization (Use API Key)<\/li>\n<\/ol>\n

    Method-1 : Use OAuth 2.0 Authentication – Call MailChimp API using SSIS REST API Task<\/h3>\n

    This section will show you how to call MailChimp API using OAuth 2.0 protocol. Simplest way to test our MailChimp API call is use REST API Webservice Task<\/a>.<\/p>\n

      \n
    1. Download and Install SSIS PowerPack (Click here to download<\/span><\/a>). It includes 40+ components which can help you with any JSON or API Integration scenario inside SSIS<\/li>\n
    2. Open \/ Create SSIS Project<\/li>\n
    3. Drag ZS REST API Task from SSIS Toolbox<\/li>\n
    4. Double click REST API Task to configure it<\/li>\n
    5. Set Url Access mode to [Url from Connection]<\/li>\n
    6. From connection dropdown select New OAuth Connection<\/li>\n
    7. Select Provider = MailChimp<\/li>\n
    8. Click Generate Token and once done Click Test and OK to save UI\n
    9. Once you are back to REST API Task enter following partial URL in URL box. Notice that we have not supplied full URL because depending on which region you are MailChimp may be using different URL for API access (e.g. https:\/\/us14.api.mailchimp.com\/3.0\/campaigns ). If you don’t specify full URL then OAuth connection manager automatically determines correct Data center for you and prepares correct URL at runtime.\n
      \/3.0\/campaigns<\/pre>\n<\/li>\n
    10. Click Test Request – Scroll to see Content (you should see JSON like below)\n
      \"Call<\/a>
      Call MailChimp API using SSIS REST API Task (Use OAuth 2.0 Connection Manager)<\/p><\/div><\/li>\n<\/ol>\n
      Method-2 : Use Basic Authentication – Call MailChimp API using SSIS REST API Task<\/h3>\n
      Simplest way to test our MailChimp API call is use REST API Webservice Task<\/a>.<\/p>\n
        \n
      1. Download and Install SSIS PowerPack (Click here to download<\/span><\/a>). It includes 40+ components which can help you with any JSON or API Integration scenario inside SSIS<\/li>\n
      2. Open \/ Create SSIS Project<\/li>\n
      3. Drag ZS REST API Task from SSIS Toolbox<\/li>\n
      4. Double click REST API Task to configure it<\/li>\n
      5. Set Url Access mode to [Url from Connection]<\/li>\n
      6. From connection dropdown select New HTTP Connection<\/li>\n
      7. Enter any valid URL e.g. https:\/\/us14.api.mailchimp.com\/3.0\/campaigns (If you are not sure what is your DC then try to call https:\/\/login.mailchimp.com\/oauth2\/metadata URL first and get response via REST API Task – Test to see api_endpoint URL)\n
        https:\/\/us14.api.mailchimp.com\/3.0\/campaigns<\/pre>\n<\/li>\n
      8. Click use credentials. Click Use userid\/password option<\/li>\n
      9. Enter any fake userid (this is ignored) and Valid API Key in Password field. If you are not sure how to get Mailchimp API Key then check this article<\/a><\/li>\n
      10. Click OK to save HTTP connection<\/li>\n
      11. Click Test Request – Scroll to see Content (you should see JSON like below)<\/li>\n<\/ol>\n
        Get data from\u00a0MailChimp API and load into SQL Server<\/h2>\n
        In previous section we saw how to use REST API Task to call any MailChimp API. It can do POST \/ PUT \/ DELETE try of requests. Another scenario could be read data from MailChimp and load into SQL Server (i.e. your local Data warehouse). For this purpose you can use JSON Source. It can parse JSON response into Rows and columns. Also it supports pagination so you can loop through response.<\/p>\n
          \n
        1. Download and Install SSIS PowerPack (Click here to download<\/span><\/a>). It includes 40+ components which can help you with any JSON or API Integration scenario inside SSIS<\/li>\n
        2. Open \/ Create SSIS Project<\/li>\n
        3. Drag and drop Data flow task in SSIS designer from SSIS Toolbox\n
          \"\"
          Dragging and dropping Data Flow Task into Control Flow<\/p><\/div><\/li>\n
        4. Double click Data flow to open Data flow designer<\/li>\n
        5. From SSIS toolbox find ZS JSON Source and drag on the surface<\/li>\n
        6. Double click to configure JSON source<\/li>\n
        7. Set Access mode to [Path or URL from Connection]<\/li>\n
        8. Before entering URL check option [Use credentials] and Select existing OAuth connection manager created in Previous section (i.e. Method-1 : Use OAuth)<\/li>\n
        9. Now we have to enter API URL. First determine endpoint assigned to your account. For that enter https:\/\/login.mailchimp.com\/oauth2\/metadata and click preview. Copy url from api_endpoint column found on the preview window. Once copied click OK to close<\/li>\n
        10. Now Use API endpoint URL (copied from previous step) and append version number (i.e. \/3.0) and API (i.e. \/campaigns) you like to call. Your final URL may be something like below. Our DC was us14 yours can be different.\n
          https:\/\/us14.api.mailchimp.com\/3.0\/campaigns<\/pre>\n<\/li>\n
        11. Once you enter correct API URL click on Select Filter button to select correct hierarchy you want to extract. If API response contains records inside JSON array then select that as your filter. For example if you getting list of all campaigns then your Filter may be like $.campaigns[*]<\/li>\n
        12. Once filter is specified, click Preview.<\/li>\n
        13. Click on columns tab to review columns and metadata. You can change default length or datatype. If you change default metadata then make sure to check Lock option (last column – right side) so it stays there when metadata refresh occurs<\/li>\n
        14. Click OK to save UI<\/li>\n
        15. Now you can connect your JSON source to any destination such as SQL Server (Use OLEDB or ADO.net Destination component)<\/li>\n
        16. Execute SSIS Package to see data in destination<\/li>\n<\/ol>\n
          \"Load<\/a>
          Load MailChimp data into SQL Server – Use SSIS JSON Source to call REST API<\/p><\/div>\n
          MailChimp REST API Pagination<\/h2>\n
          If you have many records then most likely your response will be partial. To fetch all records from MailChimp you have to use Pagination technique. Read this article for more information<\/a><\/p>\n
          Page number is indicated by offset query string parameter in URL as below. 0 mean first page. When pagination method is used on JSON source it will automatically increment offset and fetch data from next URL until no more records found for selected expression in JSON (e.g. $.campaigns[*] )<\/p>\n
          \"MailChimp<\/a>
          MailChimp API Pagination using SSIS JSON Source<\/p><\/div>\n
          Conclusion<\/h2>\n
          As you saw how easy it is to consume MailChimp API using SSIS without any coding. SSIS PowerPack<\/a> is one of the finest toolset for virtually any API or JSON integration project. Click here to Try SSIS PowerPack for FREE<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
          Introduction In this article you will learn how to automate\/integrate MailChimp API calls inside SSIS without any coding efforts. Historically anytime you want to access API or Automate workflow you have to use SDKs or write custom script which can be very time consuming and expensive effort. If you are looking for drag and drop […]<\/p>\n","protected":false},"author":1,"featured_media":1630,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[225,17],"tags":[215,6,57,60,25,160,3,12,4,144],"yoast_head":"\r\nHow to get data from MailChimp in SSIS with REST API | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to get data from MailChimp in SSIS using REST API Calls. Easy to use drag and drop approach without coding will make your integration super easy.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to get data from MailChimp in SSIS with REST API | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to get data from MailChimp in SSIS using REST API Calls. Easy to use drag and drop approach without coding will make your integration super easy.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2016-01-08T14:42:45+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-03-19T05:25:27+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"210\" \/>\r\n\t<meta property=\"og:image:height\" content=\"210\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/\",\"url\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/\",\"name\":\"How to get data from MailChimp in SSIS with REST API | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png\",\"datePublished\":\"2016-01-08T14:42:45+00:00\",\"dateModified\":\"2025-03-19T05:25:27+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to get data from MailChimp in SSIS using REST API Calls. Easy to use drag and drop approach without coding will make your integration super easy.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png\",\"width\":210,\"height\":210},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to get data from MailChimp in SSIS with REST API\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to get data from MailChimp in SSIS with REST API | ZappySys Blog","description":"Learn how to get data from MailChimp in SSIS using REST API Calls. Easy to use drag and drop approach without coding will make your integration super easy.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/","og_locale":"en_US","og_type":"article","og_title":"How to get data from MailChimp in SSIS with REST API | ZappySys Blog","og_description":"Learn how to get data from MailChimp in SSIS using REST API Calls. Easy to use drag and drop approach without coding will make your integration super easy.","og_url":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2016-01-08T14:42:45+00:00","article_modified_time":"2025-03-19T05:25:27+00:00","og_image":[{"width":210,"height":210,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/","url":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/","name":"How to get data from MailChimp in SSIS with REST API | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png","datePublished":"2016-01-08T14:42:45+00:00","dateModified":"2025-03-19T05:25:27+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to get data from MailChimp in SSIS using REST API Calls. Easy to use drag and drop approach without coding will make your integration super easy.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2016\/01\/mailchimp-api-integration.png","width":210,"height":210},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/get-data-from-mailchimp-in-ssis-using-rest-api-call\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to get data from MailChimp in SSIS with REST API"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/291"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=291"}],"version-history":[{"count":19,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/291\/revisions"}],"predecessor-version":[{"id":11243,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/291\/revisions\/11243"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/1630"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=291"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=291"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=291"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}