How to download a file from a URL in Dataflow using SSIS?

SSIS PowerPack
, ,
1

Introduction

This article provides a concise guide on how to download a file from a URL and save it into a Physical folder location using SSIS. It covers the setup, configuration, and execution of the necessary steps to streamline your data workflows.

Solution

In this article, we demonstrate how to automate the process of loading files from URLs stored in CSV files into a Physical folder location using SQL Server Integration Services (SSIS). We will guide you through reading the URLs from the CSV file using CSV Source, read/downloading the files using a Web API Destination, and finally saving the file to a folder with a FUN_WRITE_FILE_BINARY Placeholder function using a Template Transform.
CSV File

If you have file URLs in any other source, you can read the file URL from there using the desired source component.

We also have an article that demonstrates how to extract image URLs from a web page and download the images. You can read it How to download images from a web page using SSIS.

Step-by-Step Guide

Let’s jump right in, step by step, as we learn how to download a file from a URL using SSIS.

  1. Firstly, You need to Download and Install SSIS ZappySys PowerPack.

  2. Once you have finished the first step, Open Visual Studio and Create a New SSIS Package Project.

  3. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. And double click on the DataFlow task to see DataFlow designer surface.
    SSIS Data Flow Task - Drag and Drop

  4. From the SSIS toolbox, drag and drop CSV Source on the dataflow designer surface.
    SSIS CSV Source - Drag and Drop

  5. Double click CSV Source and configure it like this:

    • Set the CSV File Path: In this case, use FileURLs.csv.
    • Select the Desired Column Separator Choose the appropriate column separator.
    • Check “First Row Has Column Headers”: Ensure this option is selected.
    • Preview the CSV File Data: Click on “Preview” to view the data.
    • Close the CSV Source: Click “OK” to close the CSV Source.
      CSV Source
      CSV Source992×735 62.8 KB

  6. Below the CSV Source, drag and drop the Web API Destination. Configure a new HTTP Connection by setting the dummy Web URL to https:// then click OK.

    Web API Destination
    Web API Destination1005×795 61.1 KB

  7. Set the CSV Source FileURL column as the input column for the URL. Choose GET as the HTTP Request Method, checkmark “Treat Response as Binary Data”, and then click “OK”.

    Web API Destination
    Web API Destination721×773 50.1 KB

  8. Now let’s save the file into the local folder using Template Transform:
    Sample:

image
image836×673 45.1 KB

Related Articles

Conclusion

This comprehensive guide should assist you in reading/downloading a file from a URL and loading it into an SQL table using ZappySys components in your SSIS packages. For further assistance, please contact our support team via chat on our website.