当Data Flow Task中报错时，怎么得到ErrorColumn Name

在你的脚本任务控件中插入如下代码：
Dictionary<int, string> lineageIds = null;

public void Main()
{
    // Grab the executables so we have to something to iterate over, and initialize our lineageIDs list
    // Why the executables?  Well, SSIS won't let us store a reference to the Package itself...
    Dts.Variables["User::execsObj"].Value = ((Package)Dts.Variables["User::execsObj"].Parent).Executables;
    Dts.Variables["User::lineageIds"].Value = new Dictionary<int, string>();
    lineageIds = (Dictionary<int, string>)Dts.Variables["User::lineageIds"].Value;
    Executables execs = (Executables)Dts.Variables["User::execsObj"].Value;

    ReadExecutables(execs);

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

private void ReadExecutables(Executables executables)
{
    foreach (Executable pkgExecutable in executables)
    {
        if (object.ReferenceEquals(pkgExecutable.GetType(), typeof(Microsoft.SqlServer.Dts.Runtime.TaskHost)))
        {
            TaskHost pkgExecTaskHost = (TaskHost)pkgExecutable;
            if (pkgExecTaskHost.CreationName.StartsWith("SSIS.Pipeline"))
            {
                ProcessDataFlowTask(pkgExecTaskHost);
            }
        }
        else if (object.ReferenceEquals(pkgExecutable.GetType(), typeof(Microsoft.SqlServer.Dts.Runtime.ForEachLoop)))
        {
            // Recurse into FELCs
            ReadExecutables(((ForEachLoop)pkgExecutable).Executables);
        }
    }
}

private void ProcessDataFlowTask(TaskHost currentDataFlowTask)
{
    MainPipe currentDataFlow = (MainPipe)currentDataFlowTask.InnerObject;
    foreach (IDTSComponentMetaData100 currentComponent in currentDataFlow.ComponentMetaDataCollection)
    {
        // Get the inputs in the component.
        foreach (IDTSInput100 currentInput in currentComponent.InputCollection)
            foreach (IDTSInputColumn100 currentInputColumn in currentInput.InputColumnCollection)
                lineageIds.Add(currentInputColumn.ID, currentInputColumn.Name);

        // Get the outputs in the component.
        foreach (IDTSOutput100 currentOutput in currentComponent.OutputCollection)
            foreach (IDTSOutputColumn100 currentoutputColumn in currentOutput.OutputColumnCollection)
                lineageIds.Add(currentoutputColumn.ID, currentoutputColumn.Name);
    }
}
 
在你的数据流任务中创建一个Script Component，并添加如下代码：
public override void Input0_ProcessInputRow(Input0Buffer Row)
  {
      Dictionary<int, string> lineageIds = (Dictionary<int, string>)Variables.lineageIds;

      int? colNum = Row.ErrorColumn;
      if (colNum.HasValue && (lineageIds != null))
      {
          if (lineageIds.ContainsKey(colNum.Value))
              Row.ErrorColumnName = lineageIds[colNum.Value];

          else
              Row.ErrorColumnName = "Row error";
      }
      Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
  }
 
-----------------------------------------------------
如果你是使用C#来执行一个SSIS，则可以使用C#代码来获取：
  private Dictionary<long, string> GetLineageIdAndColumnMapping(string SSISFilename)
{
    XDocument xdoc = XDocument.Load(SSISFilename);
    Dictionary<long, string> LineageColumn = new Dictionary<long, string>();

    var LineageNodes = from Nodes in xdoc.Descendants()
                       where Nodes.Attribute("lineageId") != null &&
                             Nodes.Attribute("lineageId").Value != String.Empty &&
                             Nodes.Attribute("name") != null &&
                             Nodes.Attribute("name").Value != String.Empty
                       select new
                       {
                           LineageId = Convert.ToInt64(Nodes.Attribute("lineageId").Value),
                           ColumnName = Nodes.Attribute("name").Value
                       };

    foreach (var Item in LineageNodes)
        LineageColumn.Add(Item.LineageId, Item.ColumnName);

    return LineageColumn;
}
 
 
 
 
 

SSIS2008R2可以参考如下链接:
http://www.techbrothersit.com/2013/12/ssis-how-to-get-error-column-name-in.html
 

你好，Variables["User::execsObj"]是什么变量？
是自己定义的变量名叫：execsObj
Value是”PackageName“吗？

这个代码好熟悉，我用的好像就是这种方式，2008里面的，但是2012版本好像解析起来就很麻烦。
@Bob, 赞！

如果你是使用C#来执行一个SSIS，则可以使用C#代码来获取：

  private Dictionary<long, string> GetLineageIdAndColumnMapping(string SSISFilename)

{

    XDocument xdoc = XDocument.Load(SSISFilename);

    Dictionary<long, string> LineageColumn = new Dictionary<long, string>();



    var LineageNodes = from Nodes in xdoc.Descendants()

                       where Nodes.Attribute("lineageId") != null &&

                             Nodes.Attribute("lineageId").Value != String.Empty &&

                             Nodes.Attribute("name") != null &&

                             Nodes.Attribute("name").Value != String.Empty

                       select new

                       {

                           LineageId = Convert.ToInt64(Nodes.Attribute("lineageId").Value),

                           ColumnName = Nodes.Attribute("name").Value

                       };



    foreach (var Item in LineageNodes)

        LineageColumn.Add(Item.LineageId, Item.ColumnName);



    return LineageColumn;

}

 

据说在 SQL SERVER 2016 里面，好像是可以直接取到 Column Name，拭目以待！