SSIS Data Flow Error Handling With Description and Column Name
Without SQL 2016's modified API, it's really difficult to find the error column name using just a script task and script component. Here's a simple solution.
Join the DZone community and get the full member experience.
Join For FreeFinding an error description is a simple task involving one line of code — but finding the error column name is not so simple. Microsoft made it easy with SQL 2016's modified API. But with prior versions, it's a really difficult job to find the error column name using just a script task and script component.
This article provides a simple step-by-step solution to find the error description and error column name.
Scenario
We are trying to insert records from a flat file to our SQL table. The flat file has two columns: roll
and name
. Sometimes, the name
column contains huge text and isn't supported by our table object (the size of the name
column the in table object is Varchar(50)). This generates an error. We are trying to display the error description and the column name that generated the error.
Control/Data Flow Details
Control Flow: Get Lineage ID Task
This script task is used to grade the lineage ID at runtime and save it as a text file.
Create a text file:
C:\temp\write.txt
.Create two package-level variables named
User::execsObj
andUser::lineageIds
as the object types. Pass them as the Read-Write variables of the script task.In the script task, add an assembly named
Microsoft.SqlServer.DTSPipelineWrap
:71using System;
2using System.Data;
3using Microsoft.SqlServer.Dts.Runtime;
4using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
5using System.Windows.Forms;
6using System.Collections.Generic;
7using System.IO;
Script task code in C#:
651{
2// Grab the executables so we have to something to iterate over, and initialize our lineageIDs list
3// Why the executables? Well, SSIS won't let us store a reference to the Package itself...
4Dts.Variables["User::execsObj"].Value =(Package)Dts.Variables["User::execsObj"].Parent).Executables;
5
6Dts.Variables["User::lineageIds"].Value = new Dictionary<int, string>();
7lineageIds = (Dictionary<int, string>)Dts.Variables["User::lineageIds"].Value;
8Executables execs = (Executables)Dts.Variables["User::execsObj"].Value;
9
10ReadExecutables(execs);
11
12// Just proof of concept to see the results before you dedicate your time to the solution
13// Delete this code in your actual implementation
14using (StreamWriter writetext = new StreamWriter(@"C:\temp\write.txt", true))
15{
16foreach (var kvp in lineageIds)
17writetext.WriteLine(kvp.Key + " : " + kvp.Value);
18}
19Dts.TaskResult = (int)ScriptResults.Success;
20}
21
2223
24private void ReadExecutables(Executables executables)
25{
26foreach (Executable pkgExecutable in executables)
27{
28if (object.ReferenceEquals(pkgExecutable.GetType(),
29typeof(Microsoft.SqlServer.Dts.Runtime.TaskHost)))
30{
31TaskHost pkgExecTaskHost = (TaskHost)pkgExecutable;
32if (pkgExecTaskHost.CreationName.StartsWith("SSIS.Pipeline"))
33{
34ProcessDataFlowTask(pkgExecTaskHost);
35}
36}
37else if (object.ReferenceEquals(pkgExecutable.GetType(),
38typeof(Microsoft.SqlServer.Dts.Runtime.ForEachLoop)))
39{
40// Recurse into FELCs
41ReadExecutables(((ForEachLoop)pkgExecutable).Executables);
42}
43}
44}
45
46private void ProcessDataFlowTask(TaskHost currentDataFlowTask)
47{
48MainPipe currentDataFlow = (MainPipe)currentDataFlowTask.InnerObject;
49
50foreach (IDTSComponentMetaData100 currentComponent in
51currentDataFlow.ComponentMetaDataCollection)
52{
53// Get the inputs in the component.
54foreach (IDTSInput100 currentInput in currentComponent.InputCollection)
55foreach (IDTSInputColumn100 currentInputColumn in
56currentInput.InputColumnCollection)
57lineageIds.Add(currentInputColumn.ID, currentInputColumn.Name);
58
59// Get the outputs in the component.
60foreach (IDTSOutput100 currentOutput in currentComponent.OutputCollection)
61foreach (IDTSOutputColumn100 currentoutputColumn in
62currentOutput.OutputColumnCollection)
63lineageIds.Add(currentoutputColumn.ID, currentoutputColumn.Name);
64}
65}
Adding a Script Component to the Data Flow Task
Now, add a script component to the data flow task.
Pass the variable named
User::lineageIds
as the Read variable.Script the component input columns as
ErrorCode
andErrorColumn
.Script the component output column as
ErrorDescription
andErrorColumnDescription
with aDT_STR
data type.Script task code in C#:51using System;
2using System.Data;
3using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
4using Microsoft.SqlServer.Dts.Runtime.Wrapper;
5using System.Collections.Generic;
x1public override void Input0_ProcessInputRow(Input0Buffer Row)
2{
3Dictionary<int, string> lineageIds = (Dictionary<int, string>)Variables.lineageIds;
4
5int? colNum = Row.ErrorColumn;
6if (colNum.HasValue && (lineageIds != null))
7{
8if (lineageIds.ContainsKey(colNum.Value))
9Row.ErrorColumnDescription = lineageIds[colNum.Value];
10
11else
12Row.ErrorColumnDescription = "Row error";
13}
14Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
15}
I hope you found this article informative!
Published at DZone with permission of Joydeep Das, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments