无法在C# SSIS中打开与数据库的连接。

huangapple go评论66阅读模式
英文:

Can't open connection with database in C# SSIS

问题

I'm working with SSIS 2019 and with SSMS as my database. I wrote a script in SSIS in C# and it can't for some reason make a connection to my database. I manage to see what's the problematic line and its the line with ** (see in code section). This line just bring me null no matter what. The variables I gave this code are right and I double checked that. its something small that I'm missing.

If you have any insight, I Would be glad to hear.

Error raised:

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

Here's the code:

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;
}
英文:

I'm working with SSIS 2019 and with SSMS as my database. I wrote a script in SSIS in C# and it can't for some reason make a connection to my database.
I manage to see what's the problematic line and its the line with ** (see in code section). This line just bring me null no matter what. The variables I gave this code are right and I double checked that. its something small that I'm missing.

If you have any insight, I Would be glad to hear.

Error raised:

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

Here's the code:

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

答案1

得分: 1

你必须使用这段代码
我的代码(正确的代码):

SqlConnection conn = new SqlConnection(Your connectionstring);
// 例如连接字符串:"Password=123;Persist Security Info=True;User ID=sad;Initial Catalog=TestDB1;Data Source=.;TrustServerCertificate=True"
// 检查连接状态
if (conn.State == ConnectionState.Closed) conn.Open();

你的代码:

SqlConnection conn = (SqlConnection)(Dts.Connections[Dts.Variables["CONNECTION_MANAGER"].Value.ToString()].AcquireConnection(Dts.Transaction) as SqlConnection);
// 检查连接状态
if (conn == null) { MessageBox.Show("null"); }
英文:

you must use this code
my code(correct Code):

  SqlConnection conn = new SqlConnection(Your connectionstring);
//example connectionstring :"Password=123;Persist Security Info=True;User ID=sad;Initial Catalog=TestDB1;Data Source=.;TrustServerCertificate=True"
// Check the connection state                  
if (conn.State == ConnectionState.Closed) conn.Open(); 

your code


**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");  }

答案2

得分: 0

你需要在以下行之后打开你的连接:

// 检查连接状态
if (conn == null)
{
MessageBox.Show("null");
}
else
{
conn.Open();
}

英文:

you need to open your connection after the line

// Check the connection state
if (conn == null)
{ 
MessageBox.Show("null");      
}
else
{
conn.Open();
}

huangapple
  • 本文由 发表于 2023年5月7日 05:16:26
  • 转载请务必保留本文链接:https://go.coder-hub.com/76191182.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定