如何自动将SFTP文件夹中的XML文件导入SQL Server数据库？

目前已通过SQL查询使用OPENROWSET实现手动处理数据，但OPENROWSET不允许使用变量定义待导入文件的名称/路径。现有手动处理代码如下：

WITH rs (xmlData) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK N'C:\Users\SFLYNN01\Desktop\jobStatusUpdate-C158788-20231017161702.xml', SINGLE_BLOB) AS x
)
SELECT 
      c.value('(JobRef/text())[1]', 'VARCHAR(20)')AS JobRef
    , c.value('(Stages[Type/text()="1"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS ColCode
    , c.value('(Stages[Type/text()="1"]/DateTimeFrom/text())[1]', 'DATE') AS ColDate
    , c.value('(Stages[Type/text()="1"]/DateTimeFrom/text())[1]', 'TIME') AS ColTime
    , c.value('(Stages[Type/text()="2"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS DelCode
    , c.value('(Stages[Type/text()="2"]/DateTimeFrom/text())[1]', 'DATE') AS DelDate
    , c.value('(Stages[Type/text()="2"]/DateTimeFrom/text())[1]', 'TIME') AS DelTime
    , c.value('(Response/JobTrackingURL/text())[1]', 'VARCHAR(20)')AS TrackingURL
    , c.value('(statusId/text())[1]', 'VARCHAR(20)')AS Status
FROM 
    rs
CROSS APPLY 
    xmldata.nodes('/job') AS t(c)

现需要可自动处理SFTP文件夹中新传入XML文件的方案，因文件数量过多无法手动处理。

方案1：动态SQL + SQL Server代理作业 #

步骤： #

• 编写存储过程，通过动态SQL拼接OPENROWSET路径，遍历本地临时目录（需先从SFTP下载文件到该目录）的XML文件：

CREATE PROCEDURE dbo.ImportXMLFiles
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @FilePath NVARCHAR(500), @SQL NVARCHAR(MAX);
    -- 临时表存储文件列表
    CREATE TABLE #Files (FileName NVARCHAR(500));
    -- 读取指定目录下的XML文件
    INSERT INTO #Files
    EXEC xp_cmdshell 'dir "C:\SFTP_Downloads\*.xml" /b';

    DECLARE file_cursor CURSOR FOR
    SELECT FileName FROM #Files WHERE FileName IS NOT NULL;

    OPEN file_cursor;
    FETCH NEXT FROM file_cursor INTO @FilePath;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @SQL = N'
        WITH rs (xmlData) AS
        (
           SELECT TRY_CAST(BulkColumn AS XML) 
           FROM OPENROWSET(BULK N''C:\SFTP_Downloads\' + @FilePath + ''', SINGLE_BLOB) AS x
        )
        INSERT INTO YourTargetTable (JobRef, ColCode, ColDate, ColTime, DelCode, DelDate, DelTime, TrackingURL, Status)
        SELECT 
              c.value(''(JobRef/text())[1]'', ''VARCHAR(20)'')AS JobRef
            , c.value(''(Stages[Type/text()="1"]/Address/AddressCode/text())[1]'', ''VARCHAR(20)'')AS ColCode
            , c.value(''(Stages[Type/text()="1"]/DateTimeFrom/text())[1]'', ''DATE'') AS ColDate
            , c.value(''(Stages[Type/text()="1"]/DateTimeFrom/text())[1]'', ''TIME'') AS ColTime
            , c.value(''(Stages[Type/text()="2"]/Address/AddressCode/text())[1]'', ''VARCHAR(20)'')AS DelCode
            , c.value(''(Stages[Type/text()="2"]/DateTimeFrom/text())[1]'', ''DATE'') AS DelDate
            , c.value(''(Stages[Type/text()="2"]/DateTimeFrom/text())[1]'', ''TIME'') AS DelTime
            , c.value(''(Response/JobTrackingURL/text())[1]'', ''VARCHAR(20)'')AS TrackingURL
            , c.value(''(statusId/text())[1]'', ''VARCHAR(20)'')AS Status
        FROM 
            rs
        CROSS APPLY 
            xmldata.nodes(''/job'') AS t(c)';
        EXEC sp_executesql @SQL;

        -- 导入后将文件移至归档目录
        EXEC xp_cmdshell 'move "C:\SFTP_Downloads\' + @FilePath + '" "C:\SFTP_Archive\"';

        FETCH NEXT FROM file_cursor INTO @FilePath;
    END

    CLOSE file_cursor;
    DEALLOCATE file_cursor;
    DROP TABLE #Files;
END

• 配置SQL Server代理作业，定期执行该存储过程；同时添加PowerShell脚本步骤，自动从SFTP下载新文件到本地目录，确保作业账户拥有SFTP访问、文件系统读写和SQL Server操作权限。

方案2：SSIS（SQL Server Integration Services） #

步骤： #

• 创建SSIS包，添加SFTP任务从远程服务器下载新XML文件到本地目录。
• 添加Foreach循环容器，遍历本地目录中的XML文件。
• 在循环内添加XML源组件，通过XPath提取所需字段，映射到SQL Server目标表完成导入。
• 添加文件移动任务，将已处理文件移至归档目录避免重复导入。
• 将SSIS包部署到SSIS目录，通过SQL Server代理定期执行，或借助自定义脚本监控目录实现文件触发执行。

方案3：PowerShell脚本自动化 #

步骤： #

• 编写PowerShell脚本，整合SFTP下载、文件遍历、SQL导入逻辑：

# 加载WinSCP组件（需预先安装WinSCP .NET Assembly）
Add-Type -Path "C:\Program Files (x86)\WinSCP\WinSCPnet.dll"

# SFTP连接配置
$sessionOptions = New-Object WinSCP.SessionOptions -Property @{
    Protocol = [WinSCP.Protocol]::Sftp
    HostName = "your-sftp-server.com"
    UserName = "sftp-user"
    Password = "sftp-password"
    SshHostKeyFingerprint = "ssh-rsa 2048 xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx"
}

$session = New-Object WinSCP.Session
try {
    $session.Open($sessionOptions)
    # 下载SFTP上的新XML文件到本地
    $transferOptions = New-Object WinSCP.TransferOptions
    $transferOptions.TransferMode = [WinSCP.TransferMode]::Binary
    $transferResult = $session.GetFiles("/remote-sftp-path/*.xml", "C:\Local_XML_Files\")
    
    # 遍历已下载文件并导入SQL
    foreach ($file in $transferResult.Transfers) {
        $localPath = $file.Destination
        $sql = @"
WITH rs (xmlData) AS
(
   SELECT TRY_CAST(BulkColumn AS XML) 
   FROM OPENROWSET(BULK N'$localPath', SINGLE_BLOB) AS x
)
INSERT INTO YourTargetTable (JobRef, ColCode, ColDate, ColTime, DelCode, DelDate, DelTime, TrackingURL, Status)
SELECT 
      c.value('(JobRef/text())[1]', 'VARCHAR(20)')AS JobRef
    , c.value('(Stages[Type/text()="1"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS ColCode
    , c.value('(Stages[Type/text()="1"]/DateTimeFrom/text())[1]', 'DATE') AS ColDate
    , c.value('(Stages[Type/text()="1"]/DateTimeFrom/text())[1]', 'TIME') AS ColTime
    , c.value('(Stages[Type/text()="2"]/Address/AddressCode/text())[1]', 'VARCHAR(20)')AS DelCode
    , c.value('(Stages[Type/text()="2"]/DateTimeFrom/text())[1]', 'DATE') AS DelDate
    , c.value('(Stages[Type/text()="2"]/DateTimeFrom/text())[1]', 'TIME') AS DelTime
    , c.value('(Response/JobTrackingURL/text())[1]', 'VARCHAR(20)')AS TrackingURL
    , c.value('(statusId/text())[1]', 'VARCHAR(20)')AS Status
FROM 
    rs
CROSS APPLY 
    xmldata.nodes('/job') AS t(c)
"@
        Invoke-SqlCmd -ServerInstance "YourSQLServer" -Database "YourDB" -Query $sql
        
        # 移至归档目录
        Move-Item -Path $localPath -Destination "C:\XML_Archive\"
    }
} finally {
    $session.Dispose()
}

• 将脚本设置为Windows定时任务，定期执行，确保执行账户拥有SFTP访问、文件系统读写和SQL Server操作权限。

内容的提问来源于stack exchange，提问作者steve_flynn