Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Adding DTS Column Objects

SQL Server 2000

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.