Skip to content

Instantly share code, notes, and snippets.

@MaciejLisCK
Last active December 17, 2015 12:48
Show Gist options
  • Save MaciejLisCK/a2840cdfcedda88d83a0 to your computer and use it in GitHub Desktop.
Save MaciejLisCK/a2840cdfcedda88d83a0 to your computer and use it in GitHub Desktop.
SSIS Custom Component Error Output
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
[DtsPipelineComponent(DisplayName = "Test Component", ComponentType = ComponentType.Transform,
IconResource = "Components.Resources.DefaultIcon.ico")]
public class TestComponent : PipelineComponentBase
{
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.UsesDispositions = true;
var mainInput = AddInput(MainInputName);
mainInput.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;
mainInput.ErrorOrTruncationOperation = "A string...";
var mainOutput = AddOutput(MainOutputName);
mainOutput.SynchronousInputID = mainInput.ID;
mainOutput.ExclusionGroup = 1;
var mainErrorOutput = AddErrorOutput(MainErrorOutputName, MainOutputName);
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
base.ProcessInput(inputID, buffer);
Random rand = new Random();
while (buffer.NextRow())
{
var randomNumber = rand.Next(2);
if (randomNumber == 0)
{
var output = Outputs.Single(o => o.Name == MainOutputName);
buffer.DirectRow(output.ID);
}
else
{
var errorOutput = Outputs.Single(o => o.Name == MainErrorOutputName);
buffer.DirectErrorRow(errorOutput.ID, 1, 0);
}
}
}
}
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
public abstract class PipelineComponentBase : PipelineComponent
{
protected const int MaxWstrDataTypeLength = 4000;
protected const string MainInputName = "Input";
protected const string MainOutputName = "Output";
protected const string MainErrorOutputName = "ErrorOutput";
public Dictionary<string, PipelineBuffer> OutputBuffersDictionary { get; protected set; }
public IList<IDTSCustomProperty100> CustomProperties
{
get
{
var customPropertiesCollection = ComponentMetaData.CustomPropertyCollection;
var customProperties = customPropertiesCollection.Cast<IDTSCustomProperty100>();
return customProperties.ToList();
}
}
public IList<IDTSOutput100> Outputs
{
get
{
var outputCollections = ComponentMetaData.OutputCollection;
var outputs = outputCollections.Cast<IDTSOutput100>();
return outputs.ToList();
}
}
public IList<IDTSInput100> Inputs
{
get
{
var inputCollection = ComponentMetaData.InputCollection;
var inputs = inputCollection.Cast<IDTSInput100>();
return inputs.ToList();
}
}
public PipelineComponentBase()
{
CultureInfo.DefaultThreadCurrentCulture = Culture.Default;
}
public override void PrepareForExecute()
{
base.PrepareForExecute();
CultureInfo.DefaultThreadCurrentCulture = Culture.Default;
}
protected IDTSCustomProperty100 AddCustomProperty(string name)
{
return AddCustomProperty(name, String.Empty, String.Empty);
}
protected IDTSCustomProperty100 AddCustomProperty(string name, string description)
{
return AddCustomProperty(name, description, String.Empty);
}
protected IDTSCustomProperty100 AddCustomProperty(string name, string description, string initialValue)
{
var customProperty = ComponentMetaData.CustomPropertyCollection.New();
customProperty.Name = name;
customProperty.Description = description;
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
customProperty.Value = initialValue;
return customProperty;
}
protected string GetCustomPropertyValue(string customPropertyName)
{
var customProperty = CustomProperties.Single(cp => cp.Name == customPropertyName);
return customProperty.Value;
}
protected IDTSInput100 AddInput(string name)
{
var input = ComponentMetaData.InputCollection.New();
input.Name = name;
return input;
}
protected IDTSOutput100 AddOutput(string name)
{
var output = ComponentMetaData.OutputCollection.New();
output.Name = name;
return output;
}
protected IDTSOutput100 AddErrorOutput(string errorOutputName)
{
var output = AddOutput(errorOutputName);
output.IsErrorOut = true;
return output;
}
protected IDTSOutput100 AddErrorOutput(string errorOutputName, string synchronousOutputName)
{
var synchronousOutput = Outputs.Single(i => i.Name == synchronousOutputName);
var errorOutput = AddErrorOutput(errorOutputName);
errorOutput.SynchronousInputID = synchronousOutput.SynchronousInputID;
errorOutput.ExclusionGroup = synchronousOutput.ExclusionGroup;
return errorOutput;
}
protected IDTSOutputColumn100 AddOutputColumn(IDTSOutput100 output, IDTSInputColumn100 inputColumn)
{
var outputColumnsCollection = output.OutputColumnCollection;
var outputColumn = outputColumnsCollection.New();
outputColumn.Name = inputColumn.Name;
outputColumn.SetDataTypeProperties(inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
return outputColumn;
}
protected IDTSOutputColumn100 AddStringStreamOutputColumn(IDTSOutput100 output, string columnName)
{
var outputColumnsCollection = output.OutputColumnCollection;
var outputColumn = outputColumnsCollection.New();
outputColumn.Name = columnName;
outputColumn.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0);
return outputColumn;
}
protected IDTSOutputColumn100 AddStringStreamOutputColumn(string outputName, string columnName)
{
var output = Outputs.Single(o => o.Name == outputName);
return AddStringStreamOutputColumn(output, columnName);
}
protected IDTSOutputColumn100 AddStringOutputColumn(IDTSOutput100 output, string columnName, int length)
{
var outputColumnsCollection = output.OutputColumnCollection;
var outputColumn = outputColumnsCollection.New();
outputColumn.Name = columnName;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, length, 0, 0, 0);
return outputColumn;
}
protected IDTSOutputColumn100 AddStringOutputColumn(string outputName, string columnName, int length)
{
var output = Outputs.Single(o => o.Name == outputName);
return AddStringOutputColumn(output, columnName, length);
}
protected IDTSOutputColumn100 AddOutputColumn<T>(string outputName, string columnName, int length = 0)
{
var outputColumnsCollection = Outputs.Single(o => o.Name == outputName).OutputColumnCollection;
var outputColumn = outputColumnsCollection.New();
outputColumn.Name = columnName;
var type = typeof(T);
DataType dataType;
if (type == typeof(Boolean))
dataType = DataType.DT_BOOL;
else if (type == typeof(DateTime))
dataType = DataType.DT_DATE;
else if (type == typeof(Decimal))
dataType = DataType.DT_DECIMAL;
else if (type == typeof(Double))
dataType = DataType.DT_R8;
else if (type == typeof(Int16))
dataType = DataType.DT_I2;
else if (type == typeof(Int32))
dataType = DataType.DT_I4;
else if (type == typeof(Int64))
dataType = DataType.DT_I8;
else if (type == typeof(Single))
dataType = DataType.DT_R4;
else if (type == typeof(String))
dataType = DataType.DT_WSTR;
else if (type == typeof(UInt16))
dataType = DataType.DT_UI2;
else if (type == typeof(UInt32))
dataType = DataType.DT_UI4;
else if (type == typeof(UInt64))
dataType = DataType.DT_UI8;
else
throw new NotImplementedException();
outputColumn.SetDataTypeProperties(dataType, length, 0, 0, 0);
return outputColumn;
}
protected IDTSOutputColumn100 GetOutputColumn(string columnName, string outputName)
{
var outputColumns = GetOutputColumns(outputName);
var column = outputColumns.Single(oc => oc.Name == columnName);
return column;
}
protected IDTSInputColumn100 GetInputColumn(string columnName, string inputName)
{
var inputColumns = GetInputColumns(inputName);
var column = inputColumns.Single(ic => ic.Name == columnName);
return column;
}
protected IList<IDTSInputColumn100> GetInputColumns(string inputName)
{
var input = Inputs.Single(ic => ic.Name == inputName);
return GetInputColumns(input);
}
protected IList<IDTSInputColumn100> GetInputColumns(IDTSInput100 input)
{
var inputColumnsCollection = input.InputColumnCollection;
var inputColumns = inputColumnsCollection.Cast<IDTSInputColumn100>();
return inputColumns.ToList();
}
protected IList<IDTSOutputColumn100> GetOutputColumns(string outputName)
{
var output = Outputs.Single(oc => oc.Name == outputName);
return GetOutputColumns(output);
}
protected IList<IDTSOutputColumn100> GetOutputColumns(IDTSOutput100 output)
{
var outputColumnsCollection = output.OutputColumnCollection;
var outputColumns = outputColumnsCollection.Cast<IDTSOutputColumn100>();
return outputColumns.ToList();
}
protected int GetInputColumnId(string columnName, string inputName)
{
var input = Inputs.Single(i => i.Name == inputName);
var inputColumn = GetInputColumn(columnName, inputName);
var index = (int)BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);
return index;
}
protected int GetOutputColumnId(string columnName, string outputName, int bufferId)
{
var output = Outputs.Single(o => o.Name == outputName);
var outputColumn = GetOutputColumn(columnName, outputName);
var index = (int)BufferManager.FindColumnByLineageID(bufferId, outputColumn.LineageID);
return index;
}
protected int GetOutputColumnId(string columnName, string outputName)
{
var output = Outputs.Single(o => o.Name == outputName);
var index = GetOutputColumnId(columnName, outputName, output.Buffer);
return index;
}
protected T GetInputColumnValue<T>(PipelineBuffer buffer, string inputName, string columnName)
{
var inputColumnId = GetInputColumnId(columnName, inputName);
var type = typeof(T);
object result;
if (type == typeof(Boolean))
result = buffer.GetBoolean(inputColumnId);
else if (type == typeof(DateTime))
result = buffer.GetDateTime(inputColumnId);
else if (type == typeof(Decimal))
result = buffer.GetDecimal(inputColumnId);
else if (type == typeof(Double))
result = buffer.GetDouble(inputColumnId);
else if (type == typeof(Int16))
result = buffer.GetInt16(inputColumnId);
else if (type == typeof(Int32))
result = buffer.GetInt32(inputColumnId);
else if (type == typeof(Int64))
result = buffer.GetInt64(inputColumnId);
else if (type == typeof(Single))
result = buffer.GetSingle(inputColumnId);
else if (type == typeof(String))
result = buffer.GetString(inputColumnId);
else if (type == typeof(UInt16))
result = buffer.GetUInt16(inputColumnId);
else if (type == typeof(UInt32))
result = buffer.GetUInt32(inputColumnId);
else if (type == typeof(UInt64))
result = buffer.GetUInt64(inputColumnId);
else
throw new NotImplementedException();
return (T)result;
}
protected bool HasInput(string inputName)
{
var hasInput = Inputs.Any(o => o.Name == inputName);
return hasInput;
}
protected bool HasOutput(string outputName)
{
var hasOutput = Outputs.Any(o => o.Name == outputName);
return hasOutput;
}
protected bool HasInputColumns(string inputName)
{
var hasInput = Inputs.Any(i => i.Name == inputName);
if (!hasInput)
return false;
var input = Inputs.Single(i => i.Name == inputName);
var inputColumns = GetInputColumns(input);
var hasInputColumns = inputColumns.Any();
return hasInputColumns;
}
protected bool HasOutputColumns(string outputName)
{
if (!HasOutput(outputName))
return false;
var output = Outputs.Single(o => o.Name == outputName);
var outputColumns = GetOutputColumns(output);
var hasOutputColumns = outputColumns.Any();
return hasOutputColumns;
}
protected bool HasInputColumn(string columnName, string inputName)
{
if (!HasInputColumns(inputName))
return false;
var hasNameSpecifiedColumn = GetInputColumns(inputName).Any(oc => columnName == oc.Name);
return hasNameSpecifiedColumn;
}
protected bool HasOutputColumn(string columnName, string outputName)
{
if (!HasOutputColumns(outputName))
return false;
var hasNameSpecifiedColumn = GetOutputColumns(outputName).Any(oc => columnName == oc.Name);
return hasNameSpecifiedColumn;
}
protected bool IsInputColumnTypeOf(string columnName, DataType type, string inputName)
{
var isInputColumnTypeOf = GetInputColumns(inputName)
.Any(c => c.Name == columnName && c.DataType == type);
return isInputColumnTypeOf;
}
protected bool IsOutputColumnTypeOf(string columnName, DataType type, string outputName)
{
var isOutputColumnTypeOf = GetOutputColumns(outputName)
.Any(c => c.Name == columnName && c.DataType == type);
return isOutputColumnTypeOf;
}
protected bool HasCustomProperty(string name)
{
var hasCustomProperty = CustomProperties.Any(cp => cp.Name == name);
return hasCustomProperty;
}
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
{
SetOutputBuffersDictionary(outputIDs, buffers);
base.PrimeOutput(outputs, outputIDs, buffers);
}
protected void SetOutputBuffersDictionary(int[] outputIDs, PipelineBuffer[] buffers)
{
var outputBuffersDictionary = new Dictionary<string, PipelineBuffer>();
for (int i = 0; i < outputIDs.Length; i++)
{
var outputId = outputIDs[i];
var outputCollection = ComponentMetaData.OutputCollection.GetObjectByID(outputId);
outputBuffersDictionary.Add(outputCollection.Name, buffers[i]);
}
OutputBuffersDictionary = outputBuffersDictionary;
}
protected void FireError(string message)
{
bool cancel;
ComponentMetaData.FireError(0, ComponentMetaData.Name, message, string.Empty, 0, out cancel);
}
protected void FireWarning(string message)
{
ComponentMetaData.FireWarning(0, ComponentMetaData.Name, message, string.Empty, 0);
}
protected void FireInformation(string message)
{
bool fireAgain = false;
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, message, string.Empty, 0, ref fireAgain);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment