148 How to download files from sftp server using batch file / command line / ssis / WinScp

hi friends my name is Akil Ahmed and in this particular video tutorial I will show you how to download the files from the SFTP server using the badge file or the command prompt using ssis so the agenda of today's video tutorial is that we will see like how can we download the files from the SFTP server using the command line so uh if you're working on an ssis project then downloading or uploading the files to the SFTP server is very common I have also created several videos where I shown you how you can upload or download the files to the SFTP server using the cop language but the issue with that particular method was that we were using the C language and we were using the script task in ssis and in the cop language we were using ry. SSS net. DL so if you were using a dll then we had to register the D to the gek if you are going to use it in the ssis package so if you are creating the ssis package on the development server and testing the package there then you had to register the DL into the gag on the development server and once you will deploy the package to the production server or if you are scheduling the package to be run from the production server then you have to register the DL on the production server as well so that was one of the drawback that can be avoided so in this method we won't be using any DL so that's why we don't need to register any D to the gag instead we will be using the win SCP client along with few commands and that should be quite simple so without wasting further times let's jump to the demo so first of all let's open the win SCP client so I can open Win SCP uh This Is An Open Source software so you can install it on on your server Okay so so now um here it's asking to login so from the protocol the SFTP is already selected okay so we won't change it now inside the host name you need to type the your SFTP server address okay but right now in my case the SFTP server is installed on my machine itself so that's why I can type the IP address of my machine so I can open the command from here and then I can type ip config okay and then I can actually copy the IP address from here okay and then paste the address here the port number will be 22 you know whenever you install the SFTP servers the default TCP Port is the 22 so that's fine now you need to provide the credentials for the SFP server so in my case the username is the HP and the password is I just provided the password so when you will be working with the SFTP server so they will provide you the SFTP server address the username the password and the location like from which location you want to download the files okay so I provided the required things here and now I can click on login so this should be able to log to the SFTP server so on the right side these folders they are from the SFTP server okay so if I go to the C drive for example so there are few folders here like data to load so in the data to load C uh column SL dat to load uh we got several CSU files so these files you will try to download from the SFTP server using the command line and with the help of the ssis package so I want to download these files from the SFTP server to this particular location D files location here I want to download all the CSV files okay so let me open the visual studio so this is my visual studio here and we will write the code to download the files so we actually need to create a badge file which can actually use to download the files from the SFTP server so we can open the win SCP client and now we need to click on the tabs option and from the tabs menu we can click on this one generate session URL code okay make sure that you select the script option and you select the script file so this line this can be used to actually connect to the SFTP server so here uh we are writing like open SFTP and this is the username HP is the username and test is my password actually okay and then this is the IP address of the SFTP server or whatever SFTP server you are connected with this will be the address of of the SFTP server and the host key is this one sssh hyph this one this is the host key okay so using this particular line you can actually connect to the SFTP server so what you can do you can actually click on the copy to clipboard okay and then you can close this one now suppose I want to download the files to this location so that's fine you can download the files to any location but you need to create a batch file which can be used to download the files so I have a folder here script folder now what I will do inside the script folder I will create a badge file and I will call it as download files okay and the extension will be B8 badge okay and then I can open this particular badge file and inside the badge file I will paste this particular script okay whatever is the script now what I need to do is actually uh I can uh delete this one or just one second maybe I can keep this one for a second uh I can delete whatever the code is here and then this line will be there in the badge file like open this one open SFTP server and whatever is the code now in the new line U we will write Echo and connected okay we will actually this will be used to just uh log the information that we are connected to the SFTP server so in case if there is some issue with the Conn or there is some issue with the you know host key or the username or the password so we will actually write the information to the log file so we can anytime we can open the log file if the process fails and from there we can read like at which Step the U process was filled so at least we know that if the Eco connected is there in the log file so we know that the connection was at least successful and maybe it got failed while downloading the file okay okay so we are just writing it for the loging purpose Eco connected and now here we can write the code to download the files okay so I actually want to download the files uh from this location C colon hyphen so I just want to copy this particular uh you know location so how I can copy it I can right click and then I can click copy path to clipboard okay and then I can reopen the badge file and here I can write get and then in the double code I need to provide the path here so I want to download the files from this particular location so here I can just remove the first forward slash and then after the data to download I can write another forward slash and then I want to download all the CSV files so I can write star. CSV okay and then I can close the double code so here I'm using the double code because there is a space in the folder if there is no space in the folder then you don't need to use the double code but it's better that you use the double code so that if there are spaces in the path so even then you can just download the files and after putting an space you can actually give the location like to which location you want to download the files to so I want to download the files to my D files location okay so in this location I want to download the f so I can copy this location D files and then inside the double code I can paste this particular location okay so this is fine so our code is done and I can just simply write Echo uh download completed okay so that's it uh this is the only code just to download the files so here we will just open the SFTP server connection and then we will download the files using a single line of code like get the path from from where you want to download the file the type of file that we want to download like I just want to download is star. CSC so it will download all the CSC files and it will download the files to this location D files okay and this particular command I can actually remove from here okay this is not required in this particular badge file so I can just simply save the badge file here okay so now in the script there is a badge file that will be used and now I can open a text editor and I can paste the script that I copied okay so if you look at this particular command closely they are telling like execute the script using this command like you just need to provide the location of the win SCP file and then you just need to provide the location of the log file and then you need to provide the script path like where the script is situated so our script is situated in this location download files B okay and same way maybe we can create the blog file here itself so we can make use of this particular script okay so now the next option is that uh we can use the execute process task in ssis to run a script okay so we can open the ssis package and then we can just drag and drop the execute process task into the control flow window and now we can configure the execute process task to execute the badge file that we just created so I can just configure the execute process task here and now I can go to the process and inside the executable you just need to provide the location of the VIN SCP file like our Vin SCP file is situated I think at this location so maybe I can open this one and make sure that this is the correct location so maybe I can just type the location here and see if this is the valid location yeah so this is the correct location actually this can open the V CP so what I can do I can can actually uh browse this location uh paste the location here open so this is the file with sc.exe I can select this particular file okay so this is good now the second thing that we need to provide to this particular task is that we need to provide the value to the arguments okay and if you look at this particular command actually that we copied uh from the I think from this one you know uh wi SCP tabs and then generate session so this was the command they already given the whole command to you like how you can call it okay so here we have already provided this particular thing now the only thing that is remaining is that we need to provide the log log path and the script path so I can actually copy the this particular script from here okay and I can paste it here okay so that's fine and uh this one I I equal to null I can actually remove this one this is not required and if you want you can provide the script in the beginning or if you want you can provide in the LA so that's fine so I will provide in the beginning so it will make more sense that the script is this one so here I need to provide the location of the script the badge file so what I can do I can actually uh copy the location from here of the badge file and I can paste it here that this is the location of the batch file and now I need to provide the location of the log file so my log file will be created here like I can call it as log.txt that's fine okay so now the log file will be created at this location okay and in the badge file we have already uh written like where to download the file so if you open the badge files so the files will be downloaded at this location D files location and from this particular location of the SFTP server so right now we don't have any files in the D files location so I think we should be good to run the ssis package so we have configured the execute process task here so now I think we are good to execute the ssis package so I can click on the start button and it should kick off the ssis package so it seems like the process got failed here and if I go to the script and insert inside the script I can open the log.txt so I can open this one and I can see maybe I can go go to the bottom side so it's trying to download the file C data to load company. CSV are you sure you want to transfer multiple files to a single file in a directory D files so I think we need to um provide this single backs slash here that I think that might be the issue so I can close this one and I can open the badge file and here I can provide the D files and then single backs slash okay I think this might be the issue so I can just close this one save and then you know what happens actually whenever the log file is created so it just start appending the data to the log file so maybe uh before the execute process task we can have the file system task just to delete any PR previous log file okay so that you know it should not append the data to the same log file because then it can be seems like some kind of messy okay so I think I can just delete the existing log file and just rerun the ssis package and let's see what happens so this time the process ran fine and uh it should have downloaded the files from the SFTP server so I can go to the D files location and yeah all the files all the CSC files have been downloaded from the SFTP server so this is great so I can go to the script and I can check the data in the log file like what kind of information is in the log file so you can see like uh uh this is the command C program files okay and this is running the script okay and now inside the script it's opening the you know SFTP server this is the location of the SFTP server this is the host key you know so this is writing every small detail into the this particular log file like connecting to this particular server SFTP server on P22 and here it is saying like connected to the SFTP server so all the details are there like authenticated using SFP protocol and if you scroll down so here this is the one Eco connected okay like what we written in the script in the badge file now it's trying to download the data from this location see data to download to the D files location it's trying to download all the star. CSV file and then it will I think it will also mention the CSV file that it is downloading like company. CSV and then emails. CSV so it is giving you very minor details as well you know all the details give giving you in the log file so they are good to know like if the process was successful or it was failed so the log file is you know good enough to check all the details but before running the execute process task it's good like if you delete the existing log file so that it won you know seems kind of messy so that's fine you can add the code here you know to just delete the existing log file okay uh like I can create a variable log file PA I think it's pretty straightforward uh if you do not want to delete it then it's totally fine it's up to you but I don't want to like uh append data to the log file so I can write log.txt and I can copy the path from here and just give the location here D files script log.txt in a minute I can just write this code like deleting the log file if the log file already there so I can call it as delete the log file okay and there is one additional thing that I will tell you in a bit so just stay tuned be with me for a minute so is Source path variable true so from the operation we just need to select the delete file operation is Source path variable like is the path of the file that you want to delete is it stored in a variable so we can say yeah the path of the file is stored in a variable so you can select the variable here log file file path so this is the log file path that we want to delete so just two setting you need to do here and then it can delete the file okay delete the log file and then it can just download the file from the SFTP server so this is great so if you just want to download the file then you are good here now suppose after downloading the file if you want to delete the file as well from the SFTP server then what you need to do that's the very simple thing actually uh if you also want to delete the file after downloading the file successfully so you simply need to write here delete that's it you just need to write hyund delete and it can actually now delete the file from the SFTP server as soon as the files are downloaded successfully to the local folder so we can test it okay so I can just save this one click on Save now you can go back to the D files location and the files are already there so I can just delete them okay now I can open the vins CP and I can see that the files are here on the SFTP server if you want to refresh this particular folder so you can click here refresh and this can just refresh the folder for example if the new files are added or some files are deleted so you will get the latest state after clicking on the refresh button okay so now because I have made a change in the script that after downloading the script the file should deleted so let's run the ssis package and then let's see like how it will work and of course the log file will also be deleted okay I mean it will be deleted before running the execute process task so in case if the process is failed or successful so the log file will be recreated every time okay so this kind of thing so our process ran fine and if you go to the D files location so you can see that the files just got downloaded okay and now they should be deleted from the SFTP server so if you open the vinp client and now if you refresh this particular folder then the file should be gone so now you can see that all files are deleted from the SFP server because that's what we wanted because in the script if you open the script so we had written the hyph delete so this is pretty easy I think you know there is just two lines of code which can actually download the file from the SFTP server so that's great U maybe in next videos we will see like how we can upload the files to the SFTP server using the command lines so stay tuned for the upcoming videos so I will share the contents of the badge file you can actually copy them from the description of this particular video yes so I think that's it for today's video thank you guys for watching the video and if you like the video then please click the like button do subscribe to our Channel press the Bell icon and click on all so that you will be notified every time I a new video

Channel: Learn SSIS

Views: 1,776

Keywords: How to download files from sftp server using batch file, Download SFTP file using SSIS package, How to get file from SFTP to local directory using bat file, Want to automate download of files from Sftp to local, How do I download all files from SFTP server?, How, to, download, files, file, from, sftp, server, using, batch, command, line, prompt, Download, SFTP, package, get, local, directory, automate, all, ftp, winscp, learn ssis

Id: yVSvKZcDSUY

Length: 21min 1sec (1261 seconds)

Published: Tue Jan 23 2024