Download all files through FTP in SQL Server by serverku

We have completed the reading of earlier methods to receive single file and receive file dynamically through the FTP in SSIS. Hope you like those posts. Previously we have downloaded a file which has specific extension with format of current date. The same thing and criteria will be repeated again, but one thing is different and it's not downloading a single file and not the specific current date format. It's a download of multiple files which are having an extension with current year like you can see in below shot.

1. Here you can see the files from FTP, among them we will download files meet with the criteria. You can see target files.

2. Drag and drop FTP Task as shown on screen

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. Create one variable for the Remote Path for the FTP and assign the value of the FTP directory from where the file will be received.

7. Here we haven't given static value of Remote Path and file name, but the qualified name will be created dynamically. So apply expression for the Remote Path option like following.

8. Finally, execute the created SSIS package and you will see the files received in the local folder. You can see the target files received.

What is the next? Stay tuned for the next posts.

Script to download file via FTP in SQL Server by serverku

I want to share the script to download files via FTP in SQL Server, which need work directory to create an FTP command file to execute. This script will use ASCII transfer mode as a default to download files.

DECLARE
 @FTPServer varchar(128) ,
 @FTPUser varchar(128) ,
 @FTPPWD  varchar(128) ,
 @FTPPath varchar(128) ,
 @FTPFileName varchar(128) ,
 @SourcePath varchar(128) ,
 @SourceFile varchar(128) ,
 @workdir varchar(128),
 @cmd varchar(1000),
 @workfilename varchar(128)

SET @FTPServer = 'FTP Server Name'
SET @FTPUser = 'FTP User Name'
SET @FTPPWD = 'Password'
SET @FTPPath = '/subfolder1/subfolder2/' -- or '' if not having subfolders
SET @FTPFileName = 'FTP File Name'
SET @SourcePath = 'Local Path for download'
SET @SourceFile = 'Local File Name to be saved as'
SET @workdir = 'C:\FTP\'
SET @workfilename = 'ftpcmd.txt'
 
-- Writing steps to working file
SELECT  @cmd = 'echo '+ 'open ' + @FTPServer+ ' > ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ @FTPUser+ '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ @FTPPWD+ '>> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ 'get ' + @FTPPath + @FTPFileName + ' ' + @SourcePath + @SourceFile+ ' >> ' + @workdir + @workfilename
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'echo '+ 'quit'+ ' >> ' + @workdir + @workfilename
-- Executing steps from working file
EXEC master..xp_cmdshell @cmd
SELECT  @cmd = 'ftp -s:' + @workdir + @workfilename
-- Executing final step
EXEC master..xp_cmdshell @cmd

This reference is taken from here.

Download single file from FTP in SQL Server - SSIS by serverku

Here I am sharing the same thing, but it will be using SSIS instead of tsql script. Here I just want to show you a demo to download single file from an FTP. So let us move to the current post and follow the steps for same,

1. Here you can see the files from FTP, among them we will download one file.

2. Drag and drop FTP Task as shown in screen.

3. Choose new FTP connection option for to connect FTP and apply FTP configuration as shown below.

4. Here we want to download files, so we need to go ahead with 'Receive' operation.

5. Configure Local Folder for file to be downloaded.

6. From Remote Path option, select one of the file from FTP which is going to be received in local folder which we have configured in the previous step.

7. Finally, execute the created ssis package and you will see the file received in the local folder.

Hope you enjoyed this post. Stay tuned for more posts. Stay tuned for more!

How to reset SQL Server all default settings in SQL Server Denali CTP3? by serverku

Problem:

Before a few days ago I have installed SQL Server Denali CTP3. Then I was querying in query analyzer and found, minimizing result is not working with CTRL+R is not working. Before CTP3 I have already Denali CTP1 installed in my machine and it was working perfectly, So it stops to work with SQL Server CTP3.The default SQL Server settings was changed during this installation.

One of the solution:

I did not find any option to resolve it. Then I found one solution to reset these settings, which are changed during Denali CTP3 installation. Below are some snaps which I performed to recover it back.

Step 1.

Step 2.

Step 3.

Step 4.

Step 5.

After completion of all above steps, I recovered my SQL Server default setting and CTRL+R working perfectly now with CTP3.