SSIS中C#脚本无法连接数据库问题求助

使用SSIS 2019与SSMS数据库，在SSIS的C#脚本任务中无法连接数据库。已定位到以下代码行始终返回null，且确认传入变量的值无误，附上错误信息及代码，求解决思路：

SqlConnection conn = (SqlConnection)(Dts.Connections[Dts.Variables["CONNECTION_MANAGER"].Value.ToString()].AcquireConnection(Dts.Transaction) as SqlConnection);

错误信息 #

SSIS package "C:\Users\shale\source\repos\Integration Services Project3\Integration 
Services Project3\CompareCoulmnsNames.dtsx" starting.
Error: 0x0 at Script Task, Exception from Script Task: ExecuteNonQuery: Connection 
property has not been initialized.
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 
completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, 
Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at ST_0802b46cfde44ff192edd969586bd834.ScriptMain.Main()
Warning: 0x80019002 at Script Task: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  
The Execution method succeeded, but the number of errors raised (1) reached the maximum 
allowed (1); resulting in failure. This occurs when the number of errors reaches the 
number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Task failed: Script Task
Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code 
DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of 
errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs 
when the number of errors reaches the number specified in MaximumErrorCount. Change the 
MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Package1: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The 
Execution method succeeded, but the number of errors raised (1) reached the maximum 
allowed (1); resulting in failure. This occurs when the number of errors reaches the 
number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "C:\Users\shale\source\repos\Integration Services Project3\Integration 
Services Project3\CompareCoulmnsNames.dtsx" finished: Failure.
The program '[3020] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

完整代码 #

public void Main()
{
    
    string strPath1 = Dts.Variables["File1_PATH"].Value.ToString();
    if (File.Exists(strPath1))
    {
        string[] file1 = File.ReadAllLines(strPath1);
        Dts.Variables["COLUMN_NAMES"].Value = file1[0];
    }
    string strArr1 = Dts.Variables["COLUMN_NAMES"].Value.ToString();

    string strPath2 = Dts.Variables["FilesPath"].Value.ToString();
    string[] strArr2 = File.ReadAllLines(strPath2);

    
    DateTime loadingDateTime = new DateTime();
    string fileName = Path.GetFileName(strPath2);
    int records = 0;
    string status = "";
   
    if (Enumerable.SequenceEqual(strArr1, strArr2[0]))
    {
        Dts.Variables["BooleanVar"].Value = true;
        try
        {

            int counter = 0;
            
            string TableName = Dts.Variables["TABLE_NAME"].Value.ToString();
            SqlConnection conn = (SqlConnection)(Dts.Connections[Dts.Variables["CONNECTION_MANAGER"].Value.ToString()].AcquireConnection(Dts.Transaction) as SqlConnection);
            // Check the connection state
            if (conn == null) { MessageBox.Show("null");  }
            foreach (string i in strArr2)
            {
                
                if (counter != 0)
                {  
                    string query = "Insert into " + TableName + " values('" + i.Replace(",", "','").Replace("   ", "','"). + "')";
                    
                    SqlCommand SQLCommand = new SqlCommand(query, conn);
                    SQLCommand.ExecuteNonQuery();
                    
                    
                }
                counter++;
            }
            //change the current file data
            loadingDateTime = DateTime.Now;
            records = counter - 1;
            status = "Processed";

            Dts.TaskResult = (int)ScriptResults.Success;
            conn.Close();
        }
        catch (Exception ex)
        {
            Dts.Events.FireError(0, "Exception from Script Task", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }
    else
    {
        Dts.Variables["BooleanVar"].Value = false;
        status = "Wrong";
    }
    //insert the file data to etl_report
    string dest = Dts.Variables["DEST_PATH"].Value.ToString();
    string data = fileName + "," + status + "," + records.ToString() + "," + loadingDateTime.ToString("dd/MM/yyyy hh:mm tt");

    if (!File.Exists(dest))
    {
        string clientHeader = "Filename" + "," + "Status" + "," + "Records" + "," + "LoadingDateTime" + Environment.NewLine;
        File.WriteAllText(dest, clientHeader);
    }
    File.AppendAllText(dest, data + Environment.NewLine);

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

解决思路 #

• 连接管理器类型不匹配：如果你的连接管理器是OLE DB类型，AcquireConnection返回的是OleDbConnection对象，无法直接转换为SqlConnection。解决方法：
  1. 将连接管理器改为ADO.NET连接管理器（类型为SqlClient），此时AcquireConnection可直接返回SqlConnection。
  2. 若保留OLE DB连接，可提取连接字符串重新创建SqlConnection：

     ConnectionManager cm = Dts.Connections[Dts.Variables["CONNECTION_MANAGER"].Value.ToString()];
     string connStr = cm.ConnectionString;
     // 针对OLE DB连接字符串，转换为SqlClient格式，比如移除Provider相关配置
     connStr = connStr.Replace("Provider=SQLOLEDB.1;", "").Trim();
     SqlConnection conn = new SqlConnection(connStr);
     conn.Open();
     

• 事务参数问题：如果当前没有运行事务，传递Dts.Transaction会导致获取连接失败，改为传递null：

  var connObj = Dts.Connections[Dts.Variables["CONNECTION_MANAGER"].Value.ToString()].AcquireConnection(null);
  SqlConnection conn = connObj as SqlConnection;
  

• 验证连接配置：确认连接管理器的名称与CONNECTION_MANAGER变量的值完全一致，且连接管理器测试连接成功，权限足够访问目标数据库。
• 修复代码语法错误：insert语句中存在多余的点（. + "')"），会导致运行错误，需改为：

  string query = "Insert into " + TableName + " values('" + i.Replace(",", "','").Replace("   ", "','") + "')";
  

• 替换调试方式：SSIS脚本任务中不要用MessageBox.Show，改用Dts.Events.FireInformation输出调试信息：

  bool fireAgain = true;
  if (conn == null) { Dts.Events.FireInformation(0, "Script Task", "Connection is null", string.Empty, 0, ref fireAgain); }
  

内容的提问来源于stack exchange，提问作者Shalev Levi Sagzan