{"id":5601,"date":"2018-12-16T19:28:17","date_gmt":"2018-12-16T19:28:17","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5601"},"modified":"2018-12-19T16:32:56","modified_gmt":"2018-12-19T16:32:56","slug":"read-html-table-ssis-extract-download-links-images","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/","title":{"rendered":"Read HTML Table in SSIS – Extract \/ Download Links \/ Images"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"In this post you will learn how to extract data from web pages using SSIS. In other words, read HTML Table in SSIS, then Loop through extracted links and finally download files. To achieve this scenario we will use\u00a0SSIS HTML Source<\/a>\u00a0and REST API Task<\/a>.<\/p>\n

 <\/p>\n

 <\/p>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Step-By-Step<\/h2>\n

    To achieve desired extraction from web pages \/ download files we have to use few different components. Let’s get started.<\/p>\n

    Read HTML Table from Web Page<\/h3>\n

    First, let’s read desired content from web URL using\u00a0 \u00a0SSIS HTML Source<\/a>. This component can read data from URL(s), Local HTML File(s) or Direct HTML String stored in a SSIS variable.<\/p>\n

      \n
    1. Create a new SSIS Project<\/li>\n
    2. In the control flow, Right click anywhere in the designer and click Variables option.<\/li>\n
    3. Create following 3 variables which we will use later on.
      \nFileList (Object Type)
      \nZipFileName (String Type)
      \nZipFileUrl (String Type) – Default Value must not be blank (Enter anything like\u00a0\u00a0http:\/\/a.com\u00a0<\/strong>).<\/li>\n
    4. Drag Data Flow Task from SSIS Toolbox to designer<\/li>\n
    5. Go to data flow designer by clicking on the data flow task.<\/li>\n
    6. Drag\u00a0and drop\u00a0ZS HTML Table Source<\/a>\u00a0and double click to edit it. Enter the HTML Web Page URL from where you like to read information. For example purpose, we will use below URL. On that page we have a table with files we need to download.\n
      http:\/\/www.patentsview.org\/download<\/pre>\n
      \"Configure<\/a>
      Configure – SSIS HTML Table Source URL \/ Other Options<\/p><\/div><\/li>\n
    7. Now let’s go to Extract Links and Images<\/strong> tab and check Output Links for each column. This will give us link for Zip File URL which we want to download.\n
    8. Click Preview to see data.\n
    9. Now click on OK to save the UI.<\/li>\n
    10. In next section we will configure Recordset Destination<\/li>\n<\/ol>\n
      Save Records to SSIS Variable<\/h3>\n
      Now let’s look at how to save HTML Table into in memory recordset which we will loop through later on. You can save data directly to relational destination such as SQL Server or Oracle but in our scenario we are extracting links and downloading them one by one.<\/p>\n
        \n
      1. In continuation of previous step, Once done with HTML Table Source configuration, Drag Recordset Destination from Data flow toolbox<\/li>\n
      2. Connect HTML Table Source to Recordset Destination<\/li>\n
      3. Select Variable to hold recordset (Must be Object Datatype – in our case we have User::FileList)<\/li>\n
      4. Select Input columns you like to use. In our case we will Pick two columns as below (Name and Link).\n
      5. Click OK to save Recordset destination.<\/li>\n
      6. In our next step we will look at how to loop through these records.<\/li>\n<\/ol>\n
        Loop through records in SSIS<\/h3>\n
        Once you done with previous step, switch to control flow tab and follow below steps.<\/p>\n
          \n
        1. Drag ForEach Loop Task from SSIS Toolbox.<\/li>\n
        2. Connect Data Flow Task to ForEach Loop Task<\/li>\n
        3. Double click ForEach Loop task to configure<\/li>\n
        4. On the Collection Pane, select Enumerator as Foreach ADO Enumerator as below.\n
        5. On the Variable Mappings tab you can map columns you care about. For example in the previous section we selected two input columns for recordset destination. We can map same columns here in same the same order.\n
        6. Click OK to save our settings.<\/li>\n<\/ol>\n
          Download File from URL in SSIS<\/h3>\n
          Now, after Loop configuration we can define what we want to do in each iteration. For example we can simply drag Logging Task<\/a>\u00a0and display Variables we extracted in ForEach Loop Variable Mappings tab. For downloading links we extracted in previous step you can use\u00a0REST API Task<\/a>\u00a0as below.<\/p>\n
            \n
          1. Drag ZS REST API Task<\/a>\u00a0from SSIS Toolbox and drop inside ForEach Loop.<\/li>\n
          2. You can now set Dynamic URL using placeholder\u00a0 {{User::ZipFileUrl}}<\/span><\/li>\n
          3. Now one more thing we need to do is define Target path where we will save downloaded file.<\/li>\n
          4. Go to, Response Settings Tab, Check Save Response option and Select Save to File.<\/li>\n
          5. Also, check Treat Response As Binary\n
          6. Click OK to save REST API Task.<\/li>\n<\/ol>\n
            That’s it. You can now run SSIS Package and see things in action.<\/p>\n
            Conclusion<\/h2>\n
            In this article, we saw how easy it is to extract unstructured from web in few clicks. Using components such as HTML Table SOurce and REST API Task we can read \/ parse HTML Pages. Download SSIS PowerPack<\/a> to explore many other options including 70+ tasks \/ components we have not discussed in this article.<\/p>\n
             <\/p>\n","protected":false},"excerpt":{"rendered":"
            Introduction In this post you will learn how to extract data from web pages using SSIS. In other words, read HTML Table in SSIS, then Loop through extracted links and finally download files. To achieve this scenario we will use\u00a0SSIS HTML Source\u00a0and REST API Task.     Step-By-Step To achieve desired extraction from web pages […]<\/p>\n","protected":false},"author":1,"featured_media":5656,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[419,322,420,10],"tags":[318],"yoast_head":"\r\nRead HTML Table in SSIS - Extract \/ Download Links \/ Images | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Extract unstructured data from web pages \/ URL in SSIS. Learn how to read HTML Table in SSIS, extract links \/ images and download file using REST API Task.\" \/>\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\/read-html-table-ssis-extract-download-links-images\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Read HTML Table in SSIS - Extract \/ Download Links \/ Images | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Extract unstructured data from web pages \/ URL in SSIS. Learn how to read HTML Table in SSIS, extract links \/ images and download file using REST API Task.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/\" \/>\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=\"2018-12-16T19:28:17+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-12-19T16:32:56+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"128\" \/>\r\n\t<meta property=\"og:image:height\" content=\"128\" \/>\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=\"4 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/\",\"url\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/\",\"name\":\"Read HTML Table in SSIS - Extract \/ Download Links \/ Images | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png\",\"datePublished\":\"2018-12-16T19:28:17+00:00\",\"dateModified\":\"2018-12-19T16:32:56+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Extract unstructured data from web pages \/ URL in SSIS. Learn how to read HTML Table in SSIS, extract links \/ images and download file using REST API Task.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png\",\"width\":128,\"height\":128},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Read HTML Table in SSIS – Extract \/ Download Links \/ Images\"}]},{\"@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":"Read HTML Table in SSIS - Extract \/ Download Links \/ Images | ZappySys Blog","description":"Extract unstructured data from web pages \/ URL in SSIS. Learn how to read HTML Table in SSIS, extract links \/ images and download file using REST API Task.","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\/read-html-table-ssis-extract-download-links-images\/","og_locale":"en_US","og_type":"article","og_title":"Read HTML Table in SSIS - Extract \/ Download Links \/ Images | ZappySys Blog","og_description":"Extract unstructured data from web pages \/ URL in SSIS. Learn how to read HTML Table in SSIS, extract links \/ images and download file using REST API Task.","og_url":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2018-12-16T19:28:17+00:00","article_modified_time":"2018-12-19T16:32:56+00:00","og_image":[{"width":128,"height":128,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.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":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/","url":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/","name":"Read HTML Table in SSIS - Extract \/ Download Links \/ Images | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png","datePublished":"2018-12-16T19:28:17+00:00","dateModified":"2018-12-19T16:32:56+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Extract unstructured data from web pages \/ URL in SSIS. Learn how to read HTML Table in SSIS, extract links \/ images and download file using REST API Task.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2018\/12\/ssis-html-table-source.png","width":128,"height":128},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/read-html-table-ssis-extract-download-links-images\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Read HTML Table in SSIS – Extract \/ Download Links \/ Images"}]},{"@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\/5601"}],"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=5601"}],"version-history":[{"count":10,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/5601\/revisions"}],"predecessor-version":[{"id":9652,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/5601\/revisions\/9652"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/5656"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=5601"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=5601"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=5601"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}