Tutorial: How to Download SSIS Source Files Using WebClient

SQL Server Integration Services (SSIS) is a platform of the Microsoft SQL Server database software for building enterprise-level data integration and data transformation solutions. It includes a data warehousing tool used for data extraction, transformation, and loading (ETL). SSIS helps us to merge data from various data stores and it is used to solve complex business problems by copying or downloading files, loading data warehouses, cleaning and mining data, and managing SQL Server objects and data.

In this post, we will learn how to download the SSIS source files from a Web repository. We can identify 3 solutions for this use case:

• Third-party tools such as CozyRoc or BlueSSIS.
• Build a Script Task with WebClient
• Build a Script Task with HTTP Connection Manager

In this tutorial, we will only discuss the second and the third solutions.

Create a Script Task with WebClient to Download SSIS Source Files

I have a Data Flow task that reads a CSV file (Clients.csv), does some adjustments and inserts the records into an SQL database table. The CSV file is stored on a web application and it should be downloaded to a physical directory every time we run the package.

Here is an example of how to downloas the SSIS source files from SharePoint. To create a Script Task with WebClient to download CSV source files from SharePoint in SSIS, you can use the following steps:

• Open Microsoft SQL Server Data Tools (SSDT) and create a new SSIS project.

• In the Control Flow tab, add a Script Task to your project.

• Double-click the Script Task to open the Script Task Editor.

• In the Script Task Editor, select the "Main" method as the entry point for the script.

• In the ReadOnlyVariables property, add a new variable that will store the URL of the SharePoint site where the CSV source file is located.

• In the "using" statements, add the following references:

using System.Net;
using System.IO;

• In the Main method, add the following code to download the file using the WebClient class:

string fileUrl = Dts.Variables["User::FileUrl"].Value.ToString();
string localPath = @"C:\Temp\SourceFile.csv";

using (WebClient client = new WebClient())
{
    client.DownloadFile(fileUrl, localPath);
}

• Save and close the Script Task Editor.
• Connect the Script Task to the rest of your SSIS workflow, and run the project.

Note: Replace the file URL, local path, and other variables with your own values. Also, ensure that you have the necessary permissions to access the SharePoint site and download the file.

Create a Script Task with HTTP Connection Manager to Download SSIS Source Files

In this section, we assume that the source files are stored in SharePoint. To create a Script Task with an HTTP Connection Manager to download CSV source files from SharePoint in SSIS, you can use the following steps:

• Open Microsoft SQL Server Data Tools (SSDT) and create a new SSIS project.

• In the Control Flow tab, add a Script Task to your project.

• Double-click the Script Task to open the Script Task Editor.

• In the Script Task Editor, select the "Connection Managers" tab and add a new HTTP Connection Manager. This will allow you to connect to the SharePoint site and download the desired source files.

• Next, switch to the "Script" tab and set the "ReadOnlyVariables" property to the variables that you would like to use in the script. This will allow you to access these variables from within the script.

• In the script, you can use the System.Net.WebClient class to download the source files. For example:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Net;

namespace ST_70fb1d1772304c9ca3b57a67a7e17e59
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            using (var client = new WebClient())
            {
                client.Credentials = CredentialCache.DefaultCredentials;
                client.DownloadFile("https://<sharepoint-site-url>/<folder>/<file>.csv", "C:\\<file>.csv");
            }

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

• Replace <sharepoint-site-url>, <folder>, and <file>.csv with the actual URL, folder name, and file name of the source file on SharePoint.

• Save the script and close the Script Task Editor.

• Run the project to test if the script task can successfully download the CSV source files from SharePoint.

Note: This example assumes that you have the necessary permissions to access the SharePoint site and download the source files. You may need to modify the script to include authentication information, such as a username and password, if the SharePoint site requires it.

Conclusion

In conclusion, both the Script Task with WebClient and the Script Task with HTTP Connection Manager provide options for downloading SSIS csv source files from a web application, such as SharePoint. The choice of method will depend on the specific requirements and constraints of the project, such as security and efficiency.