How to download file from SharePoint by using SSIS Package - SSIS Tutorial / SSIS Interview questions

Scenario:

You are working as SSIS Developer and you need to download the file from SharePoint on daily basis, if the file exists in the folder you want to overwrite with new file. The file name that you need to download from the SharePoint stays the same.

Solution:

We will be using the Script Task and HTTP connection Manager in SSIS Package to download the file and write to folder in SSIS Package.

Step 1: 

Create a new SSIS Package by using BIDS or SSDT ( SQL Server Data Tools).

Step 2:

Create a variable called FilePath where you would like to save the file on local folder.

How to download File from SharePoint and Save to Local Folder in SSIS Package by using Script Task

Step 3:
Create the HTTP Connection to SharePoint File. Go to Connection Managers Pane and then right click and then choose New Connection.

How to create HTTP Connection Manager to SharePoint to download File in SSIS Package

How to download file from SharePoint by using SSIS Package by using Script Task with HTTP Connection

How to create Connection Manager to SharePoint in SSIS Package

To get the Server URL ( Path to file on SharePoint). You can go to SharePoint and then go to File and Right Click, Choose Properties and then you will see Address (URL) that is the Server URL you need to use in Connection above. Also provide the user name and password which has permission on the SharePoint File. Test the connection and it should be successful.

A new connection should be created once you click OK. You can rename if you like,I am going to leave this as it is.

Step 4: 
Bring the Script task in Control Flow Pane and open, you need to add the variable.

How to download file from SharePoint to Local Folder by using Script Task in SSIS Package

Click on Edit Script Button and then paste below script under public void Main() {

   string FilePath = Dts.Variables["User::FilePath"].Value.ToString();
   object obj = Dts.Connections["HTTP Connection Manager"].AcquireConnection(null);
   HttpClientConnection HTTPConnection = new HttpClientConnection(obj);
   HTTPConnection.DownloadFile(FilePath, true);  

Save the script and close the Editor windows. Run your SSIS Package, it should download the file. Every time you will execute the SSIS Package, it will overwrite the existing file from SharePoint file.