How to download File from SharePoint and Add Datetime to it in SSIS Package by using Script Task - SSIS Tutorial / SSIS Interview Questions


You are working as ETL/ SSIS Developer. You need to write an SSIS Package that should download the file from SharePoint site. Every time you run the SSIS Package it should download the file and add date_time to it.


We will be using HTTP Connection and Script Task to download the file from SharePoint.

Step1 :
Create your SSIS Package by using BIDS or SSDT ( SQL Server Data Tools).

Step 2: 
Create Three variables of string type
FolderPath : 
Provide the folder in which you want to download the file.
FileName : 
Provide the destination file name with extension such as myfile.xlsx or mytestfile.doc
FileFullPath : 
This is the variable we will be using to write expressions on it. Click on the expressions tab and then write the below expressions.
How to download file from SharePoint and Add Datetime to it in SSIS Package

Once you click on the Expression Button as shown above, paste the below expressions.

@[User::FolderPath]+ Replace( @[User::FileName],".", "_"+Replace(Replace(Replace(SUBSTRING((DT_WSTR,50)(GETDATE()),1,19),"-","")," ","_"),":","")+".")

Write expressions on variable in SSIS Package to add Date_Time to it 

Step 2:
To 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 use Script Task to download File from SharePoint in SSIS Package

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

   string FilePath = Dts.Variables["User::FileFullPath"].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 and add date_time to file name. Every time you will run the SSIS Package , the new file will be downloaded and date_time will be added to it.

I executed the SSIS Package couple of times and you can see that the file is downloaded and date_time is added to it.

How to download files from SharePoint and add Date_Time on each execution in SSIS Package