Initial recommendation

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

It could be a script in ShellScript, Python, PHP or a similar language executed in a scheduled way by cron (Linux) or Task Scheduler (Windows).

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

Solution with T-SQL

Enable running system commands

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

The first step is to enable the xp_cmdshell command. To do this, follow the steps 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

Calling FTP

Then, in your SQL script, you can recover the remote file by invoking the ftp utility of Windows. The -s parameter allows you to specify a file with the 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 comandos.txt file would have the FTP commands to be executed.

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

Reading the file into a variable

Then, to read the file, you can use the OPENROWSET command. You have an example in the documentation link that allows you to read the file in binary format. By modifying the format for characters, you can put 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;

Making the "parse"

The last step would be to do parse . In fact, the ideal is to already save the file in an appropriate format, separated by commas, with quotation marks, and so on. Try not to leave this load for SQL Server because it was not meant for this.

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

There are several string manipulation functions :

Final considerations

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

Safety

As for the security issue, this can be a problem depending on where FTP is. If it is an internal server of the company with access only by the administrators this would not be so bad. However, if it is an external FTP it can be a big flaw. First, FTP is not a secure protocol, data travels in plain text, for example. Second, you would be adding another invasion 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 a 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 may be a slow connection) and then interpret the content, can generate an unnecessary additional load on the server.

I do not know how SQL Server treats the threads and processes, but one of them would be blocked waiting for the response of the ftp command, for example.