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

Scenario:

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.

Solution:

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

5 comments:

  1. how to download excel files from sharepoint if the sharepoint URL has https:// instead of https://?

    ReplyDelete
  2. how to download excel files from sharepoint if the sharepoint URL has https:// instead of http://?

    ReplyDelete
  3. We are all often tend to use the internet for many things like connecting with our friends, searching for related unknown information, browsing through various websites and many things and If we are not sure of the internet speed we are connected to, BSNL speed test we can check it online with speed test and check the internet speed as per BSNL broadband plan or mobile data we are connected

    ReplyDelete
  4. Himachal Pradesh Elementary Education Board Responsibilities of Preparing the Textbook, Syllabus, Study Material and Conducting Middle, SCERT Himachal Pradesh 3rd Class Textbook 2023 Final Examination, HP Board 1st, 2nd, 3rd, 4th, 5th Textbook 2023 are one of the most Resourceful Tools for the Students. Primary School Textbooks Provide the Right and in-Depth information on a Particular topic. Also, these books act as a Framework for Facilitating Students with Effective learning.

    ReplyDelete
  5. You can go to SharePoint, select File, then Right Click, Select Properties. Address (URL), which is the Server URL you must use in Connection above Accounting Services , will appear.The connection should work when tested.

    ReplyDelete