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.
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
ReplyDeleteAdditional information: The process cannot access the file because it is being used by another process. (Exception from HRESULT: 0x80070020)"
What am I doing wrong?
It worked for me , but It copied the XML code into CSV file.. I have multiple files in the http URL folder...
ReplyDeletePlease help..
we have multiple files in the document library and we need to download only specific files containing some specific characters in filename. Please help!!
ReplyDeleteHi, I have the same requirement as yours. Please let me know how did you solve this issue?
DeleteAwesome post I might want to thank you for the endeavors you have made in composing this intriguing and educated article. How To Download
ReplyDeleteExecuted the same code but runtime error occured.
ReplyDeleteerror:
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()
same error as ^
ReplyDeleteI 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
ReplyDeleteI 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
ReplyDeleteI have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.
ReplyDeleteTangki Panel
Tangki Fiberglass
Jual Septic Tank
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
ReplyDeleteI 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
ReplyDeleteI definitely enjoying every little bit of it. It is a great website and nice share. I want to thank you. Good job! You guys do a great blog, and have some great contents. Keep up the good work. cfa level 1 mock exam 2019 pdf
ReplyDeleteI get "The remote server returned an error: (403) Forbidden" even though the account i provide has admin rights. Any thoughts on this?
ReplyDeletesame here :S
Deletesame here :S
DeleteOn the off chance that indeed, at that point a file transfer administration is the answer for your concern. transfer large files
ReplyDelete