How to load a file from a URL to SQL table using SSIS?

SSIS PowerPack
, ,
1

Introduction

This article provides a concise guide on how to download a file from a URL and load it into a SQL table using SSIS, covering 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 an SQL table 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 loading the file data into a SQL table with a VARBINARY datatype column using an Upsert Destination.
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 load a file from a URL to an SQL Table 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 load the file data and the file URL into the SQL Table using Upsert Destination:
    Sample SQL Table:

    CREATE TABLE "tblFiles" (
    "FileUrl" nvarchar(248),
    "FileData" varbinary(max),
 )

    SQL Table

  9. Next, drag and drop the Upsert Destination. Begin by configuring a New ADO.NET SQL Connection for your SQL database.

    Upsert Destination
    Upsert Destination833×699 33 KB

  10. Select or create a new SQL table, map the source columns, choose the key column (in this case, FileURL), and click “OK”.

    Upsert Destintion
    Upsert Destintion833×659 41.4 KB

  11. That’s it! Run the SSIS package, and it will read the URL from the CSV, download the file using the Web API destination, and load the data into the SQL table using the Upsert destination.
    SSIS Package

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.