This article is meant as a very gentle introduction into creating your own custom transformation component in SQL Server 2005 Integration Services. In the article we
will be touching upon a few of the essential methods available to us in the new object model and showing you how to set a few of the new properties.
A single article such as this cannot do justice to the wealth of methods and properties available so in upcoming articles we will be building components that use
different pieces of the object model or functionality.
This package is really quite simple and does as the name suggests. We are going to take one or more string input column values and reverse them when sent to the destination.
The purpose of the component is not to be of great business value but to demonstrate what goes into building a simple component.
This is what our component will look like in the package
The methods we will be looking at are detailed below.
-
ProvideComponentProperties
-
SetUsageType
-
PreExecute
-
ProcessInput
-
Validate
-
ReinitializeMetaData
-
InsertOutput
-
DeleteOutput
We will only be dealing with the actual code in this article so as we visit each of the methods above we shall explain how we have used it and why.
In building these components we have found, as we believe you will find, Books Online to be a hit and miss affair in researching what method does what
but seeing as this is a Beta release then that is only to be expected. Hopefully our Programming series will allow you to learn from our mistakes
Methods Explained
ProvideComponentProperties
This method is called when your component is dropped onto the designer. In our component we have used it to add an initial input and an output. We give both
of them a name so we can recognise them when we look at our component in designer. The code here is commented.
/*relevant class scoped variables*/
private IDTSOutput90 ReverseStringOutput;
private IDTSInput90 ReverseStringInput;
public override void ProvideComponentProperties()
{
ComponentMetaData.UsesDispositions = true;
//We want an input and an output
//Input
ReverseStringInput = ComponentMetaData.InputCollection.New();
ReverseStringInput.Name = "RSin";
//If the input is rubbish then fail the whole shebang
ReverseStringInput.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
//Output
ReverseStringOutput = ComponentMetaData.OutputCollection.New();
ReverseStringOutput.Name = "RSout";
//his matches the out to the in Async
ReverseStringOutput.SynchronousInputID = ReverseStringInput.ID;
AddErrorOutput("RSErrors", ReverseStringInput.ID, ReverseStringOutput.ExclusionGroup);
//So what this does is allow you to have 2 outputs
//in our case a good output and an error output
//That have the same synchronousinputID
//You can then use methods on the row to direct the buffer row accordingley
//to the correct output
ReverseStringOutput.ExclusionGroup = 1;
}
SetUsageType
Our component calls this method when we click way from adding input column(s) to the component. In this particular instance we want to check two things about the columns
we have just added. The first is that they are of the correct datatypes (DT_STR or DT_WSTR) and the second is that they are set to Read-Write. This second check is needed
because we are going to be altering the values in the input by reversing them. This method is not CLS Compliant. In our AssemblyInfo file we have marked the assembly as being CLS Compliant.
[assembly: CLSCompliant(true)]
We therefore need to mark non CLS compliant methods with the following attribute
[CLSCompliant(false)]
The first thing we do is declare a method variable of type IDTSInputColumn90. This is used as the return value for us calling the base class' SetUsageType method
What appears to happen is that as we click away from the setting of input columns in the UI the input columns are iterated over and checked against our constraints.
The reason we say this is that if you have multiple input columns and each has a problem when being validated only the first instance of an error column is returned.
When you fix that one and click away again then the second column fails our validation.
[CLSCompliant(false)]
public override IDTSInputColumn90 SetUsageType(int inputID, IDTSVirtualInput90 virtualInput, int lineageID, DTSUsageType usageType)
{
/*
here is where we are going to set the properties on the columns.
we only need to check that the column datatype is a character type
and that it is RW
*/
IDTSInputColumn90 propCol;
propCol = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (propCol != null)
{
if (propCol.DataType != DataType.DT_STR && propCol.DataType != DataType.DT_WSTR)
{
throw new Exception("There is a problem with your input column : " + propCol.Name.ToString() + " it is not the correct datatype.");
}
if (propCol.UsageType != DTSUsageType.UT_READWRITE)
{
throw new Exception("There is a problem with your input column : " + propCol.Name.ToString() + " it is not set to read/write.");
}
}
return propCol;
}
Here is how it looks if a column fails the validation in the advanced UI.
PreExecute
This is the workhorse of the component and a lot of our other components along with the Validate method which we shall see later. What we do here is we iterate over the input columns
(IDTSInputColumn90) in our input column collection (ComponentMetaData.InputCollection[0].InputColumnCollection). As we encounter each column we load the properties we want into
the structure so we can use them later in ProcessInput. You will also notice throughout the component a liberal sprinkling of this method:
void FireInformation(
[In] int InformationCode,
[In] string SubComponent,
[In] string Description,
[In] string HelpFile,
[In] int HelpContext,
[In, Out] ref bool pbFireAgain
);
This allows us to add information to the designer output at runtime. It is a good way of gently monitoring a component. Be careful though not to abuse it as
there is an overhead in doing this so only use it on small datasets.
Here is the method code.
/* our struct */
public struct ReverseStringColInfo
{
public int bufferColumnIndex;
public DTSRowDisposition columnDisposition;
public int lineageID;
}
public override void PreExecute()
{
//This is a really cool way of storing information about your columns
//into a structure. (Thanks Mark Silvey)
//The component will call this immediately prior to ProcessInput
bool pbFireAgain = true;
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "Entering Pre-Execute", "", 0, ref pbFireAgain);
ReverseStringInput = ComponentMetaData.InputCollection[0];
ReverseStringColInfos = new ReverseStringColInfo[ReverseStringInput.InputColumnCollection.Count];
for (int x = 0; x < ReverseStringInput.InputColumnCollection.Count; x++)
{
IDTSInputColumn90 column = ReverseStringInput.InputColumnCollection[x];
ReverseStringColInfos[x] = new ReverseStringColInfo();
ReverseStringColInfos[x].bufferColumnIndex = BufferManager.FindColumnByLineageID(ReverseStringInput.Buffer, column.LineageID);
ReverseStringColInfos[x].columnDisposition = column.ErrorRowDisposition;
ReverseStringColInfos[x].lineageID = column.LineageID;
}
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "Exiting Pre-Execute", "", 0, ref pbFireAgain);
}
There is a method in here that deserves special mention and that is FindColumnByLineageID
int FindColumnByLineageID(
int ,
[In] int hBufferID
);
This method retrieves for us our column's place in the buffer. We will need that
if we want to set or retrieve values for that column now or in the future. It
takes as arguments the buffer and the column's lineageID
ProcessInput
This is the method where we manipulate our input and reverse the strings in our component's input columns. The
buffer object parameter on the input to the method holds our data so whilst the buffer has data we proceed. We also need to look inside the array
(ReverseStringColInfos) that is holding the information about our component's input columns to see if the user actually specified any columns.
If there are no columns then we simply send the data straight through the component and to the output unchanged. If there are values in the array then
we simply iterate over them, grab the value from the column (buffer.GetString()) reverse it and put it right back (buffer.SetString()). We then direct the row to the output.
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
bool pbFireAgain = true;
if (!buffer.EndOfRowset)
{
//Here is where do something
//The buffer has the data
char[] rsArray;
while (buffer.NextRow())
{
ComponentMetaData.FireInformation(0, ComponentMetaData.Name, "Got a row", "", 0, ref pbFireAgain);
//Let's go through our structure collection
//If this is empty then we have no input columns
if (ReverseStringColInfos.Length == 0)
{
buffer.DirectRow(ComponentMetaData.OutputCollection[0].ID);
}
else
{
for (int x = 0; x < ReverseStringColInfos.Length; x++)
{
ReverseStringColInfo MyBufferCol = ReverseStringColInfos[x];
StringBuilder sb = new StringBuilder();
if (!buffer.IsNull(MyBufferCol.bufferColumnIndex))
{
rsArray = buffer.GetString(MyBufferCol.bufferColumnIndex).ToString().ToCharArray();
Array.Reverse(rsArray);
for (int i = 0; i < rsArray.Length; i++)
{
sb.Append(rsArray[i]);
}
buffer.SetString(MyBufferCol.bufferColumnIndex, sb.ToString());
}
}
buffer.DirectRow(ComponentMetaData.OutputCollection["RSout"].ID);
}
}
}
}
Validate
This method is called numerous times throughout the life of the component. A lot of your validation code should be going in here because it will help to alert you to errors in the component. An example of this is the SetUsageType method. Remember how we can fire an error if an input columns fails the validation in the method?. Well it is possible for the user to click through that error. We therefore need a way to let our component know that things are not right and the Validate method is where we do this. Inside the method we see some other methods like AreInputColumnsValid(). This method checks to make sure that the columns in our component actually exist in the upstream component. If they do not then we return from the Validate method asking for new Metadata (VS_NEEDSNEWMETADATA) which in turn calls ReinitializeMetaData.
[CLSCompliant(false)]
public override DTSValidationStatus Validate()
{
bool Cancel;
/*
OK because we have an input it is possible for the package builder to
add and remove the flow between our source input and our component
and this wi1l wreck the metadata so we need to check
*/
if (ComponentMetaData.AreInputColumnsValid == false)
{ return DTSValidationStatus.VS_NEEDSNEWMETADATA; }
//VS_NEEDSNEWMETADATA calls ReinitialiseMetaData
/*
For this component we have one requirement
This is that the column on which we have defined the property is READ WRITE
*/
foreach (IDTSInputColumn90 RSincol in ComponentMetaData.InputCollection["RSin"].InputColumnCollection)
{
//If we have the custom property to Yes then make sure the column is also read write
if (RSincol.UsageType != DTSUsageType.UT_READWRITE)
{
return DTSValidationStatus.VS_ISBROKEN;
}
if (RSincol.DataType != DataType.DT_WSTR && RSincol.DataType != DataType.DT_STR)
{
return DTSValidationStatus.VS_ISBROKEN;
}
}
return base.Validate();
}
Here is how failure in the Validate() method is represented in the component
ReinitializeMetaData
This method is called when the component returns VS_NEEDSNEWMETADATA. In our example we check the input columns again as we do in the
Validate() method and should it return false then we call the RemoveInvalidInputColumns() method of the component. This has the effect of
removing erroneous input columns from the input to our component.
public override void ReinitializeMetaData()
{
//Because we want to validate the input and make sure columns have not been removed
//Let's remove the bad ones
if (!ComponentMetaData.AreInputColumnsValid)
{
ComponentMetaData.RemoveInvalidInputColumns();
}
base.ReinitializeMetaData();
}
InsertOutput
This method lets you handle what happens when an Output is added to your component. You can choose to let the UI add an output or you can choose to throw an error.
In our component we choose to throw an error. The method is not CLS compliant.
[CLSCompliant(false)]
public override IDTSOutput90 InsertOutput(DTSInsertPlacement insertPlacement, int outputID)
{
throw new Exception("You cannot insert an output (" + outputID.ToString() + ")");
}
DeleteOutput
This method handles the UI removing an output. In our component we throw an error.
public override void DeleteOutput(int outputID)
{
throw new Exception("You cannot delete an ouput column (" + outputID.ToString() + ")");
}
The following screenshot is the result of using our component on the FirstName, MiddleName and LastName columns of the DimCustomer table in the AdventureWorksDW database as een through the eyes of a data visualiser.
We hope this article started to get you interested in creating your own components in Integration Services.
This article was written for SQL Server 2005 Beta 2 (Yukon) and Visual Studio 2005 Beta 1 (Whidbey), and may be updated to reflect changes in future releases of these products.