是否可以仅使用 T-SQL 命令而不是 SSIS 让 SQL Server 登录 SFTP 并上传文件？

我有一个 SSIS 包，它可以登录到我客户端的 SFTP 站点并使用发送到 WinSCP 的命令上传文件。

这个包经常失败并且很难使用。 Visual Studio 崩溃如此频繁，以至于我的团队拒绝再使用它。我们正在将所有自动化任务迁移到存储过程和 SQL 代理作业，因为它们比 SSIS 工作得更好。

然而，这让我们没有好的解决方案来自动通过 FTP 上传 SQL Server 上生成的报告文件。

我一直使用 PSFTP 和从 VB 脚本调用的批处理文件来完成此操作。该脚本检查特定文件夹以查看文件是否存在，如果存在则上传该文件。这可行，但不太优雅，我必须安排这些脚本每 15 分钟在文件夹中查找一个文件，而不是仅仅从 SQL Server 上的存储过程调用“上传”命令。如果有人重命名该文件夹中的文件，或者在其中放入两个文件等，则可能会出现严重错误。

我可以使用 BCP 轻松创建 .CSV 文件作为存储过程的一部分到特定文件夹中，例如：

--Create Client Update .CSV File if there are any rows in the CLIENT_UPDATE table
IF EXISTS(SELECT 1 FROM CLIENT_UPDATE)
BEGIN
DECLARE @ColumnHeader VARCHAR(8000)
    SET @FileName = @DataPath + '\Output File.csv'
 SELECT @ColumnHeader = COALESCE(@ColumnHeader+',' ,'')+''''+column_name+'''' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='CLIENT_UPDATE'
    SET @BCPCommand = 'bcp "SELECT '+ @ColumnHeader +' UNION ALL SELECT * FROM CLIENT_UPDATE" queryout "' + @FileName + '" -c -t , -r \n  -S . -T'
   EXEC master..xp_cmdshell @bcpCommand
END

我希望该存储过程的下一步是“将 .CSV 文件上传到客户端 SFTP 站点”。我将如何实现这个目标？

Success!

我在名为的数据库上创建了一个存储过程sp_UploadFileToSFTP，它使用 PSFTP.EXE 发送文件。

-- ================================================================================================================================
-- Description: Upload File to SFTP
--              - Creates "SFTPUploadScript.bat" and "SFTPUploadScript.txt" scripting files to control psftp.exe, then executes the batch file
--              - Requires a local @ScriptFolder on the SQL Server to create files inside, and which contains psftp.exe
--              - "SFTPUploadScript.bat" needs to be run once manually from the SQL Server desktop to capture the keypair of the SFTP site
--                After this, the script will work automatically
-- ================================================================================================================================
CREATE PROCEDURE sp_UploadFileToSFTP 

    @FilePathToUpload varchar(1000),
    @SFTPSiteAddress varchar(1000),
    @SFTPLogin varchar(1000),
    @SFTPPassword varchar(1000),
    @SFTPRemoteFolder varchar(1000)

AS
BEGIN
SET NOCOUNT ON;

--Declare Variables
 DECLARE @ScriptFolder varchar(1000)
     SET @ScriptFolder = 'C:\SFTPBatchFiles\'
 DECLARE @CommandString AS varchar(8000)

--Delete Existing files if they exist
     SET @CommandString = 'del "'+ @ScriptFolder + 'SFTPUploadScript.txt"'
    EXEC master..xp_cmdshell @CommandString
     SET @CommandString = 'del "'+ @ScriptFolder + 'SFTPUploadScript.bat"'
    EXEC master..xp_cmdshell @CommandString

--Create Batch fle with login credentials
     SET @CommandString = 'echo "'+ @ScriptFolder +'psftp.exe" ' + @SFTPSiteAddress + ' -l ' + @SFTPLogin + ' -pw ' + @SFTPPassword + ' -b "' + @ScriptFolder + 'SFTPUploadScript.txt" > "' + @ScriptFolder + 'SFTPUploadScript.bat"'
    EXEC master..xp_cmdshell @CommandString

--Create SFTP upload script file
     SET @CommandString = 'echo cd "' + @SFTPRemoteFolder + '" > "' + @ScriptFolder + 'SFTPUploadScript.txt"'
    EXEC master..xp_cmdshell @CommandString
     SET @CommandString = 'echo put "' + @FilePathToUpload + '" >> "' + @ScriptFolder + 'SFTPUploadScript.txt"'
    EXEC master..xp_cmdshell @CommandString

--Run the Batch File
     SET @CommandString = @ScriptFolder + 'SFTPUploadScript.bat'
    EXEC master..xp_cmdshell @CommandString

END
GO

然后我可以从另一个存储过程中调用它，如下所示：

sp_UploadFileToSFTP 'C:\FileToUpload\Test.txt','sftp.mysite.com','[email protected] /cdn-cgi/l/email-protection','Password1234','UploadFolder/In here/'

一切都很完美，运行大约需要四分之一秒，这是一个非常简洁和稳定的解决方案。

与我之前使用 SQL Server 将文件放入文件夹中，然后使用 Visual Basic SFTP 上传脚本每 15 分钟检查该文件夹中是否有新文件相比，这要好得多:)

如果您想自己尝试一下，您只需要在 SQL Server 的 C:\ 驱动器上建立一个名为“SFTPBatchFiles”的文件夹，然后在其中放置 psftp.exe，它是 PuTTY 的一部分。

您还需要一个信任的服务器管理员，他将允许您使用 xp_cmdshell 运行 psftp 命令和您自己的批处理文件，并且他们可能需要在您的防火墙中打开一条路由，以便您可以直接从 SQL Server 连接到远程站点。

最后，您还需要能够通过远程桌面访问 SQL Server，因为您需要运行一次手动创建的批处理文件，并在 psftp 要求您接受新密钥对时按“Y”。之后，一切就一帆风顺了。

没有 SSIS，没有 WINSCP，没有 Visual Studio，没有数小时的崩溃和调试！

现在唯一的问题是，如果 FTP 传输由于某种原因失败，存储过程仍然报告成功，我不会将 psftp 错误消息带回 SQL Server。

我的下一个任务是捕获从 xp_cmdshell 生成的状态和错误消息，并向用户提供一些详细的反馈，并在进程失败时生成错误。

UPDATE:

捕获来自远程 FTP 站点的错误和反馈

--Run the Batch File
  DROP TABLE IF EXISTS #CommandOutput
CREATE TABLE #CommandOutput (ReadLine varchar(1000))
         SET @CommandString = @ScriptFolder + 'SFTPUploadScript.bat'
      INSERT #CommandOutput
        EXEC master..xp_cmdshell @CommandString

--Check for Invalid Password error  
IF EXISTS (SELECT ReadLine 
            FROM #CommandOutput 
           WHERE ReadLine LIKE '%Invalid Password%')
    BEGIN
          PRINT 'Invalid Password Error!'
      END

现在，批处理文件运行时，批处理文件的所有输出（如果手动运行，通常会显示在屏幕上）将逐行插入到名为 #CommandOutput 的临时表中。

批处理文件运行后，您可以查询 #CommandOutput 以查看传输发生了什么。

我在输出中的任何位置添加了对“无效密码”一词的简单检查，因为这是您可能遇到的常见错误。您可以根据需要添加任意数量的这些检查，也可以将整个表导入到每次作业运行时发送给您的电子邮件中，将表记录到 FTP 事件日志表中，或者进行任意数量的其他操作。