153 How to upload files to sftp server using batch file in SSIS | WinScp

hi friends my name is Akil Ahmed and in this particular video tutorial I will show you how to upload files to SFTP server using batch file in ssis so the agenda of today's video tutorial is that we will see like how can we upload the files to SFP server using command line using the win SCP so we will be actually using the winp command lines to upload the files and then we will Implement that code inside the ssis package so without wasting further time let's jump to the demo so uploading the files or downloading the files from the SFTP server is very common these days and I already created a video like how you can download the files from the SFTP server and maybe I can share the link of that video in the description of this video so that you can see like how you can download the files from the SFTP server but in this video we will see like how we can upload the files back to the SFTP server so I have SFTP server installed on my machine so first of all let's connect to the SFTP server so we can use the vinp uh GUI tool to connect to the SFTP server if you don't have the VIN SCP tool then you can just directly Google it like wiin SCP and then you can download and install it so and I already have one of the video like how you can do that uh when I uh created a video like how you can configure the SFTP server on your machine and if you are struggling like downloading and installing the VP client then I will share another video where I install the SFTP server on the Windows machine and in that video I actually downloaded and installed the v c so I will share that video as well in the description of this video but because I already have the wi SCP CLI so I can just open the wiin SCP and then we can connect to it so that we can see like what files are available on the SFTP server so recently I had connected to the SFTP server so that's why it has saved my workpace but I can uh show you like connecting with a new workpace so I can click on new site okay and now inside the host name you need to provide your SFTP server address so in my case my SFTP server is installed on my laptop so I can just get the IP address from here and that IP address will be the host name so I can write ip config so I can copy the IP before address from here I can copy this one okay and then I can paste it here inside the host name and the port number will be the 22 the port number 22 is normally deserved for connecting to the SFTP server so you can connect to the multiple uh like FTP SFTP you can use multiple protocol but in this case we are going to connect to the SFTP server so that's why we will use the SFTP file protocol here okay secure file transfer protocol now inside the username and password you can provide your username and password that is given to you so normally when you upload or download the files from or to SFTP server then the client or your company gives you the credentials for the SFTP server like they give you the SFTP server address and then they give you the username and the password so in this case because my SFTP server is on my local machine so I will give my local machine name credentials HP and the password now I can click on the login so it should be connected yeah so on the right side these are the folders from the SFTP server so I will uh select this option C inside the C location I have the SFTP files folder so I want to upload the files to this location so I can refresh here so right now we don't have any files so you can right click and refresh here as well so right now inside this file C SFTP files we don't have any files as of now okay so we will upload the files here now let me open this location uh D files so in the D files location I got a apple directory and in the Apple directory I got few files here some CSU file and two xlsx files okay so first I will try to show you like how you can upload all CSV files from this Lo location you know all these files from this location to the uh SFTP server at this particular folder okay so the first thing that we will do is that we will click on the tabs and from here there is an option generate session URL okay so this will show you this particular script so you can actually copy this one okay and now paste it at some location so I can paste it here in the notepad++ now if I go back so if you see this one so this this is the like open SFTP this is the username and this is the password and this is the SFTP server address and this is the host key to connect to the SFTP server so these will be used okay and then you can put the command here like the command to upload the files to the SFTP server and if you see here these are the two uh these are the actually uh commments okay and in the commments they have given the option like the method like how you can actually uh create and how you can call your script that will have the SFTP commands Okay like you can use this win sc.exe to call your script the script that has the commands to upload the files to the SFTP server and this is the path to the log file so we will see in a bit like how we can utilize these particular scripts okay so for now we can close this one okay and we can minimize this one so first thing that we need to do is that we need to create a script uh that will have the command to upload the files to the SFTP server so I can uh go to this location D files and inside the script folder I can actually create a script file here so I can call the script file as upload files okay. txt and I can open this one now in this one we need to type our script okay so I can go back to the notepad++ okay and this is the same script that I copied you know uh from the uh VP client so I can copy this one and I can paste this one so this will actually open the connection to the SFTP server okay and now uh to upload the files to the SFTP server there is only single line of code okay that you need to write put okay and now in the double code you need to provide the location of the files like which files you want to upload so I want to upload the file uh this one like uh D files Apple so I can copy this path from here and then in the double code I can provide this path D files apple and inside this one I want to upload the star. CSV files so I want to upload all CSV files okay so these four files they should be uploaded so we provided the local file path now we need to provide the location of the SFTP server like in which folder you want to upload the files to so if you come to this particular location so this is the location of for/ C colum SL SFTP files for/ so this is the path like at this path I want to upload the files to so I can right click here and I can click on copy path to clipboard okay and now I can put the double code here then paste the path and then forward slash and then add the double code so I want to upload the files to this particular location so this is the only code just two lines of code and it should be sufficient to upload the files to the SFTP server so I can actually uh close this one save okay so the script has been saved okay and now I can open the ssis package so from the ssis package we can use the execute process task okay to call the badge file so I can just drag and drop the execute process task into the control flow window and then I can configure the execute process task so I can go to the process and in the executable location we need to provide the location of the vp. exe so if you go to this location so this script you know I got it from the v c you can I think if you remember otherwise you can just watch the video again okay like where I copied it so you just need to copy this path from here here okay and paste it inside the executable okay and now inside the arguments what you need to do you just need to uh copy this one uh the script so you can copy this one script paste it here okay and now in the script you need to actually provide the actual location of the script that we have created so we have created this script so I can copy this one upload files. txt so copy the path from here and now inside the double code provide the path okay so this is fine so this will be the script that will be executed and now uh we can also provide the log file path as well so that when the process will run it can actually create the log file and the log file is necessary because in case if the process will fail or it will succeed you know it will create the log file in every case in case if the process got failed then it will help you to get the error message like what is the error message that you are getting in the log file so I will create the log file at the same location so I can copy and paste the same location here okay and the look log file name will be either it can be like vp. log that's fine okay so this will be the log file name yeah so that's the only thing now I can click okay and we are good to run the ssis package now so I can click on start okay so it should execute the script file that we have created and it should upload all the CSV files to the SFTP server so the package run fine okay I can close this one if I want okay now first of all I will go to the uh this location where the script file is situated and this is the log file the size is really big 14 KB but that's fine so I can open this the log file and it should have the information about everything like what is happening in the script file so like uh we opening this particular SFTP server okay and uh if I scroll down where is the useful information that can help me okay so it's trying to upload this email. CSV file then email to. CSV file then employee. CSU so it seems like uh it has uploaded the files yeah student. CSU files all channels Clos so it has uploaded all the CSC files now I can go back to the uh V CP and I can actually refresh the this particular location it only shows you you know the files as soon as you refresh them so I refresh the folder and now you can see that the four files have been uploaded from my local machine to the SFTP server okay so this is working great now let me make a change to the um this particular script that instead of uploading only the CSV files from this folder okay if I do st. then it should upload the Excel files as well okay so what I can do I can just make a change a small change instead of star. CSV I can do star. star so now it should upload all the files okay this is really cool that there are just two lines of code and they are working great and even the first line was generated by the V cic line so I have only WR the one line like put and then the local file path and the remote file path and even the remote file path was we call copyed from the V CP client so this is really good now I can click on Save and now if I rerun the package so and you can also check the um size of the VIN cp. log it's 14 KB right now okay so let me rerun it and let's see if the size of the log file for example increases then what we can do before running this particular execute process Tas we can actually delete the log file so that every time the log file will be recreated okay okay and that will make more sense okay because we don't need the older log files the log file for the last execution should be sufficient so I can open the Venus cic light now and I can right click and refresh it so now you can see that it has uploaded the Excel files as well so this is pretty good now I can go back to this location and now you know the size of the log file is getting increased last time it was 14 KB now it is 30 KB so it is increasing a lot that I don't want to increase it okay uh I don't want the older log so I think this was the log from the previous run that all channels closed uh 2316 and now the new log file starts from the 2318 okay so I do not want the old log file so what I can do I can actually uh copy this location okay it's not showing yeah vp. log okay I can copy this particular location and now I can create a uh variable here and I can call it as log file okay uh data type string I can provide the value here that's good now I can use the file system task before calling this particular execute process task and I can call it as delete log file okay and I can just configure the this one pretty quickly uh delete file is Source path variable true and then the user defined variable log file so that's good I can click okay okay that's fine now when I will rerun it so the log file will be deleted and it will be actually recreated so it deleted and just recreated right it recreated at 11:21 you can see the time okay so you can see the time as well 11:21 so this is working absolutely great and the size of the lock file is also 16 KB so it was just recreated yes so I think that's it for today's video and if you have any questions then you can put your questions below and I will provide this command very small command like two lines of command I will put in the description of the video so that uh maybe you can copy it if you want okay and can use in your environment 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 put a new video thank you so much

Channel: Learn SSIS

Views: 1,457

Keywords: How to upload files to sftp server using batch file in SSIS, SFTP file transfer in SSIS, Uploading Files to SFTP Server using SSIS, How to transfer file to SFTP path using SSIS package?, How to upload file on SFTP server using batch script?, How do I upload files to SFTP server using WinSCP?, How, to, upload, files, file, sftp, server, using, batch, command, line, in, ssis, SSIS, WinScp, winscp, SFTP, transfer, Uploading, Files, package, learn ssis

Id: JV5HEm8-Fk8

Length: 15min 39sec (939 seconds)

Published: Mon Apr 01 2024