HI, I would like to get error column name from o/p redirected error flow. Currently we have error column which gives error column ID. But I need the exact column name. I know there are custom components available in codeplex. But I would like to do without using any external components. Is there any way? Appreciate your help. Thanks.Porus

I wrote this script before, actually you need to process your package, your package is an XML format file. You can get the column name from your package. Hope it helps! Vote if it's helpful! Simon MSN:simonlv@live.cn Skype:simonlvpin

For example, if your output error column ID is 330, You could open your package .dtsx with an XML editor, and search this ID '330'. This ID is unique one in your package file. What you need to do is use a script to read your package file as an XML foramt and loop the elements to get the name by ID. I'm not sure if I can find my codes I wrote before, I will share with you if I can . <inputs> <input id="326" name="Destination Input" description="" hasSideEffects="true" dangling="false" errorOrTruncationOperation="Insert" errorRowDisposition="FailComponent" truncationRowDisposition="NotUsed"><inputColumns> <inputColumn id="336" name="" description="" lineageId="299" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="330" mappedColumnId="0"/> <inputColumn id="337" name="" description="" lineageId="302" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="331" mappedColumnId="0"/> <inputColumn id="338" name="" description="" lineageId="305" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="332" mappedColumnId="0"/> <inputColumn id="339" name="" description="" lineageId="308" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="333" mappedColumnId="0"/> <inputColumn id="340" name="" description="" lineageId="311" usageType="readOnly" errorOrTruncationOperation="" errorRowDisposition="NotUsed" truncationRowDisposition="NotUsed" externalMetadataColumnId="334" mappedColumnId="0"/> </inputColumns><externalMetadataColumns isUsed="True"> <externalMetadataColumn id="330" name="EmployeeID" description="" precision="0" scale="0" length="0" dataType="i4" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="331" name="RateChangeDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="332" name="Rate" description="" precision="0" scale="4" length="0" dataType="cy" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="333" name="PayFrequency" description="" precision="0" scale="0" length="0" dataType="ui1" codePage="0" mappedColumnId="0"/> <externalMetadataColumn id="334" name="ModifiedDate" description="" precision="0" scale="0" length="0" dataType="dbTimeStamp" codePage="0" mappedColumnId="0"/></externalMetadataColumns></input> </inputs> Hope it helps! Vote if it's helpful! Simon MSN:simonlv@live.cn Skype:simonlvpin

Thanks Simon. I do aware about this XML one. But not sure how to access. Let me know if you get that code. Thanks.Porus

You need to build a script and pass variables- package path, column ID, also need a variable to save the column name. Here's the core codes I used before, you need to read it and modify some codes. /* Microsoft SQL Server Integration Services Script Component * Write scripts using Microsoft Visual C# 2008. * ScriptMain is the entry point class of the script.*/ using System; using System.Data; using Microsoft.SqlServer.Dts.Pipeline.Wrapper; using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Collections.Generic; using System.Xml; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute] public class ScriptMain : UserComponent { Dictionary<long, string> dic; string packageName; public override void PreExecute() { base.PreExecute(); /* Add your code here for preprocessing or remove if not needed */ PostExecute(); } public override void PostExecute() { base.PostExecute(); /* Add your code here for postprocessing or remove if not needed You can set read/write variables here, for example: Variables.MyIntVar = 100 */ try { // Get the package name from SSIS Variables packageName = Variables.SSPackageFileName; //errorName = Variables.ZZErrorName; } catch (Exception e) { //MessageBox.Show("123"); } } public override void Input0_ProcessInputRow(Input0Buffer Row) { /* Add your code here */ //PostExecute(); Row.ErrorMessage = "Error Reason - "+this.ComponentMetaData.GetErrorDescription(Row.ErrorCode)+""; //Row.OutColumn = Row.FlatFileSourceErrorOutputColumn.ToString(); //Console.WriteLine("Error Code - " +Row.ErrorCode+", Error Column - "+Row.ErrorColumn); //MessageBox.Show(" Row.ErrorCode - " + Row.ErrorCode); //MessageBox.Show("packname" + packageName); dic = GetLineageIdAndColumnMapping(packageName); Dictionary<long, string> dic = GetLineageIdAndColumnMapping("N:\\Test.dtsx"); foreach (KeyValuePair<long, string> keyValue in dic) { //MessageBox.Show("Key " + keyValue.Key); if (keyValue.Key == Row.ErrorColumn) { Row.ErrorColumnName ="Error Column - "+ keyValue.Value+""; break; } } } private Dictionary<long, string> GetLineageIdAndColumnMapping(string SSISFilename) { XmlDocument doc = new XmlDocument(); doc.Load(SSISFilename); Dictionary<long, string> LineageColumn = new Dictionary<long, string>(); foreach (XmlNode node in doc.SelectNodes("//*[@lineageId != '' and @name != '']")) { long key = Convert.ToInt64(node.Attributes["lineageId"].Value); if (!LineageColumn.ContainsKey(key)) { LineageColumn.Add(Convert.ToInt64(node.Attributes["lineageId"].Value), node.Attributes["name"].Value); } } return LineageColumn; } } This method - private Dictionary<long, string> GetLineageIdAndColumnMapping(string SSISFilename) will give you a Dictionary back with key and value, you could use MessageBox to show the values. I'm sure you can get the column name by column ID if you can understand these codes and have a little modification. Good luck! Hope it helps! Vote if it's helpful! Simon MSN:simonlv@live.cn Skype:simonlvpin