How to make an HTTP request from SSIS?

You can make use of the namespace System.Net.WebClient to make the Http request with the help of Script Task in SSIS. Following example shows how this can be achieved. The example was created in SSIS 2008 R2.

Step-by-step process:

  1. Create a new SSIS package and create two variables namely RemoteUri and LocalFolder. Set the variable RemoteUri with the value http://www.google.com/intl/en_com/images/srpr/logo1w.png. this is the image url of the logo on the Google's home page. Set the variable LocalFolder with the value C:\temp\. this is the path where we are going to save the content. Refer screenshot #1.

  2. On the SSIS package, place a Script Task. Replace the Main() method within the script task with the code provided under the Script Task Code section. Refer screenshot #2.

  3. Screenshot #3 shows that the path C:\temp\ is empty.

  4. Screenshot #4 shows successful execution of the package.

  5. Screenshot #5 shows that the content (in this case the logo image) has been downloaded to the local folder path.

  6. Screenshot #6 shows that the code was tested to download a .zip file. To achieve this, the value of the variable RemoteUri was changed with the content url that needs to be downloaded.

Script task code:

C# code that can be used only in SSIS 2008 and above.

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::RemoteUri");
    Dts.VariableDispenser.LockForRead("User::LocalFolder");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    System.Net.WebClient myWebClient = new System.Net.WebClient();
    string webResource = varCollection["User::RemoteUri"].Value.ToString();
    string fileName = varCollection["User::LocalFolder"].Value.ToString() + webResource.Substring(webResource.LastIndexOf('/') + 1);
    myWebClient.DownloadFile(webResource, fileName);

    Dts.TaskResult = (int)ScriptResults.Success;
}

Screenshot #1:

1

Screenshot #2:

2

Screenshot #3:

3

Screenshot #4:

4

Screenshot #5:

5

Screenshot #6:

6


Here are a couple of options:

  1. Third party tools such as CozyRoc or BlueSSIS.
  2. Script Task with WebClient
  3. Script Task with HTTP Connection Manager

Script Task Examples at: http://microsoft-ssis.blogspot.com/2011/05/download-source-file-from-website-with.html


Just an alternative for @user756519 script, not as fast, but more bulletproof

public void Main()
{
    Variables varCollection = null;

    Dts.VariableDispenser.LockForRead("User::RemoteUri");
    Dts.VariableDispenser.LockForRead("User::LocalFolder");
    Dts.VariableDispenser.GetVariables(ref varCollection);

    System.Net.WebClient myWebClient = new System.Net.WebClient();
    string webResource = varCollection["User::RemoteUri"].Value.ToString();
    string fileName = varCollection["User::LocalFolder"].Value.ToString() + webResource.Substring(webResource.LastIndexOf('/') + 1);

    byte[] data;
    using (WebClient client = new WebClient())
    {
        data = client.DownloadData(webResource);
    }
    FileInfo file = new System.IO.FileInfo(fileName);
    file.Directory.Create(); // If the directory already exists, this method does nothing.
    File.WriteAllBytes(file.FullName, data);

    Dts.TaskResult = (int)ScriptResults.Success;
}

This way, webClient doesn't stay hanging, and also you're not dependent on the previous existence of C:\Temp directory. Other than that, great answer from @user756519, very detailed.

Tags:

Http

Ssis

Recent Posts