Download Files from Web using SSIS

Often we are required to load data from files (text/CSV/excel etc.). Sometimes these files are available as a download in a web server. In such cases the using a script task in SSIS comes in handy to download the files. Here is the code snippet to download the files from web in a Script Task in SSIS

Firstly, we need to create a couple of variables,

1.     Download URL path

2.     File Save Path

Once these are created, one can easily use configurations to make these variables configurable at run time. Now coming to code part where we are downloading the files,

Firstly we need to add assembly reference to System.Net namespace

using System.Net;

Write a function as below

private bool DownloadFile(string strFileName)
{

bool retVal = false;

bool fireAgain = true;

WebClient wc = new WebClient();

string strServerFilePath = "download_url from SSIS variable goes here"+strFileName;

string strLocalFilePath = "File Save Path from SSIS variable goes here"+strFileName;

wc.Credentials = new NetworkCredential("username", "password");

try

{

wc.DownloadFile(strServerFilePath, strLocalFilePath);

retVal = true;

}

catch (WebException ex)

{

fireAgain = false;

Dts.Events.FireInformation(0, "Downlad File", "Download failed: " + ex.Message, string.Empty, 0, ref fireAgain);

retVal = false;

}

return retVal;

}

Call this function from the Main function in the Script Task and verify if the function returns a true/false, and handle the load accordingly.