Hello all,
I'm writing a custom component similar to the "Derived Column" transformation, in which a value of some input column is "decoded" into an output column.
Here's the code:
Code Snippet
public override void ProcessInput(int inputID, PipelineBuffer buffer){
//TODO
setupTraceListener();
IDTSOutput90 outp = ComponentMetaData.OutputCollection["DecodeOutput"];
int outId = outp.ID;
int errorOut = -1;
IDTSInput90 inp = ComponentMetaData.InputCollection.GetObjectByID(inputID);
if( ComponentMetaData.OutputCollection.Count > 1){errorOut = ComponentMetaData.OutputCollection["DecodeErrors"].ID;
}
string sourceValue;
if(!buffer.EndOfRowset){
while(buffer.NextRow()){
if(!buffer.IsNull(inputColumnBufferIndex)){
switch(inp.InputColumnCollection[0].DataType){
case DataType.DT_WSTR:
sourceValue = buffer.GetString(this.inputColumnBufferIndex);
break;
case DataType.DT_I4:
sourceValue = buffer.GetInt32(this.inputColumnBufferIndex).ToString();
break;
default: throw new Exception("Invalid Data Type!");
}
sourceValue = sourceValue.Trim();
try{
object decodedValueObj = this.mappings[sourceValue];
if(decodedValueObj == null){
throw new Exception("No mapping!");
}
string decodedValue = decodedValueObj.ToString();
switch(outp.OutputColumnCollection[0].DataType){
case DataType.DT_WSTR:
buffer.SetString(this.outputColumnBufferIndex, decodedValue);
break;
case DataType.DT_I4:
buffer.SetInt32(this.outputColumnBufferIndex, int.Parse(decodedValue));
break;
default:
throw new IOException("Invalid Data Type!");
}
buffer.DirectRow(outId);
}catch(IOException fake){
throw fake;
}catch(Exception e){
redirectOrFail(inp, errorOut, outId, buffer, e);
}
}else{
redirectOrFail(inp,errorOut,outId,buffer,new Exception("Null values cannot be mapped"));
}
}
}
}
mappings is a hash map filled in PreExecute by accessing a database, outputColumnBufferIndex is calculated like this:
Code Snippet
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSOutput90 output = ComponentMetaData.OutputCollection["DecodeOutput"];
IDTSInputColumn90 inputColumn = input.InputColumnCollection[0];
IDTSOutputColumn90 outputColumn = output.OutputColumnCollection[0];
this.inputColumnBufferIndex =
BufferManager.FindColumnByLineageID(input.Buffer, inputColumn.LineageID);
this.outputColumnBufferIndex =
BufferManager.FindColumnByLineageID(input.Buffer, outputColumn.LineageID);
I've highlighted the parts that interest me, I've been testing the component thoroughly and it is getting the input data and mappings right, but it won't write the Decoded value to the given column and leaves it empty, even though the decodedValue variable is not null.
I'm guessing theres a problem when I call buffer.SetString() but I haven't the slightest idea of what that could be.
Any help would be greatly appreciated!
Have you debugged it? Do inputColumnBufferIndex and outputColumnBufferIndex values make sense?
Do you only have one input and one output column in your component?
Are you getting any errors from SetString?
Thanks,
Bob
|||
Thank you for your reply Bob,
Yes, I did debug it and the values made sense. Thats how I know that the decodedValue was being also properly initialized.
It's working now by using:
Code Snippet
buffer[this.outputColumnBufferIndex] = decodedValue;
Don't ask me why.
I'm having all sorts of problems that have very easy but completely undocumented solutions like having to override PerformUpgrade to update the UserComponentTypeName attribute and the Version, even though I'm not changing either one as the component is still in development and not in use in any "real" packages.
Maybe my installation is corrupted somehow?
|||
Hmm, you should not be required to implement PerformUpgrade unless you are really changing your component.
Could you check your ProvideComponentProperties implementation and if there is something that might be messing up the settings?
Thanks,
Bob
|||
I don't know what to look for, this is my 3rd week with C# and SSIS.
Here's the basic idea:
Code Snippet
public override void ProvideComponentProperties(){
//FIXME
base.ProvideComponentProperties();
(set the name, dispositions, etc...)
base.RemoveAllInputsOutputsAndCustomProperties();
(add synchronous inputs and outputs)
(add custom properties)
}
I suspect it could be I'm calling RemoveAllInpusOutputsAndCustomProperties(); after base.ProvideComponentProperties(), but I don't want to touch it just yet. What are your thoughts?
Thank you very much again.
|||
Yeah, that is it.
You can safely remove that line as ProvideComponentProperties is called only once in the SSIS designer.
Alternatively, you may move that line before base.ProvideComponentProperties().
Thanks,
Bob
|||Thank you very much for your help|||
Adrian, this is not related to your question, but there is a bug in how buffer.EndOfRowset is used in the ProcessInput method in the first code sample, please see my blog for details
http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx
|||
Thank you very much for that link! I'll be changing that right away.
