如何通过Microsoft SSIS连接SQL数据库并调用R/Python脚本输出至SSMS表

一、先搭好SSIS与SQL的基础连接 #

• 新建SSIS项目，在连接管理器面板添加OLE DB连接管理器，配置SQL Server地址、认证方式、目标数据库，测试连通后给连接起个好记的名字（比如Conn_SSDB）。
• 后续脚本的输入输出全靠这个连接，别搞错。

二、脚本任务集成R/Python（核心：对接SSIS数据） #

1. 集成Python脚本 #

• 从SSIS工具箱拖个脚本任务到控制流，双击打开后，在脚本选项卡选Python作为脚本语言。
• 点编辑脚本进入Python编辑器，重点做这几步：
  • 读取SSIS加载的数据：先把SQL源的数据通过数据流任务导入到记录集目标，然后把这个记录集赋值给一个SSIS变量（比如User::rsInputData，类型设为Object），还要在脚本任务的ReadOnlyVariables里添加这个变量。
  • 脚本里读取变量并转成DataFrame：

    import pandas as pd
    import clr
    clr.AddReference("Microsoft.SqlServer.Dts.Runtime")
    from Microsoft.SqlServer.Dts.Runtime import Dts
    
    # 读取SSIS记录集变量
    rs = Dts.Variables["User::rsInputData"].Value
    df = pd.DataFrame([[row[i] for i in range(row.Count)] for row in rs])
    # 手动给列名赋值，对应你的源表列名
    df.columns = ["ID", "Value1", "Value2"]
    

  • 执行你的自定义逻辑：

    # 示例：新增计算列
    df["Total"] = df["Value1"] + df["Value2"]
    

  • 把结果写入SQL表：

    import pyodbc
    
    # 直接用SSIS连接管理器的字符串，不用重复写
    conn_str = Dts.Connections["Conn_SSDB"].ConnectionString
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()
    
    # 先清空目标表（按需选择），再批量插入
    cursor.execute("TRUNCATE TABLE dbo.ResultTable")
    for _, row in df.iterrows():
        cursor.execute("INSERT INTO dbo.ResultTable (ID, Value1, Value2, Total) VALUES (?, ?, ?, ?)",
                      row["ID"], row["Value1"], row["Value2"], row["Total"])
    conn.commit()
    conn.close()
    

2. 集成R脚本 #

• 同样拖入脚本任务，选R作为脚本语言。
• 编辑脚本时的核心步骤：
  • 读取SSIS记录集变量：同样要先把数据流转成记录集变量，添加到ReadOnlyVariables。

    library(RODBC)
    library(data.table)
    
    # 读取SSIS里的记录集变量
    rs <- Dts$Variables[["User::rsInputData"]]$Value
    df <- as.data.table(rs)
    # 给列名赋值
    setnames(df, c("ID", "Value1", "Value2"))
    

  • 执行自定义R逻辑：

    # 示例：计算平均值列
    df[, AvgValue := (Value1 + Value2)/2]
    

  • 写入SQL表：

    # 调用SSIS的连接字符串
    conn_str <- Dts$Connections[["Conn_SSDB"]]$ConnectionString
    conn <- odbcDriverConnect(conn_str)
    
    # 清空目标表后写入
    sqlQuery(conn, "TRUNCATE TABLE dbo.ResultTable")
    sqlSave(conn, df, tablename = "dbo.ResultTable", append = TRUE, rownames = FALSE)
    close(conn)
    

三、必踩坑的注意事项 #

• 变量权限：记录集变量必须加到脚本任务的ReadOnlyVariables里，不然脚本读不到。
• 环境依赖：SSIS所在服务器必须装对应版本的Python/R，还要提前装好需要的库（比如pandas、pyodbc、RODBC），不然脚本跑不起来。
• 数据类型对齐：SSIS源数据的类型要和脚本里处理的类型匹配，比如日期、数值类型别乱转，容易报错。
• 调试技巧：脚本里加print()（Python）或cat()（R）打日志，脚本任务里勾上Enable Debugging，运行时能看日志找问题。

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