Last active
December 17, 2015 12:48
-
-
Save MaciejLisCK/a2840cdfcedda88d83a0 to your computer and use it in GitHub Desktop.
SSIS Custom Component Error Output
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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