ErrorColumn value does not exist as line ID

Question

During insertion into the target table, any error that occurs is redirected to the Errors table, where we can see ErrorCodeand ErrorColumn. The problem is that we got a value in ErrorColumnwhich does not exist anywhere in the package. Namely, there is not a single column with LineageIDequal ErrorColumn.

Later, by turning on the NULL entry in each individual column, one by one, I discovered which column caused this problem. When I analyzed the column inside the data flow task, it did not have LineageIDwhat was reported in ErrorColumn. For example, the message ErrorColumnwas 413, but LineageIDequal to 84 during the first merge, and it changes during various sorts. Regardless, it never becomes 413. This identifier ErrorColumn(413) does not exist at all, but it is reported in the error redirection insertion to the original source (error table).

I checked a lot of sites, but they all suggested listing through ComponenteMetaData.InputCollectionor ComponentMetaData.OutputCollectiona script task, and then listing by columns to find LineageID, but that was unsuccessful.

As I said, I solved the problem, but since we are in the early stages of the ETL process, this can happen in some other cases. How can this problem be solved?

Answer 1

I copy my answer so that we can get authoritative Q&A on the site

What is an easy way to find the column name from Lineageid in SSIS .

I remember that it can’t be so difficult, I can write a script to redirect errors to find the column name from the input collection.

string badColumn = this.ComponentMetaData.InputCollection[Row.ErrorColumn].Name;

, , . , ErrorColumn , . , , , . , .

, , - . Derived (LookAtMe) . , 73. script, 73 , , . LineageID 73 - LookAtMe, LookAtMe , .

XML, , , exitColumn 73 - LookAtme.

<outputColumn id="73" name="LookAtMe" description="" lineageId="73" precision="0" scale="0" length="0" dataType="i4" codePage="0" sortKeyPosition="0" comparisonFlags="0" specialFlags="0" errorOrTruncationOperation="Computation" errorRowDisposition="RedirectRow" truncationRowDisposition="RedirectRow" externalMetadataColumnId="0" mappedColumnId="0"><properties>

, , , , . , Union All - . , , ... , , , , .

, / .

2012

SSIS 2012 LineageID . , , . XML-

 <outputColumn
 refId="Package\DFT Generate Errors\DER Divide by SomeNumber.Outputs[Derived Column Output].Columns[LookAtMe]"
 dataType="i4"
 errorOrTruncationOperation="Computation"
 errorRowDisposition="RedirectRow"
 lineageId="Package\DFT Generate Errors\DER Divide by SomeNumber.Outputs[Derived Column Output].Columns[LookAtMe]"
 name="LookAtMe"
 truncationRowDisposition="FailComponent">

ErrorColumn , lineageid. 6. XML-, 6 . .

, , , , , "". Error Columns.

Answer 2

, , SSIS. ,

• ( )
• Script
• BIML

.

.

• 2 , execsObj lineageIds
• Script , ReadWrite
• Script

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

4. Script ReadOnly lineageIds .

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

Answer 3

CSV ( ) " ". " "

( (!) ). " "

jabber, , , , , 2010, 2012 , , .

Answer 4

, , , - !? , Microsoft GitHub Apple, Google att idk, , - , , , , - , , , WiFi-. Xbox, Microsoft, , . , ! - , , , , 4 iPhone . , , , 2 , ! , , , , . , . , Facebook 2010, , , FarmVille! . , ?!? iPhone WoSign ! , , Facebook, eBay - , Pay Pal eBay . 2 iPhone. gsuit. ! Facebook. . ? , , ? iPhone , , ?? . 2 6 . 7 , - 9? . VPN , ! , , , , ? ! ! 4-5 ! . ROKU iPhone Apple TV. Apple . - , Microsoft , ? WiFi, , ! MAC- 3- 1- 3-! ! - , , ! , - , , , ? ! ? , -, , ! TX CA , URL- -, PUK- . ! , , - ? ROKU, GNU, , . Creative Commons Apple, , !! , ELMU ELMA? Idk , - , ??!? . 5732630314 , , - anC+ * -