{"id":9207,"date":"2020-12-30T18:29:57","date_gmt":"2020-12-30T18:29:57","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=9207"},"modified":"2026-03-05T01:25:27","modified_gmt":"2026-03-05T01:25:27","slug":"download-gmail-attachment-in-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/","title":{"rendered":"Download Gmail Attachment in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong>\u00a0ZappySys has released a brand new\u00a0Gmail Connector for SSIS<\/a>, which makes it much simpler to\u00a0download attachments, read \/ search Emails, users, mail folders, send emails, and more in SSIS<\/strong> compared to the steps listed in this article. You can still use the steps from this article, but if you are new to APIs or want to avoid the learning curve, use a newer approach.<\/div><\/div>\n

Downloading Gmail Attachments in SSIS is always a little more difficult than working with simple text or numeric columns. If you are not an SSIS expert, doing it for the first time may be challenging. That is why we created this article for you. This is a common request we receive, so we are bringing this article to you.<\/p>\n

Gmail is the most popular email service in the world, and it can be helpful to automate and get valuable information from it. We will connect to Gmail with the REST API. For that, if you are not an experienced user of the REST API, do not worry. This article is for you. We will provide simple steps for the connection.<\/p>\n

Requirements<\/h2>\n

To do it, we will need the following components:<\/p>\n

    \n
  1. SSDT for SSIS<\/a> must be installed on your computer<\/li>\n
  2. ZappySys SSIS Powerpack<\/a> should be installed. This Powerpack Plugin includes connectors to Gmail via the REST API.<\/li>\n<\/ol>\n

     <\/p>\n

    Getting Started<\/h2>\n

    Creating the connection<\/h3>\n
      \n
    1. First of all, in SSDT, create a new SSIS project<\/li>\n
    2. To create a connection in SSDT, open the Connection Manager and select New Connection.\n
    3. Select the ZS OAuth connection. This is a special connection included\u00a0with the ZS.
      \nSSIS PowerPack<\/a> installer that we will use to connect to Gmail.<\/p>\n
    4. In the ZS OAuth connection, select the Google API OAuth Provider and specify the following credentials and scope permissions:\n
      \"SSIS<\/a>

      SSIS Gmail connection<\/p><\/div>\n

      https:\/\/mail.google.com\/\r\nhttps:\/\/www.googleapis.com\/auth\/gmail.modify\r\nhttps:\/\/www.googleapis.com\/auth\/gmail.readonly\r\nhttps:\/\/www.googleapis.com\/auth\/gmail.addons.current.message.readonly\r\nhttps:\/\/www.googleapis.com\/auth\/gmail.addons.current.message.action<\/pre>\n<\/li>\n<\/ol>\n
      Creating the package to download Gmail attachments in SSIS<\/h3>\n
        \n
      1. First of all, drag and drop the Data Flow Task from the SSIS Toolbox and double-click it
        \nto edit.<\/p>\n
      2. Secondly, in the Data Flow, we will use the JSON Source to connect to\u00a0Gmail and get the attachments. JSON Source is a ZappySys component used now\u00a0only to connect to JSON files, but also to connect to REST API\u00a0Servers that support JSON. In this case, Gmail REST API can send information\u00a0in JSON Format. Use the connection created before.<\/li>\n
      3. Basically, we need to specify the URL of the Gmail attachment. In Gmail,\u00a0the email attachment is inside the message. Then you need to specify the\u00a0message-id<\/em> and the Gmail attachment-id<\/em> like this:\n
        \"Zappysys<\/a>
        Zappysys JSON source Gmail attachments configuration<\/p><\/div>\n
        https:\/\/www.googleapis.com\/gmail\/v1\/users\/me\/messages\/{{User::email_id}}\/attachments\/{{User::attachment_id}}<\/pre>\n<\/li>\n
      4. Now, in the go-to columns tab, set the data type to DT_IMAGE and increase the length of the other columns’ DT_WSTR fields by 1000 and 1500. We set contentBytes<\/span> to the DT_IMAGE<\/span>\u00a0 datatype, it’s like varbinary(MAX)<\/span>\n
      5. Next, we will use the derived column to set the path to the local folder where the attachment will be stored. The derived column will be used to set the local path for the attachment. Make sure to specify valid paths with double backslashes, since backslashes are special characters. The derived column is a special transformation component that creates new columns. SSIS expressions<\/a>.\n
      6. Also, we will require the Export column to download into a file. This\u00a0component is specially used to get images or binary files into the file\u00a0system.\n
      7. Finally, we will use the\u00a0Trash destination<\/a> to get the attachment and finish the flow. This one is also a ZappySys component ubiquitous.<\/li>\n
      8. Your package should be something like this:\n
      9. The next step is to run it and test it.<\/li>\n
      10. If everything is fine, we will be able to see the file in your system.<\/li>\n<\/ol>\n
        Conclusion<\/h2>\n
        In conclusion, we learned how to download Gmail attachments in SSIS. Basically, you need to use the ZappySys JSON Source to connect to Gmail via the REST API, then use the derived column component to specify the path to store the attachment. The next step is to use the Export column component to export the attachment. Finally, the ZS Trash destination is the attachment’s destination. If you liked this article, do not hesitate to continue testing and using ZappySys PowerPack for SSIS.<\/a><\/p>\n
        API Connector<\/h2>\n
        Here are a few examples with our API connector for Gmail <\/a><\/p>\n
        \"API<\/a>
        API connector – Gmail get message attachments<\/p><\/div>\n
        \"API<\/a>
        API connector – Gmail download message attachments<\/p><\/div>\n
         <\/p>\n","protected":false},"excerpt":{"rendered":"
        Introduction Downloading Gmail Attachments in SSIS is always a little more difficult than working with simple text or numeric columns. If you are not an SSIS expert, doing it for the first time may be challenging. That is why we created this article for you. This is a common request we receive, so we are […]<\/p>\n","protected":false},"author":5,"featured_media":11671,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[277,225],"tags":[587,531,306,596,12,595],"yoast_head":"\r\nDownload Gmail Attachment in SSIS in a simple way automatically<\/title>\r\n<meta name=\"description\" content=\"This article shows how to Download Gmail Attachment in SSIS using simple steps. For this article, we will need ZappySys to connect to Gmail\" \/>\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\/download-gmail-attachment-in-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Download Gmail Attachment in SSIS in a simple way automatically\" \/>\r\n<meta property=\"og:description\" content=\"This article shows how to Download Gmail Attachment in SSIS using simple steps. For this article, we will need ZappySys to connect to Gmail\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:published_time\" content=\"2020-12-30T18:29:57+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2026-03-05T01:25:27+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"722\" \/>\r\n\t<meta property=\"og:image:height\" content=\"229\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys Team\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/\",\"name\":\"Download Gmail Attachment in SSIS in a simple way automatically\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png\",\"datePublished\":\"2020-12-30T18:29:57+00:00\",\"dateModified\":\"2026-03-05T01:25:27+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e\"},\"description\":\"This article shows how to Download Gmail Attachment in SSIS using simple steps. For this article, we will need ZappySys to connect to Gmail\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png\",\"width\":722,\"height\":229,\"caption\":\"Create a new connection\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Download Gmail Attachment in SSIS\"}]},{\"@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\/91b041e2dcf7ece5f068893c1a68ac6e\",\"name\":\"ZappySys Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g\",\"caption\":\"ZappySys Team\"},\"sameAs\":[\"https:\/\/zappysys.com\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Download Gmail Attachment in SSIS in a simple way automatically","description":"This article shows how to Download Gmail Attachment in SSIS using simple steps. For this article, we will need ZappySys to connect to Gmail","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\/download-gmail-attachment-in-ssis\/","og_locale":"en_US","og_type":"article","og_title":"Download Gmail Attachment in SSIS in a simple way automatically","og_description":"This article shows how to Download Gmail Attachment in SSIS using simple steps. For this article, we will need ZappySys to connect to Gmail","og_url":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/","og_site_name":"ZappySys Blog","article_published_time":"2020-12-30T18:29:57+00:00","article_modified_time":"2026-03-05T01:25:27+00:00","og_image":[{"width":722,"height":229,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png","type":"image\/png"}],"author":"ZappySys Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ZappySys Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/","url":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/","name":"Download Gmail Attachment in SSIS in a simple way automatically","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png","datePublished":"2020-12-30T18:29:57+00:00","dateModified":"2026-03-05T01:25:27+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/91b041e2dcf7ece5f068893c1a68ac6e"},"description":"This article shows how to Download Gmail Attachment in SSIS using simple steps. For this article, we will need ZappySys to connect to Gmail","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2020\/12\/Create-a-new-connection.png","width":722,"height":229,"caption":"Create a new connection"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/download-gmail-attachment-in-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Download Gmail Attachment in SSIS"}]},{"@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\/91b041e2dcf7ece5f068893c1a68ac6e","name":"ZappySys Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/746bec9c9d27f1b90bb181aa516ee234?s=96&d=mm&r=g","caption":"ZappySys Team"},"sameAs":["https:\/\/zappysys.com"],"url":"https:\/\/zappysys.com\/blog\/author\/dcalbimonte\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9207"}],"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\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=9207"}],"version-history":[{"count":10,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9207\/revisions"}],"predecessor-version":[{"id":11668,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/9207\/revisions\/11668"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/11671"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=9207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=9207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=9207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}