Implementing External Metadata
When a component is disconnected from its data source, you can validate the columns in the input and output column collections against the columns at its external data source by using the IDTSExternalMetadataColumnCollection90 interface. This interface lets you maintain a snapshot of the columns at the external data source and map these columns to the columns in the input and output column collection of the component.
Implementing external metadata columns adds a layer of overhead and complexity to component development, because you must maintain and validate against an additional column collection, but the ability to avoid expensive round trips to the server for validation may make this development work worthwhile.
Populating External Metadata Columns
External metadata columns are typically added to the collection when the corresponding input or output column is created. New columns are created by calling the New method. The properties of the column are then set to match the external data source.
The external metadata column is mapped to the corresponding input or output column by assigning the ID of the external metadata column to the ExternalMetadataColumnID property of the input or output column. This lets you locate the external metadata column easily for a specific input or output column by using the GetObjectByID method of the collection.
The following example shows how to create an external metadata column and then map the column to an output column by setting the ExternalMetadataColumnID property.
public void CreateExternalMetaDataColumn(IDTSOutput90 output, int outputColumnID )
{
IDTSOutputColumn90 oColumn = output.OutputColumnCollection.GetObjectByID(outputColumnID);
IDTSExternalMetadataColumn90 eColumn = output.ExternalMetadataColumnCollection.New();
eColumn.DataType = oColumn.DataType;
eColumn.Precision = oColumn.Precision;
eColumn.Scale = oColumn.Scale;
eColumn.Length = oColumn.Length;
eColumn.CodePage = oColumn.CodePage;
oColumn.ExternalMetadataColumnID = eColumn.ID;
}
Public Sub CreateExternalMetaDataColumn(ByVal output As IDTSOutput90, ByVal outputColumnID As Integer)
Dim oColumn As IDTSOutputColumn90 = output.OutputColumnCollection.GetObjectByID(outputColumnID)
Dim eColumn As IDTSExternalMetadataColumn90 = output.ExternalMetadataColumnCollection.New
eColumn.DataType = oColumn.DataType
eColumn.Precision = oColumn.Precision
eColumn.Scale = oColumn.Scale
eColumn.Length = oColumn.Length
eColumn.CodePage = oColumn.CodePage
oColumn.ExternalMetadataColumnID = eColumn.ID
End Sub
Validating with External Metadata Columns
Validation requires additional steps for components that maintain an external metadata column collection, because you must validate against an additional collection of columns. Validation can be divided into connected validation or disconnected validation.
Connected Validation
When a component is connected to an external data source, the columns in the input or output collections are verified directly against the external data source. Additionally, the columns in the external metadata collection must be validated. This is required because the external metadata collection can be modified by using the Advanced Editor in Business Intelligence Development Studio, and changes made to the collection are not detectable. Therefore, when connected, components must make sure that the columns in the external metadata column collection continue to reflect the columns at the external data source.
You may choose to hide the external metadata collection in the Advanced Editor by setting the IsUsed property of the collection to false. However this also hides the Column Mapping tab of the editor, which lets users map columns from the input or output collection to the columns in the external metadata column collection. Setting this property to false does not prevent developers from programmatically modifying the collection, but it does provide a level of protection for the external metadata column collection of a component that is used exclusively in Business Intelligence Development Studio.
Disconnected Validation
When a component is disconnected from an external data source, validation is simplified because the columns in the input or output collection are verified directly against the columns in the external metadata collection and not against the external source. A component should perform disconnected validation when the connection to its external data source has not been established, or when the ValidateExternalMetadata property is false.
The following code example demonstrates an implementation of a component that performs validation against its external metadata column collection. For a more detailed example, see the ADO Source Component Sample.
public override DTSValidationStatus Validate()
{
if( this.isConnected && ComponentMetaData.ValidateExternalMetaData )
{
// TODO: Perform connected validation.
}
else
{
// TODO: Perform disconnected validation.
}
}
Public Overrides Function Validate() As DTSValidationStatus
If Me.isConnected AndAlso ComponentMetaData.ValidateExternalMetaData Then
' TODO: Perform connected validation.
Else
' TODO: Perform disconnected validation.
End If
End Function