December 3, 2009 at 5:15 pm
Hello All,
am using Microsoft's Business Intelligence Studio, and I am trying to get data from a table (which is a matrix) and multiply this matrix.
Using an ODE DB Source, I run a query on my data to produce the following table:
AverageTransition MatrixRow MatrixColum
1 1 1
2 1 2
3 1 3
2 2 1
2 2 2
4 2 3
3 3 1
1 3 2
5 3 3
I want to create a custom transformation (asynchronous) component that takes this data, stores it into an array, multiplies it by itself and outputs the multiplied matrix.
I have been working on a class library that will at do this. At the moment I want to get some data out of the tool (just the input data. Not currently storing in an array and performing the calculation), but its not working for me.
Here is the code:
#region Using Assemblies
using System;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
#endregion
namespace Test.Dts.Pipeline.TransitionMatrixCalculator
{
[DtsPipelineComponent(DisplayName = "Matrix Calculator",
ComponentType = ComponentType.Transform,
Description = "Multiply a matrix")]
public class TransitionMatrixCalculator : PipelineComponent
{
int AverageTransitionColumnIndex;
int RowNumberColumnIndex;
int ColumnNumberColumnIndex;
int SquaredValueIndex;
int RowNumberIndex;
int ColumnNumberIndex;
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
#region Resetting the component
this.RemoveAllInputsOutputsAndCustomProperties();
this.ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
this.ComponentMetaData.UsesDispositions = false;
this.ComponentMetaData.ValidateExternalMetadata = true;
#endregion
#region Add Input Collection
IDTSInput100 input = ComponentMetaData.InputCollection.New();
input.Name = "Input Matrix";
input.ExternalMetadataColumnCollection.RemoveAll();
input.ExternalMetadataColumnCollection.IsUsed = false;
#endregion
#region Add output Collection
IDTSOutput100 Output = ComponentMetaData.OutputCollection.New();
Output.Name = "Squared Matirx";
Output.Description = "Squared Matrix";
Output.SynchronousInputID = 0;
Output.ExternalMetadataColumnCollection.RemoveAll();
Output.ExternalMetadataColumnCollection.IsUsed = false;
#endregion
#region Define Output Columns
IDTSOutputColumn100 SquaredValue = this.InsertOutputColumnAt(Output.ID, 0, "Squared Value", "");
SquaredValue.SetDataTypeProperties(DataType.DT_R8, 0, 0, 0, 0);
IDTSOutputColumn100 RowNumber = this.InsertOutputColumnAt(Output.ID, 1, "Row_Number", "Row number of the matrix");
RowNumber.SetDataTypeProperties(DataType.DT_R8, 0, 0, 0, 0);
IDTSOutputColumn100 ColNumber = this.InsertOutputColumnAt(Output.ID, 2, "Column_Number", "Column number of the matrix");
ColNumber.SetDataTypeProperties(DataType.DT_R8, 0, 0, 0, 0);
#endregion
#region Define Custom Properties
ComponentMetaData.UsesDispositions = false;
IDTSCustomProperty100 matrixsize = ComponentMetaData.CustomPropertyCollection.New();
matrixsize.Name = "Matrix Size";
matrixsize.Description = "Size of the Input Matirx. For Example, 3 for a 3*3 matrix";
matrixsize.Value = "";
#endregion
}
public override void PreExecute()
{
base.PreExecute();
#region Input Columns
IDTSInput100 input = ComponentMetaData.InputCollection[0];
IDTSInputColumnCollection100 inputColumns = input.InputColumnCollection;
IDTSInputColumn100 AverageTransitionColumn = inputColumns[0];
if (AverageTransitionColumn == null)
{
throw new ApplicationException("Average Transition column is missing");
}
IDTSInputColumn100 RowNumberColumn = inputColumns[1];
if (RowNumberColumn == null)
{
throw new ApplicationException("Row Number column is missing");
}
IDTSInputColumn100 ColumnNumberColumn = inputColumns[2];
if (ColumnNumberColumn == null)
{
throw new ApplicationException("Column Number column is missing");
}
AverageTransitionColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, AverageTransitionColumn.LineageID);
RowNumberColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, RowNumberColumn.LineageID);
ColumnNumberColumnIndex = BufferManager.FindColumnByLineageID(input.Buffer, ColumnNumberColumn.LineageID);
#endregion
#region Output Columns
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
IDTSOutputColumnCollection100 outputColumns = output.OutputColumnCollection;
IDTSOutputColumn100 SquaredValue = outputColumns[0];
IDTSOutputColumn100 RowNumber = outputColumns[1];
IDTSOutputColumn100 ColumnNumber = outputColumns[2];
SquaredValueIndex = BufferManager.FindColumnByLineageID(output.Buffer, SquaredValue.LineageID);
RowNumberIndex = BufferManager.FindColumnByLineageID(output.Buffer, RowNumber.LineageID);
ColumnNumberIndex = BufferManager.FindColumnByLineageID(output.Buffer, ColumnNumber.LineageID);
#endregion
}
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
base.ProcessInput(inputID, buffer);
if (buffer != null)
{
if (!buffer.EndOfRowset)
{
while (buffer.NextRow())
{
buffer[SquaredValueIndex] = buffer[AverageTransitionColumnIndex];
buffer[RowNumberIndex] = buffer[RowNumberColumnIndex];
buffer[ColumnNumberIndex] = buffer[ColumnNumberColumnIndex];
}
if (buffer.EndOfRowset)
{
buffer.SetEndOfRowset();
}
}
}
}
}
}
When I deploy the tool and execute the package, I don't get an error, but the package gets stuck on the tool. It remains yellow as if its processing but I never get and output to my union all. Can anyone help me identify what I'm doing wrong?
Thanks
December 3, 2009 at 5:34 pm
Not exactly sure - but you can debug your component by starting up a session of Visual Studio and connecting to the DTExec process. Set a breakpoint in your ProcessInput method. I'm even sure you can do that remotely - but I've never done remote .Net debugging.
If you can't do that, then use the ComponentMetaData.FireInformation methods to push info to the Output window. If the package is running in DTExec, you won't have that window - but you can still get those events by turning package logging on.
What I can see is that you're using the EndOfRowset and NextRow improperly. DON'T check EndOfRowset at the top of your method. Move the NextRow loop outside of that IF. Check for EndOfRowset after you do your loop.
You should also move that column checking code you have out of PreExecute and into a Validate method.
Hope that helps...
Todd McDermid - SQL Server MVP, MCSD.Net, MCTS (SQL08BI)
Find great SSIS extensions - most are open source - at SSIS Community Tasks and Components[/url]
Are you doing Data Warehouse ETL with SSIS? Don't use the slow SCD Wizard, use the Kimball Method SCD component instead.
December 3, 2009 at 5:46 pm
Thank-you very much
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply