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.

72 comments:

  1. It didn't work for me. I get this error: "An exception of type 'System.IO.FileLoadException' occurred in Microsoft.SqlServer.ManagedDTS.dll but was not handled in user code

    Additional information: The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020)"

    What am I doing wrong?

    ReplyDelete
  2. It worked for me , but It copied the XML code into CSV file.. I have multiple files in the http URL folder...

    Please help..

    ReplyDelete
  3. we have multiple files in the document library and we need to download only specific files containing some specific characters in filename. Please help!!

    ReplyDelete
    Replies
    1. Hi, I have the same requirement as yours. Please let me know how did you solve this issue?

      Delete
  4. Awesome post I might want to thank you for the endeavors you have made in composing this intriguing and educated article. How To Download

    ReplyDelete
  5. Executed the same code but runtime error occured.
    error:
    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
    Replies
    1. Did anyone identify a solution this error? I get the same thing. Switched to using an Excel Connection Manager and I get a driver error. (The requested OLE DB Microsoft.ACE.OLEDB.12.0 is not registered....) One developer here has the Excel Connection Mgr working but two of us get the driver error. We spent hours trying to resolve that issue but were unsuccessful. This and "agile" makes me long for retirement.

      Delete
    2. Same here, it's now the problem of permission that resource.

      Delete
  6. I am thankful to you for sharing this plethora of useful information. I found this resource utmost beneficial for me. Thanks a lot for hard work. download vk videos

    ReplyDelete
  7. I have read all the comments and suggestions posted by the visitors for this article are very fine,We will wait for your next article so only.Thanks! Reddit videos downloader

    ReplyDelete
  8. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. free downloading microsoft office 2007

    ReplyDelete
  9. I get familiar with a great deal about an individual from a look at their email inbox or record the executives framework. I as of late worked with a customer whose email inbox was, in all honesty, a wreck.7zip

    ReplyDelete
  10. I get "The remote server returned an error: (403) Forbidden" even though the account i provide has admin rights. Any thoughts on this?

    ReplyDelete
  11. On the off chance that indeed, at that point a file transfer administration is the answer for your concern. transfer large files

    ReplyDelete
  12. I'm getting a 403 access error in the connector. I know my creds are good since I can download from the browser.

    ReplyDelete
    Replies
    1. Could it by multi Multi-factor authentication

      Delete
  13. This comment has been removed by the author.

    ReplyDelete
  14. I visit your blog regularly and recommend it to all of those who wanted to enhance their knowledge with ease. The style of writing is excellent and also the content is top-notch. Thanks for that shrewdness you provide the readers! convert pdf to png

    ReplyDelete
  15. I am getting below error. Please suggest how to fix
    [Connection manager "HTTP Connection Manager"] Error: SSL certificate response obtained from the server was not valid. Cannot process the request.

    ReplyDelete
  16. I am impressed. I don't think Ive met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here. file upload

    ReplyDelete
  17. Advances in interchanges and innovation have carried with it an off-shoot marvel that has significantly affected the manner in which people and organizations interface and manage one another.simple file sharing

    ReplyDelete
  18. Nice to be visiting your blog once more, it has been months for me. Well this article that ive been waited for therefore long. i want this article to finish my assignment within the faculty, and it has same topic together with your article. Thanks, nice share. psychiatric service dog letter

    ReplyDelete
  19. Keep up the good work , I read few posts on this web site and I conceive that your blog is very interesting and has sets of fantastic information. cheapest web hosting indiaonohosting

    ReplyDelete
  20. Good post but I was wondering if you could write a litte more on this subject? I’d be very thankful if you could elaborate a little bit further. Appreciate it! 바카라사이트

    ReplyDelete
  21. Hi there! Nice post! Please tell us when I will see a follow up! Glary Utilities Pro Keygen

    ReplyDelete
  22. Thankyou for this wondrous post, I am glad I observed this website on yahoo. 먹튀검증

    ReplyDelete
  23. Wow, excellent post. I'd like to draft like this too - taking time and real hard work to make a great article. This post has encouraged me to write some posts that I am going to write soon. BizOp

    ReplyDelete
  24. Thankyou for this wondrous post, I am glad I observed this website on yahoo. 토토사이트

    ReplyDelete
  25. You made such an interesting piece to read, giving every subject enlightenment for us to gain knowledge. Thanks for sharing the such information with us to read this... 릴게임

    ReplyDelete
  26. Efficiently written information. It will be profitable to anybody who utilizes it, counting me. Keep up the good work. For certain I will review out more posts day in and day out. 온라인릴게임

    ReplyDelete
  27. nice post, keep up with this interesting work. It really is good to know that this topic is being covered also on this web site so cheers for taking time to discuss this! 먹튀검증커뮤니티

    ReplyDelete
  28. Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work. 88카

    ReplyDelete
  29. Varicose veins that develop during pregnancy may improve gradually over several months after giving birth.메이저사이트

    ReplyDelete
  30. Thanks For sharing this Superb article.I use this Article to show my assignment in college.it is useful For me Great Work. KissAnime

    ReplyDelete
  31. 123betting Very helpful information I got very good information Thanks for sharing good. 123betting

    ReplyDelete
  32. Great survey, I'm sure you're getting a great response. تفسير الاحلام

    ReplyDelete
  33. I love visiting sites in my free time. I have visited many sites but did not find any site more efficient than yours. Thanks for the nudge! textbook answers

    ReplyDelete
  34. I like valuable information you supply in your articles. I'll bookmark your blog. 카지노사이트

    ReplyDelete
  35. having a blog like yours would cost a pretty penny?검증사이트추천
    I’m not very internet smart so I’m not 100% sure. Any tips or advice would be greatly appreciated.

    ReplyDelete
  36. Very nice article. I enjoyed reading your post. and much obliged for the data. very nice share. I want to twit this to my followers. Thanks 먹튀검증추천

    ReplyDelete
  37. I'll come often. Great work! my own blog and would like to find out where u got this from. 토토보증업체

    ReplyDelete
  38. I look forward to your kind cooperation. 메이저놀이터 We are linking to this great post on our website

    ReplyDelete
  39. Guess I will just bookmark this site I'm surprised there's such a wonderful article 토토검증사이트

    ReplyDelete
  40. Some extremely valid points! I appreciate you writing this write-up plus the rest of the site is extremely good. 789 สล็อต

    ReplyDelete
  41. your content is very inspiring and appriciating I really like it please visit my site for Satta King Result also check Satta king 24X7 and also check sattaking and for quick result check my site Satta matka and for super fast result check Satta king

    ReplyDelete
  42. Nice to meet you. Your website is full of really interesting topics. It helps me a lot. I have a similar site. We would appreciate it if you visit once and leave your opinion. 안전놀이터추천


    ReplyDelete
  43. From some point on, I am preparing to build my site while browsing various sites. It is now somewhat completed. If you are interested, please come to play with 토토사이트!!토토사이트



    ReplyDelete
  44. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Great work 알파벳 토토

    ReplyDelete
  45. It's really great. Thank you for providing a quality article. There is something you might be interested in. Do you know 룰렛사이트? If you have more questions, please come to my site and check it out!


    ReplyDelete
  46. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. File transfer

    ReplyDelete
  47. I saw your article well. You seem to enjoy 카지노사이트 for some reason. We can help you enjoy more fun. Welcome anytime :-)


    ReplyDelete
  48. Your post has really helped me a lot. I live in a different country than you, but I believe it will help a lot in my country. 카지노사이트 Thank you very much. Can I refer to your post on my website? Your post touched me a lot and helped me a lot. If you have any questions, please visit my site and read what kind of posts I am posting. I am sure it will be interesting.


    ReplyDelete
  49. Thanks, I have just been looking for information approximately this topic for a long time and yours is the greatest I’ve came upon till now. สมัครสมาชิก 789betting

    ReplyDelete
  50. I wish for to subscribe for this blog to get newest updates, so where can i do it please help out. ฝากจอดรถยนต์อุบล

    ReplyDelete
  51. Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post. how to send large files

    ReplyDelete
  52. Incredibly best man toasts, nicely toasts. is directed building your own by way of the wedding celebration as a result are supposed to try to be witty, amusing and consequently unusual as well as. แทงบอลชุด

    ReplyDelete
  53. i am getting -->The remote server returned an error: (401) Unauthorized

    ReplyDelete
  54. I really found this to much informatics. It is what i was searching for. ทางเข้า 789bet

    ReplyDelete
  55. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well. Thanks... rv share

    ReplyDelete
  56. Himachal Pradesh 6th, 7th, 8th, 9th, 10th Book 2023 is Available here for Free Download, We are Providing the Chapter-wise Links which can be Downloaded as Pdf which Students may Refer whenever Required. HPBOSE 7th Class Textbook This is the Latest Edition of has been Published by the Himachal Pradesh Board of School Education is Agency Government of Himachal Pradesh entrusted with the Responsibilities of Prescribing Courses of instructions and Textbook for Secondary School Students in Himachal Pradesh. Students may also check here the HP Board 6th, 7th, 8th, 9th, 10th Books 2023 Prepared by Subject Experts. This Study Materiel For a better understanding of concepts used in 6th, 7th, 8th, 9th, 10th, Download the Book From the Link Provided at the end of this article.

    ReplyDelete
  57. Any recommendations on how to do the opposite operation; download a file to SharePoint online from SSIS

    ReplyDelete
  58. I am impressed. This is the post I was looking for I am very happy to finally read about the Thank you very much. Your post was of great help to me. If you are interested please visit my site. I simply couldn't leave your site just my reading this blog. I am really impressed with the content and way of writing. Keep sharing more.
    amend preliminary protective order virginia
    domestic violence protective orders in virginia

    ReplyDelete
  59. If you're ready to embark on a gaming adventure that knows no bounds, sign up today and become a part of our winning legacy. writer for us casino

    ReplyDelete