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

Thank you Srey Leth.

Thank you.

There are 2 options here. 1. You can generate the bat command using the script task using C#. You can use the variable to get the current date in YYYYMMDD format and accordingly use that variable to concatenate the folder path. I would suggest you to take help from chatgpt and ask it to give you the C# code to do the same. Give it the static batch file command and ask to make the path dynamic with current date time and ask to write a C# script to produce that script. 2. You can use the variable in the batch command to get the current date. However I have not tried it before but you can try and here as well see if chatgpt can help to write the script for you. stackoverflow.com/questions/10945572/windows-batch-formatted-date-into-variable

@@learnssisThank you for explanation. I couldn't figure out how to inject variable into the bat command as it's not valid with SFTP connection string. C # code also requires third party library to code. I managed to get a plugin for SSIS which allows SFTP connection and special File Transfer Task which resolves it. Thanks again for your kind help.

For that scenario you would need to create the batch dynamically using C# code.

@@learnssis thanks Sir ❤

1. I am not sure here which account can be used in sql agent job to execute the ssis package as it will need to login to sftp server and delete the file from sftp server. Can you take a look at this url ? They are calling .batch file directly from the sql agent job www.mssqltips.com/sqlservertip/6285/automate-sftp-file-transfer-with-sql-server-agent-and-winscp/ 2. Do you mean overwrite file at sftp server ? I don't think we can overwrite file at sftp server.

Is it not overwriting the existing file in the destination folder ? I think it should overwrite the file in the local folder.

@@learnssis I have checked but .bat file unable to overwrite existing file. Can you please check and confirm

@@TKJ Can you add -overwrite in the end of this line and see if it works. get "C:/Data to Load/*.csv" "D:\Files\" -delete -overwrite

@@learnssis thanks I'll check

Winscp should be installed on the server on which you are deploying your ssis package. Winscp.exe file should present at the correct location on server whatever location is given in the Execute process task.

Winscp is installed on the server and also the file location is correct

Hi Sir could you create one video on,How to run this job under SQL server agent

@@user-mp1jn8vv3f There is no different way of scheduling this under sql agent job. You might be getting the errors because of permissions issue as the owner of sql agent service might not have proper access either on local or on sftp. Try to schedule this from windows task scheduler using your own account and see if that works, if it works then it might be permissions issue ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-w269R8gd_co.html

Yes, It's working under task scheduler.

Sorry not sure how to do this with command line, but we can do this using C# ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-2HdpMMPMo1A.html

Here there are few things. If you are trying to import the 2 excel files and they have different columns then they can't be imported using foreach loop container because a data flow task is very schema specific and it can't import the 2 excel files if they have different columns. Here you have 2 options 1. Use 2 data flow task, in first data flow task import first excel file. In second data flow task, import second excel file to sql server table. Now take an execute sql task and then execute the sp from the execute sql task. So you will have 3 tasks, and they will run in sequence, data flow task1, data flow task2 and execute sql task. 2. Another option is that you can use 2 foreach loop container, and in the first foreach loop container you can import 1 file. And in second foreach loop container you can import second file. So initially, first foreach loop container will be executed and then second foreach loop container will be executed, and then the execute sql task will be executed.

@@learnssis I have to pass an Excel file as input to the stored procedure, the stored procedure will load the data into a table using the open rowset method. Here we are not using a Data flow task. I am using execute SQL task

@@santhoshsomu6711Sorry I don't know if we can import the excel file to the sql server table. Did you tried importing the excel file to sql server table ? I know we can import the csv file to sql server table using sql query, but I am not sure if we can import the excel file to sql server table using sql query. Can you ask chatgpt if we can do or google it ?

@@learnssis Yes we can import the excel file to sql server table using Open row set method. We are using that.

Okay, I have explained in my previous reply that how we can do that using ssis, however I am not sure how we can achieve the same using sql query.

If I look at the error message so it is saying "Access is denied". It seems like it is unable to create the file locally. Can you try give the download location in another folder, maybe in D drive, E drive or at least in another folder ?