DTS Custom Transformation Example: Copy One Column
The following code example in Microsoft® Visual C++® implements a custom transformation that copies a single source column to a destination column. The source and destination columns must be the same simple type. The transformation verifies that:
- There is exactly one source and one destination column.
- The columns are the same type.
- The columns are not complex types like binary large objects (BLOBs).
To implement this example, use the Active Template Library (ATL) custom transformation template to create the transformation framework. Name the component DTSCopy and the transformation class Copy1Column. For more information, see Using the ATL Custom Transformation Template.
Add the following code segments to the framework files:
- CCopy1Column::PreValidateSchema method
- CCopy1Column::ValidateSchema method
- CCopy1Column::ProcessPhase method
- Error code definitions
PreValidateSchema
The code for PreValidateSchema checks the number of source and destination columns. It also checks that the types match and that they are simple types.
Adding PreValidateSchema Code
Insert the following code immediately ahead of the
return NOERROR;
statement in CCopy1Column::PreValidateSchema in file Copy1Column.cpp:
// Validate the count of source and destination columns.
if (pDestMetadata->cColumns != 1 || pSrcMetadata->cColumns != 1)
return DTSCopy_E_WrongNumCols;
// Validate that the destination column type is simple. Remove BYREF flag.
const DBCOLUMNINFO* pDestDBColumnInfo = &(pDestMetadata->rgDBColumnInfo[0]);
WORD wDestType = (pDestDBColumnInfo->wType & (~DBTYPE_BYREF));
if( wDestType & ( DBTYPE_ARRAY | DBTYPE_VECTOR | DBTYPE_RESERVED ) )
return DTSCopy_E_NotSimpleType;
// Validate that the source column type is simple.
const DBCOLUMNINFO* pSrcDBColumnInfo = &(pSrcMetadata->rgDBColumnInfo[0]);
WORD wSourceType = (pSrcDBColumnInfo->wType);
if( wSourceType & ( DBTYPE_ARRAY | DBTYPE_VECTOR | DBTYPE_RESERVED | DBTYPE_BYREF ) )
return DTSCopy_E_NotSimpleType;
// Source and destination columns must be the same type.
if( wDestType != wSourceType )
return DTSCopy_E_NotSameType;
ValidateSchema
The code for ValidateSchema performs the same logic as PreValidateSchema in this custom transformation. This will typically be the case in simple transformations that do not have properties needing to be set before validation can occur.
ValidateSchema does not support promotion or demotion between similar column data types (for example, int and smallint). It also does not reference the transformation flags.
Adding ValidateSchema Code
Insert the following code immediately ahead of the
return NOERROR;
statement in CCopy1Column::ValidateSchema in file Copy1Column.cpp.
// Validate the count of source and destination columns.
if (pDestColumnInfo->cColumns != 1 || pSrcColumnInfo->cColumns != 1)
return DTSCopy_E_WrongNumCols;
// Validate that the destination column type is simple. Remove BYREF flag.
const DBCOLUMNINFO* pDestDBColumnInfo = pDestColumnInfo->rgColumnData[0].pDBColumnInfo;
WORD wDestType = (pDestDBColumnInfo->wType & (~DBTYPE_BYREF));
if( wDestType & ( DBTYPE_ARRAY | DBTYPE_VECTOR | DBTYPE_RESERVED ) )
return DTSCopy_E_NotSimpleType;
// Validate that the source column type is simple.
const DBCOLUMNINFO* pSrcDBColumnInfo = pSrcColumnInfo->rgColumnData[0].pDBColumnInfo;
WORD wSourceType = (pSrcDBColumnInfo->wType);
if( wSourceType & ( DBTYPE_ARRAY | DBTYPE_VECTOR | DBTYPE_RESERVED | DBTYPE_BYREF ) )
return DTSCopy_E_NotSimpleType;
// Source and destination columns must be the same type.
if( wDestType != wSourceType )
return DTSCopy_E_NotSameType;
ProcessPhase
The code for ProcessPhase immediately returns if called for a phase other than DTSTransformPhase_Transform, although it should only be called for those phases specified by PreValidateSchema.
For the destination column, ProcessPhase code gets the buffer size from the binding structure and creates pointers to the buffer fields where the data, length and status are to be stored. For the source column, this code gets the length and status and creates a data pointer. It must check the source type for DBTYPE_BYREF to see whether an additional level of indirection is required.
If the source status is DBSTATUS_S_ISNULL, ProcessPhase sets the destination status to this value. Length and data do not need to be set when the status is set to DBSTATUS_S_ISNULL. Otherwise, it calculates the length of data to be moved, which is the shorter of the source actual data length or the size of the destination buffer (reduced by the width of a NULL character, for string types). It copies the data, sets status, and writes a trailing NULL character, if there is room (there will be, for string types).
Adding ProcessPhase Code
Insert the following code immediately ahead of the
return NOERROR;
statement in CCopy1Column::ProcessPhase in file Copy1Column.cpp:
// Only do something for the Transform phase.
if( pPhaseInfo && !( pPhaseInfo->eCurrentPhase & DTSTransformPhase_Transform ) )
return NOERROR;
// Get destination binding and information structures.
DTSColumnData* pDTSDestColumnData = &( pDestColumnInfo->rgColumnData[ 0 ] );
const DBBINDING* pDBDestBinding = pDTSDestColumnData->pDBBinding;
// Set the destination length to maximum length. Initialize to empty string.
ULONG ulDestMaxLen = pDBDestBinding->cbMaxLen;
LPBYTE pDestData = (pDTSDestColumnData->pvData + pDBDestBinding->obValue);
// Pointers to destination length and status buffers
ULONG* pulLength = (ULONG *)(pDTSDestColumnData->pvData + pDBDestBinding->obLength);
ULONG* pulStatus = (ULONG *)(pDTSDestColumnData->pvData + pDBDestBinding->obStatus);
// Get source binding and information structures.
DTSColumnData* pDTSSourceColumnData = &( pSrcColumnInfo->rgColumnData[ 0 ] );
const DBBINDING* pDBSourceBinding = pDTSSourceColumnData->pDBBinding;
// Get source type, length and status.
ULONG wSourceType = (pDBSourceBinding->wType);
ULONG ulSourceStatus = *(ULONG *)(pDTSSourceColumnData->pvData + pDBSourceBinding->obStatus);
ULONG ulSourceLength = *(ULONG *)(pDTSSourceColumnData->pvData + pDBSourceBinding->obLength);
LPBYTE pSourceData;
// Get pointer to source data.
if( wSourceType & DBTYPE_BYREF )
pSourceData = *(LPBYTE *)(pDTSSourceColumnData->pvData + pDBSourceBinding->obValue);
else
pSourceData = (pDTSSourceColumnData->pvData + pDBSourceBinding->obValue);
// Copy source to destination if source is not Null.
if( ulSourceStatus != DBSTATUS_S_ISNULL )
{
// Calculate maximum actual data space (allow room for \0).
wSourceType &= ~DBTYPE_BYREF;
ULONG ulMaxDataLen = ulDestMaxLen - ( ( wSourceType == DBTYPE_WSTR ) ? 2 :
( ( wSourceType == DBTYPE_STR ) ? 1 : 0 ) );
// Calculate length of data, then move it and set status.
*pulLength = min( ulSourceLength, ulMaxDataLen );
memcpy( pDestData, pSourceData, *pulLength );
*pulStatus = DBSTATUS_S_OK;
// Add one or two NULLs if there is room ( for ANSI/Unicode strings).
if( *pulLength < ulDestMaxLen )
*( pDestData + *pulLength ) = '\0';
if( *pulLength + 1 < ulDestMaxLen )
*( pDestData + *pulLength + 1 ) = '\0';
}
else
*pulStatus = DBSTATUS_S_ISNULL;
Error Code Definitions
These error codes are returned by functions in the transformation.
Adding Transformation Error Codes
Insert the following code immediately ahead of the
import "ocidl.idl";
statement in file Copy1Column.idl.
//Error codes for this custom transformation
typedef [helpstring("Error codes generated by the DTSCopy transformation")] enum DTSCopyError {
DTSCopy_E_WrongNumCols = 0x80041001,
DTSCopy_E_NotSimpleType = 0x80041002,
DTSCopy_E_NotSameType = 0x80041003,
} DTSCopyError, *LPDTSCopyError;
Building and Testing Copy1Column
For more information about building and testing this project, see Implementing and Testing a DTS Custom Transformation.