Need to replace FTP with a SQL Server stored procedure... 1

[Webkins]

Good morning to all...

I am currently using a Windows 2012 Server with a MSSql 2012 stored procedure that calls the Windows FTP program to transfer a text file from one location to another. I am pretty sure that FTP is not multi-threaded which is why I am having problems using this program to transfer data. I have 5 MSSql stored procedures calling FTP at various times to transfer many files throughout the day. I believe that many are transactions are taking place at the same time and therefore failing due to multiple FTP programs being open at the same time. I am sending the text file from the server folder to a TCP/IP destination. The largest text file size is 60KB.

I am curious if there is a similar way to do this with only a stored procedure using something available within MSSql so that I can eliminate the FTP software usage.

Here is 1 of the 5 Stored Procedures code currently in use.

CREATE PROCEDURE ftp_one
(@file varchar(255))
AS
SET NOCOUNT ON
DECLARE @file_to_push varchar(255), @ftp_to_server varchar(255), @cmd varchar(255)
DECLARE @batch_ftp varchar(255), @put varchar(255), @rc int
SET @ftp_to_server = '192.168.7.1'
SET @file_to_push = '\\mxbusaccp1ms001\Common\Data\' + @file + '.txt'
SET @batch_ftp = '\\mxbusaccp1ms001\Common\Data\1.txt'
SET @put = 'echo put ' + @file_to_push + ' bye >' + @batch_ftp
EXEC master.dbo.xp_cmdshell @put, no_output
SET @cmd = 'ftp -v -n -s:' + @batch_ftp + ' ' + @ftp_to_server
EXEC master.dbo.xp_cmdshell @cmd, no_output
GO

Thank you for any and all replies.

 

[Olaf Doschke]

What I'd do is store FTP jobs into a job table, that's an easy job for SQL

Then let an external process do the transfers. There are nice libraries for whatever programming language you're fluent with, in the end it could be your current code, just feeding itself from this job table.
You can schedule an SSIS package or use Powershell to both connect to SQL and FTP.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[johnherman]

nice solution, Olaf!

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity