将SQL Server错误累积到单个变量中 [英] Accumulating SQL Server Errors into a Single Variable
问题描述
大家好!
在某些SQL Server错误上生成了多条消息,但是一个简单的分配如@ [User :: ErrorMessage] = @ [System :: ErrorDescription]仅检索最新消息。 并非所有邮件的行为都与此类似,许多邮件会返回可以对其执行的
重要邮件。
On certain SQL Server errors there are multiple messages generated, but a simple assignment like @[User::ErrorMessage] = @[System::ErrorDescription] only retrieves the latest message. Not all messages behave like this and many return the important message that can be acted upon.
如何检索所有邮件,尤其是第一个,最常见的是实际问题。 我对SSISDB或DTS日志记录"workarounds"不感兴趣,只是如何分配给变量。
How do I retrieve all of them, especially the first one, which most often is the actual problem. I am NOT interested in SSISDB, or DTS logging "workarounds", just how to assign to a variable.
OnError中的表达式,如"@ [User :: ErrorMessage] = @ [User :: ErrorMessage] +"。 " + @ [System :: ErrorDescription]"工作&NBSP?;我暂时无法测试。
Would an expression in OnError, like "@[User::ErrorMessage] = @[User::ErrorMessage] + ". " + @[System::ErrorDescription]" work? I cannot test this at the moment.
谢谢!
推荐答案
您好,SQLRaff,
Hi SQLRaff,
我是否想知道您是否要从变量中检索错误消息?
May I know if you want to retrieve error messages from variable?
如果是这样,我们可以在< OnError 包的事件处理程序。
If so, we could add a Script Task on the OnError event handler of the package.
在脚本任务中,我们可以将 System :: ErrorDescription 添加到 ReadOnlyVariables 然后点击 编辑脚本 。
In Script Task, we could add System::ErrorDescription to ReadOnlyVariables and then click Edit Script .
public void Main()
{
//a.Show the value of variable.
MessageBox.Show("ErrorDescription: " + Dts.Variables["System::ErrorDescription"].Value.ToString());
//
//b.Write the value of variable to .txt file.
string fileName = "C:/SSIS/Error.txt"; //File path.
string textToAdd = "ErrorDescription: " + Dts.Variables["System::ErrorDescription"].Value.ToString();
using (System.IO.StreamWriter writer = new System.IO.StreamWriter(fileName, true))
{
writer.Write(textToAdd);
}
//
Dts.TaskResult = (int)ScriptResults.Success;
}
结果:
一个。 MessageBox.Show
b。 Error.txt
最好的问候,
Mona Lv
这篇关于将SQL Server错误累积到单个变量中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!