FTP Files using SQL Server

In previous post we discussed BCP command and how it can be used to generate a csv file to a specified location. Today, we will see how you can FTP file created via BCP command to a given FTP Server. If you haven't done so, I suggest you review the previous post as we will build on that.

Note, this nice little stored procedure was originally developed by Nigel Rivett, although I have modified it a bit to better explain it here.

Previously, we created a file called "payroll.csv" in C:\Temp\Folder. Today, we will FTP this file to an FTP Server.

Lets create a stored procedure which will accept FTP parameters, file to FTP and then use xp_cmdshell to ftp the file.

CREATE PROCEDURE [dbo].[sp_FTP]
    @ServerName      varchar(50) ,
    @UserName        varchar(50) ,
    @Password        varchar(50) ,
    @FilePath        varchar(255) ,
    @FileName        varchar(255) ,
    @SourcePath      varchar(255) ,
    @SourceFile      varchar(255) ,
    @WorkingDir      varchar(255),
    @CommandFile     varchar(50)
AS

DECLARE @SQL varchar(1000)

--Since we want to echo the output, we need to replace some special characters
select @FTPServer = replace(replace(replace(@FTPServer, '|', '^|'),'<','^<'),'>','^>')
select @FTPUser = replace(replace(replace(@FTPUser, '|', '^|'),'<','^<'),'>','^>')
select @FTPPWD = replace(replace(replace(@FTPPWD, '|', '^|'),'<','^<'),'>','^>')
select @FTPPath = replace(replace(replace(@FTPPath, '|', '^|'),'<','^<'),'>','^>')

SELECT @SQL= 'echo ' + 'open ' + @ServerName + ' > ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + @UserName + '>> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + @Password + '>> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + 'put ' + @SourcePath + @SourceFile + ' ' + @FilePath + @FileName + ' >> ' + @workdir + @workfilename
exec master..xp_cmdshell @SQL

SELECT @SQL= 'echo ' + 'quit' + ' >> ' + @WorkingDir + @CommandFile
exec master..xp_cmdshell @SQL

SELECT @cmd = 'ftp -s:' + @WorkingDir + @CommandFile

CREATE TABLE #tempTable (ID INT IDENTITY(1,1), [Command] varchar(1000))
insert #tempTable
exec master..xp_cmdshell @SQL

select ID, ouputtmp = [Command] from #tempTable

@CommandFile parameter accepts a text file that will have the FTP command to execute

For example

open myftp.ftp.com
testuser
testpass
put c:\temp\payroll.csv payroll.csv
quit

Remember, xp_cmdshell is disabled by default. You can enable it by running the following script.

RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

You can call this stored procedure in this way...

EXEC sp_FTP
    @ServerName = 'myftp.ftp.com' ,
    @UserName   = 'testuser' ,
    @Password   = 'testpass' ,
    @FilePath   = '' ,
    @FileName   = 'payroll.csv' ,
    @SourcePath = 'C:\temp\' ,
    @SourceFile = 'payroll.csv' ,
    @WorkingDir = 'C:\temp\',
    @CommandFile = 'FTPCommand.txt'

Although not ideal, this is a nice little feature that you can use via xp_cmdshell to FTP files.

Thank you.

Using BCP command to create a CSV file from SQL Server

Today, we will review BCP client utility and XP_CMDSHELL to extract information from SQL Server into a csv file. Suppose you have a punch processing application which is used by employees to clock in and clock out. This information is stored in a database. Every night a SQL Server agent job runs which selects all the payroll hours for that day and extract into a csv file, saving it to a network location. The file can then be imported into a payroll processing application such as ADP.

In this article, we will review generating and extracting the csv file to a network location. In future post we will extend this further to FTP the file to a remote FTP Server.

So, let's get started.

1. Create a stored procedure that can be run as a step 1 in a SQL Agent Job.

CREATE PROC GetPayrollPunches
  AS
     BEGIN
                SELECT E.FirstName,E.LastName,P.Hours 
                FROM Employees E INNER JOIN 
               PayrollHours P ON E.EmployeeID = P.EmployeeID
    END
 GO

2. Create a BCP command as a second step in SQL Agent Job.

DECLARE @FileName varchar(50)
DECLARE @Command varchar(2000)

SET @FileName = 'C:\temp\payroll.csv'
SET @Command = 'bcp "EXEC tempdb..GetPayrollPunches" queryout ' + @FileName + '-t -c -T -S MyServer\SQL2008'
--Print (@Command)
EXEC master..xp_cmdshell @Command
GO

Note, you only have to pass server name when server is not default instance or you are running the utility from a different machine.

Switches

• -c output in ASCII
• -t  override field terminator with "," (instead of default tab)
• -T use a trusted connection. You can pass -U and -P for username and password instead.
• -S Server name. If the SQL Server is detault instance, you don't have to pass this parameter.

By default, xp_cmdshell is not enabled. You can enable it using the following command..

RECONFIGURE
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

BCP is a nice little utility to export data into a file format from SQL Server. I personally don't think this is a very elegant solution. Ideally, there should be an application to run the stored procedure, retrieve the data, and save it out. But, the functionality is there if you need it.

Thank you.