SQL Server 2019部署时SSIS脚本任务失败问题求助

数据老陈

2026-7-11

SSIS脚本任务部署到SQL Agent 2019后编译失败问题排查

问题现象

本地使用Visual Studio 2019（目标版本设为SQL Server 2019）运行SSIS作业无错误，但部署到SQL Agent 2019后出现以下错误：

Get Tables for Report:Error: 任务验证期间出错。
Get Tables for Report:Error: 未找到脚本的二进制代码，请点击编辑脚本按钮在设计器中打开脚本并确保构建成功。
Script Task 4:Error: 未找到脚本的二进制代码，请点击编辑脚本按钮在设计器中打开脚本并确保构建成功。
Script Task 4:Error: CS1504 - 无法打开源文件'c:\Windows\Temp.NETFramework,Version=v4.7.AssemblyAttributes.cs'（拒绝访问），CSC，0，0
Script Task 4:Error: 未能编译包中包含的脚本，请在SSIS设计器中打开包并解决编译错误。

已尝试操作：

原以为是权限问题，让DBA运行也出现相同错误。
删除并重新创建脚本任务，且每个任务都已构建并保存。

相关脚本代码

Get Tables for Report脚本

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Text;
#endregion

namespace ST_70ab92c155744d1396f11009d032d49f
{
    
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {


        public void Main()
        {
            Variables vars = null;
            OleDbConnection conn = null;
            string password = string.Empty;

            try
            {
                Dts.VariableDispenser.LockForRead("$Package::SqlDatabasePW");
                Dts.VariableDispenser.LockForWrite("User::sqlstatement1");
                Dts.VariableDispenser.LockForWrite("User::sqlstatement2");
                Dts.VariableDispenser.LockForWrite("User::sqlstatement3");
                Dts.VariableDispenser.LockForWrite("User::sqlstatement4");
                Dts.VariableDispenser.GetVariables(ref vars);

                password = vars["$Package::SqlDatabasePW"].GetSensitiveValue().ToString();
                ConnectionManager cm = Dts.Connections["LCGMS Connection Manager"];
                string originalConnectionString = cm.ConnectionString;
                string passwordToAdd = "Password=" + password + ";";
                string connString = originalConnectionString + passwordToAdd;
                conn = new OleDbConnection(connString);
                conn.Open();

                string[] sqlStatements = new string[]
                {
            "SELECT count(*) as Count FROM Supertable_Storage_Staging WHERE(PDB1_Upload = 'N');",
            "SELECT Fiscal_Year + '-' + Location_Code + '-' + CONVERT(varchar(04), Count(*)) FROM Supertable_Storage_Staging WHERE(PDB1_Upload = 'N') AND (Update_Flag='I') GROUP BY Fiscal_Year + '-' + Location_Code + '-' ORDER BY Fiscal_Year + '-' + Location_Code + '-';",
            "SELECT Fiscal_Year + '-' + Location_Code + '-' + CONVERT(varchar(04), Count(*)) FROM Supertable_Storage_Staging WHERE(PDB1_Upload = 'N') AND (Update_Flag='U') GROUP BY Fiscal_Year + '-' + Location_Code + '-' ORDER BY Fiscal_Year + '-' + Location_Code + '-';",
            "SELECT Fiscal_Year + '-' + Location_Code + '-' + CONVERT(varchar(04), Count(*)) FROM Supertable_Storage_Staging WHERE(PDB1_Upload = 'O') AND (Update_Flag='D') GROUP BY Fiscal_Year + '-' + Location_Code + '-' ORDER BY Fiscal_Year + '-' + Location_Code + '-';"
                };

                for (int i = 0; i < sqlStatements.Length; i++)
                {
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatements[i], conn))
                    {
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            StringBuilder resultBuilder = new StringBuilder();
                            while (reader.Read())
                            {
                                string count = reader[0].ToString();
                                resultBuilder.AppendLine(count);
                            }
                            string varKey = "User::sqlstatement" + (i + 1).ToString();
                            vars[varKey].Value = resultBuilder.ToString();
                        }
                    }
                }

                Dts.TaskResult = (int)ScriptResults.Success;
                vars.Unlock();
            }
            catch (Exception ex)
            {
                Dts.Events.FireError(0, "Script Task Error", ex.Message + "\n" + ex.StackTrace, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };


    }
}

Script Task脚本

#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text;
using System.Threading;
using System.Data.OleDb;
#endregion

