Insert SQL Server records via ftp file.txt

Question

I have the following structure in a file . txt of an FTP server:

CodigoPedido: 120952

DataPedido: 2014-03-11-10:19

LocalVenda: MERCADO LIVRE

Status: A ENVIAR

Parceiro: 0

IdCliente: 103002

NomeCliente: Juliano Ramires Garcia | JRAMIRES GARCIA

Is there any way to automate the process for SQL Server to connect to FTP, load the file, parse, insert the record and delete the FTP file?

Answer 1

Initial recommendation

First of all, I would recommend creating a separate process to do this.

Could be a script in Shellscript, Python, PHP or a similar language executed in a scheduled manner by cron (Linux) or Task Scheduler (Windows).

This script would be responsible for recovering the file, reading the data, validating and then sending a command to SQL, being this passive in this story.

Solution with T-SQL

Enable system command execution

However, the T-SQL language (Transact-SQL) has commands that allow interaction with the environment.

The first step is to enable the command xp_cmdshell. For this, follow the steps described in official documentation:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Call the FTP

So, in your SQL script, you can recover the remote file by invoking the utility ftp of Windows. The parameter -s allows specifying a file with FTP commands. See this example:

Set @cmd = 'ftp -s:"C:\comandos.txt" 127.0.0.0'
exec master..xp_cmdshell @cmd

In the above example, the file comandos.txt would have FTP commands to be executed.

But the important thing here is that with the ability to run any program, you can virtually do whatever you want. You can call a program in Java or any script.

Reading the file in a variable

Then to read the file, you can use the command OPENROWSET. There is an example in the documentation link that allows you to read the file in binary format. By changing the format to characters, you can put it in any variable. Use SINGLE_CLOB if the contents of the file are encoded in ASCII or SINGLE_NCLOB if it is Unicode. Example:

DECLARE @conteudo NVARCHAR(2000)
SELECT @conteudo = BulkColumn
FROM OPENROWSET(BULK N'C:\dados.txt', SINGLE_CLOB) AS Document;

Doing the "parsing"

The last step would be to do the parse. In fact, the ideal is to already write the file in a suitable format, separated by commas, with quotes and so on. Try not to leave this load for SQL Server because it was not made for this.

I am not going to develop the solution because it would be something very specific and not worth it. However, I will leave some commands that can help if you need.

There are several string handling functions:

1. With the CHARINDEX and PATINDEX you can find the position of Labels.

2. So with the SUBSTRING you can pick up the text that is in front of them. It is not so complicated, it is more a matter of manual work.

Final considerations

Although it is possible to do this, there are better and more flexible ways, as presented at the beginning of the answer.

Security

As for the security issue, this can be a problem depending on where FTP is located. If it’s an internal company server with just admin access, that wouldn’t be so bad. However, if it is an external FTP it can be a major failure. First, FTP is not a secure protocol, the data moves in plain text, for example. Second, you’d be adding another breaking point. If a hacker compromises the FTP server, it can inject spurious commands into the text file and be able to execute some command on its database server. What would be the name of a client, can be an SQL Injection.

Performance

Another factor that can be impacted is the performance of your database server. Depending on how many files are read on the day, the fact that SQL has to connect to FTP (which can be a slow connection) and then interpret the content, can generate an unnecessary additional load to the server.

I honestly don’t know how SQL Server addresses the issue of threads and lawsuits, but surely one of them would be blocked waiting for the response of the command ftp, for example.