是否可以仅使用T-SQL命令而不使用SSIS来使SQL Server登录到SFTP并上传文件? [英] Is it possible to get SQL Server to log into an SFTP and upload a file just by using T-SQL commands, no SSIS?

问题描述

我有一个SSIS程序包，该程序包登录到客户端的SFTP站点并使用发送到WinSCP的命令上载文件.

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

但是，这给我们提供了一个自动化SQL Server上生成的报告文件的FTP上传自动化的好的解决方案.

我一直在使用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站点".我该怎么办?

解决方案

成功！

我在名为 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','[e-mail]','Password1234','UploadFolder/In here/'

所有操作都完美完成，大约需要四分之一秒才能运行，这是一个非常简洁且稳定的解决方案.

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

如果您想亲自尝试，只需在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生成的状态和错误消息，并向用户提供一些详细的反馈信息，并在处理失败时生成错误.

更新:

陷阱错误和来自远程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的临时表中./p>

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

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

I have an SSIS package which logs into my client's SFTP site and uploads a file using commands sent to WinSCP.

This package fails quite often and is difficult to work with. Visual Studio crashes so frequently that my team refuses to use it any more. We are migrating all automated tasks to Stored Procedures and SQL Agent Jobs, as they work a lot better than SSIS does.

However, this leaves us with no good solution for automating FTP uploads of report files produced on SQL Server.

I have been doing it using PSFTP and batch files called from VB scripts. The script checks a particular folder to see if a file is there and uploads it if it is. This works, but is inelegant and I have to schedule these scripts to look for a file in the folder every 15 minutes, rather than just call an "upload" command from my stored procedure on SQL Server. There's potential for things to go horribly wrong if someone renames a file in that folder, or puts two files in there, etc.

I can easily create a .CSV file as part of a stored procedure into a particular folder using BCP, e.g.:

--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

I'd like the next step in that stored procedure to be "Upload .CSV file to client SFTP site". How would I accomplish this?

解决方案

Success!

I created a stored proc on my database named sp_UploadFileToSFTP, which uses PSFTP.EXE to send files.

-- ================================================================================================================================
-- 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

I can then call it from inside another stored proc like so:

sp_UploadFileToSFTP 'C:\FileToUpload\Test.txt','sftp.mysite.com','[e-mail]','Password1234','UploadFolder/In here/'

All works perfectly, takes about a quarter of a second to run, a very neat and stable solution.

Compared to what I was doing before which was using SQL Server to drop a file into a folder, then checking that folder for new files every 15 minutes with my Visual Basic SFTP upload script, it's miles better :)

If you want to try this yourself, all you need is a folder on the C:\ drive of your SQL Server named "SFTPBatchFiles", and in there you put psftp.exe, which is part of PuTTY.

You will also need a trusting server admin who will allow you to run psftp commands and your own batch files using xp_cmdshell, and they'll probably need to open a route in your firewall so you can connect to your remote site directly from the SQL Server.

Finally, you will also need to be able to remote desktop into your SQL Server, since you'll need to run the batch file it creates manually once and press "Y" when psftp asks you to accept the new keypair. After that, it's all plain sailing.

No SSIS, no WINSCP, no Visual Studio, no hours upon hours of crashes and debugging!

The only issue right now is that if the FTP transfer fails for whatever reason, the stored procedure still reports success, I'm not bringing the psftp error messages back into SQL Server.

My next task will be to trap the status and error messages as they get generated from xp_cmdshell and give the user some detailed feedback and generate errors if the process fails.

UPDATE:

Trapping errors and feedback from the remote FTP site

--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

All of the output from the batch file (what would normally be shown on the screen if you were to run it manually) is now inserted line-by-line into a temporary table named #CommandOutput as the batch file runs.

After the batch file runs, you can then query #CommandOutput to see what's happened with your transfer.

I've added a simple check for the words "Invalid Password" anywhere in the output, as this is a common error you might have. You could add as many of these checks as you liked, or you could import the entire table into an email which gets sent to you every time the job runs, log the table to an FTP event logging table, or any number of other things.

这篇关于是否可以仅使用T-SQL命令而不使用SSIS来使SQL Server登录到SFTP并上传文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！