Adding DTS Column Objects
You create Column objects to specify the source and destination columns referenced by a transformation and to specify the destination column parameters for the queries of the DataDrivenQueryTask2 object.
Typically, transformations reference columns of the source and destination connections. In most cases, source and destination columns need to be assigned to a transformation to define these columns.
To create a Column object, use the New method of the SourceColumns or DestinationColumns collections of the Transformation2 object. Set properties as appropriate, then use the Add method of the appropriate collection. Or, use the AddColumn method to create and add the column in a single step.
You do not need to define Column objects if:
- Only a single Transformation2 object has been defined for the task.
- The number of columns in the data source and the data destinations is the same.
- The source and destination column ordering correctly match up.
Note You can use a Select query on the data source, specified with the DataDrivenQueryTask2, DataPumpTask2, or ParallelDataPumpTask object SourceSQLStatement property, to control the number and ordering of source columns.
Whether a transformation can access the source or destination connections is determined by the phase in which it runs. The following table specifies the permitted access.
| Phase | Source column access | Destination column access |
|---|---|---|
| PreSourceData | Read access to meta data | Write access to columns |
| Transform | Read access to columns | Write access to columns |
| OnTransformFailure | Read access to columns | Write access to columns |
| OnInsertSuccess | Read access to columns | No access to columns |
| OnInsertFailure | Read access to columns | No access to columns |
| OnBatchComplete | Read access to meta data | Write access to columns |
| PostSourceData | Read access to meta data | Write access to columns |
| OnPumpComplete | No access to columns | No access to columns |
For more information about configuring the Column object and the SourceColumns and DestinationColumns collections, see DTS Column Objects in Visual Basic.
To assign destination column parameters to the DataDrivenQueryTask2 and ParallelDataPumpTask object queries, use the procedure specified above to create Column objects and add them to the DeleteQueryColumns, InsertQueryColumns, UpdateQueryColumns, or UserQueryColumns collections, as appropriate. For more information about parameterized queries for these objects, see Adding DTS Query Strings.