namespace ST_143da356cdc14c98a01686eb6c63137b
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
       
   public void Main()
        {
            int insertedRows1 = 0;
            int insertedRows2 = 0;
            int updatedRows1 = 0;
            int updatedRows2 = 0;

            int deletedRows = 0;
            string ServerName = string.Empty;
            string sqlstatement1 = string.Empty;
            string sqlstatement2 = string.Empty;
            string sqlstatement3 = string.Empty;
            string sqlstatement4 = string.Empty;

            try
            {


                insertedRows1 = (int)Dts.Variables["User::InsertedRowCount1"].Value;
                insertedRows2 = (int)Dts.Variables["User::InsertedRowCount2"].Value;
                updatedRows1 = (int)Dts.Variables["User::UpdatedRowCount1"].Value;
                updatedRows2 = (int)Dts.Variables["User::UpdatedRowCount2"].Value;

                deletedRows = (int)Dts.Variables["User::DeletedRowCount"].Value;
                ServerName = (string)Dts.Variables["$Package::SqlDatabaseServer"].Value;
                bool isDataFlowSuccessful = (bool)Dts.Variables["User::isDataFlowSuccessful"].Value;
                String Environment = (string)Dts.Variables["$Package::Environment"].Value;

                int totalInsertedRows = insertedRows1 + insertedRows2;
                int totalUpdatedRows = updatedRows1 + updatedRows2;
                int totalTransaction = totalInsertedRows + totalUpdatedRows + deletedRows;

                sqlstatement1 = (string)Dts.Variables["User::sqlstatement1"].Value;
                sqlstatement2 = (string)Dts.Variables["User::sqlstatement2"].Value;
                sqlstatement3 = (string)Dts.Variables["User::sqlstatement3"].Value;
                sqlstatement4 = (string)Dts.Variables["User::sqlstatement4"].Value;

                if (isDataFlowSuccessful == true)
                {
                    Dts.Variables["User::emailsubject"].Value = "LCGMS_Supertable_Update_Process_LC1P1_" + Environment + ":" + ServerName + " Success on " + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                    Dts.Variables["User::emailbody"].Value = string.Format(
     "Subject:\t\tSupertable Hourly Process For LC1P1 DB2 Region Successfully Updated (or Override with the failure message)\n" +
     "Project Name:\t\tLCGMS_Supertable_Update_Process_LC1P1\n" +
     "Purpose:\t\tThis Job gets the data from the Supertable_Storage_Staging SQL table and updates LC1U1.LOCATION_SUPERTBL1 DB2 table.\n" +
     "\t\t\tThis process Insert, Update, or Delete the rows accordingly on an hourly basis.\n\n" +
     "Technical Description:\n\n" +
     "SSIS Package Name:\tLCGMS_Supertable_Update_Process_LC1P1\n" +
     "SQL Table (Input):\tSupertable.dbo.Supertable_Storage_Staging\n" +
     "DB2 Table (Output):\tLC1U1.LOCATION_SUPERTBL1\n" +
     "SQL Stored Procs:\tsp_populate_supertmp\n" +
     "SQL View:\t\tNone\n\n" +
     "Schedule to run:\tMultiple times per day (@ 9:00 AM, 11 AM, 2:00 PM, 6:30 PM, & 11 on Weekdays)\n\n" +
     "Dependencies:\t\tIt handles logically\n\n" +
     "Total Transaction: {0}\n" +
     "Inserted: \t   {1}\n" +
     "Updated: \t   {2}\n" +
     "Deleted: \t   {3}\n\n" +
     "Insert List:\n{4}\n\n" +
     "Update List:\n{5}\n\n" +
     "Delete List:\n{6}\nEND\n\n" +
     "Please get in touch with the LCGMS team in case of questions or concerns.\n\n" +
     "Thanks,\nLCGMS team",
     totalTransaction, totalInsertedRows, totalUpdatedRows, deletedRows, sqlstatement2, sqlstatement3, sqlstatement4);

                    Dts.TaskResult = (int)ScriptResults.Success;
                }

            }
            catch (Exception ex)
            {
                // Handle exception
                Dts.Events.FireError(0, "Script Task Error", ex.Message + "\n" + ex.StackTrace, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

        }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };


    }
}

问题原因分析及解决建议

核心原因

错误提示中无法打开源文件'c:\Windows\Temp\.NETFramework,Version=v4.7.AssemblyAttributes.cs'（拒绝访问）是关键，说明SQL Agent服务账户对系统临时目录没有足够权限，导致脚本任务运行时无法生成或访问编译所需的临时文件。

另外，脚本任务的二进制代码未找到，通常是因为部署时未正确包含已编译的脚本程序集，或者运行时无法重新编译脚本。

解决步骤

调整SQL Agent服务账户权限
- 给SQL Agent服务账户授予c:\Windows\Temp目录的读取、写入、修改权限。
- 若使用默认的NT SERVICE\SQLSERVERAGENT账户，需手动添加该账户到Temp目录的权限列表中。
确保脚本任务已预编译并正确部署
- 在Visual Studio中打开每个脚本任务，点击编辑脚本，确认项目编译成功（无编译错误），然后保存脚本任务和包。
- 部署时选择项目部署模型，确保部署包包含脚本任务的已编译程序集（而非仅源代码）。
检查.NET Framework版本兼容性
- 确认SQL Server 2019服务器上已安装.NET Framework 4.7或更高版本，与脚本任务的目标框架版本一致。
- 若服务器上.NET版本不匹配，安装对应版本的.NET Framework。
尝试修改脚本任务的临时目录
- 通过修改注册表或配置文件，让SSIS脚本任务使用自定义临时目录（而非系统Temp），并确保SQL Agent账户对该目录有完全权限。

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

SQL Server 2019部署时SSIS脚本任务失败问题求助

问题现象 #

相关脚本代码 #

Get Tables for Report脚本 #

Script Task脚本 #

问题原因分析及解决建议 #

核心原因 #

解决步骤 #