Column Information Structures in DTS Transformations
When you build a custom transformation, you must consider the Data Transformation Services (DTS) transformation methods that are used to validate and process the source and destination columns being transformed. The ValidateSchema, Execute, OnRowComplete and OnTransformComplete methods of the IDTSDataPumpTransform interface and the ProcessPhase method of the IDTSDataPumpTransform2 interface need to access structures that define these columns. This access is provided by pointers to a DTSTransformColumnInfo structure for the source columns and another DTSTransformColumnInfo for the destination columns, which are passed as parameters to each of these methods.
DTSTransformColumnInfo Important Fields
The following are the important fields in DTSTransformColumnInfo.
| Field | Description |
|---|---|
| cColumns | Count of source or destination columns. |
| rgColumnData | Pointer to an array of DTSColumnData structures. There is one array element for each column. |
The remaining fields are associated with binary large object (BLOB) processing, and only need to be referenced if the transformation processes BLOB types.
For the complete definition of this structure, search the include file dtspump.h for DTSTransformColumnInfo. This file is installed by default in C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\include\ during a custom installation of Microsoft® SQL Server™ 2000 client tools.
DTSColumnData Important Fields
The array referenced by DTSTransformColumnInfo.rgColumnData contains a DTSColumnData structure for each source or destination column. The following are the important fields in DTSColumnData.
| Field | Description |
|---|---|
| pDBColumnInfo | Pointer to an OLE DB DBCOLUMNINFO structure for the column. |
| pDBBinding | Pointer to an OLE DB DBBINDING structure for the column. |
| pvData | Pointer to the data space for the column. Includes fields for the data (or a pointer to the data), data length, and status. |
When IDTSDataPumpTransform::ValidateSchema is called, both pDBBinding and pvData are NULL. Thus, the DBBINDING structure and the data space are not available in ValidateSchema.
For the complete definition of this structure, search the include file dtspump.h for DTSColumnData.
DBCOLUMNINFO Important Fields
Each DTSColumnData structure references an OLE DB DBCOLUMNINFO structure, which contains the meta data for the column. The following are the important fields in DBCOLUMNINFO.
| Field | Description |
|---|---|
| pwszName | The name of the column. |
| iOrdinal | The numeric position of the column within the source or destination row. |
| dwFlags | The sum of flags for meta data attributes (for example, ISNULLABLE, ISROWID, KEYCOLUMN). |
| ulColumnSize | The width of the column, in characters for wide character types and in bytes for other types. |
| wType | The data type of the column. |
The information in the DBCOLUMNINFO structure is not generally modified by the transformation. For the complete definition of this structure, search the include file OLEDB.h for DBCOLUMNINFO.
To find the valid values for dwFlags, search OLEDB.h for DBCOLUMNFLAGS_. The symbols containing DBCOLUMNFLAGS_ are defined in enumerations named DBCOLUMNFLAGSENUMxx, where xx is an optional OLE DB version number.
To find the valid values for wType, search OLEDB.h for DBTYPE_. The symbols containing DBTYPE_ are defined in enumerations named DBTYPExx, where xx is an optional OLE DB version number.
DBBINDING Important Fields
Each DTSColumnData structure also references an OLE DB DBBINDING structure. A binding associates a single column to the buffer referenced by the pvData field of the DTSColumnData structure, and it contains information about that buffer. The following are the important fields in DBBINDING.
| Field | Description |
|---|---|
| iOrdinal | The numeric position of the column within the source or destination row. |
| obValue | The offset within the buffer referenced by DTSColumnData.pvData where the data value, or a pointer to the data value, is stored. |
| obLength | The offset within the buffer referenced by DTSColumnData.pvData where the actual data length, in bytes, is stored. |
| obStatus | The offset within the buffer referenced by DTSColumnData.pvData where the data status is stored. |
| dwPart | Flags that specify which parts of the buffer are to be bound to the column. The flags will indicate a combination of data length, status and value. |
| cbMaxLen | The size of the data area of the buffer, which is the maximum length of the data. For character types, this is usually the width of the column in bytes, plus one character. |
| wType | The data type of the column. |
For the complete definition of this structure, search the include file OLEDB.h for DBBINDING.
To find the valid values for the field whose offset is specified by obStatus, search OLEDB.h for DBSTATUS_. The symbols containing DBSTATUS_ are defined in enumerations named DBSTATUSENUMxx, where xx is an optional OLE DB version number.
To find the valid values for dwPart, search OLEDB.h for DBPART_. These symbols containing DBPART_ are defined in an enumeration named DBPARTENUM.
The valid values for wType are the same as for the DBCOLUMNINFO.wType field. If wType includes the flags DBTYPE_ARRAY, DBTYPE_BYREF or DBTYPE_VECTOR, then the field in DTSColumnData.pvData at offset obValue contains a pointer to the data, not the data itself.
IDTSDataPumpTransform2::PreValidateSchema Method
In PreValidateSchema the column information parameters are DTSTransformColumnMetadata structures. The following are the important fields in DTSTransformColumnMetadata.
| Field | Description |
|---|---|
| cColumns | Count of source or destination columns. |
| rgDBColumnInfo | Pointer to an array of OLE DB DBCOLUMNINFO structures. There is one array element for each column. DBCOLUMNINFO was described above. |
The information in the DTSTransformColumnMetadata structures is the same information that is available to ValidateSchema, packaged differently.