DTS Package Properties (Advanced Tab)

DTS Designer

DTS Designer Help

DTS Package Properties (Advanced Tab)

Use this tab to specify data lineage properties, Microsoft® SQL Server™ 2000 Meta Data Services scanning options, and transaction settings for Data Transformation Services (DTS). These allow you to do the following:

  • The data lineage feature of DTS allows you to determine the source of any piece of data and the transformations applied to that data.

  • Meta Data Services scanning options are settings for relating objects referenced by the package to catalog meta data in Meta Data Services.

  • Transaction capabilities allow you to assign tasks in a workflow to a transaction, and commit and roll back individual steps based on the success or failure of the transactional unit. The settings on the Advanced tab allow you to turn on transaction capabilities, and set their commit mode and isolation level.

    Note  Not all OLE DB providers support transactions or specific transaction capabilities. For more information, see the documentation for the individual providers.

The first two groups of these options can only be used if you are saving a DTS package to Meta Data Services. If you attempt to save a package to any other location or format after you have clicked any of these options, DTS Designer prompts you to save the package to Meta Data Services.

Options

Name

View the package name.

Show lineage variables as source columns

Add global data lineage variables to the package, but do not write them to Meta Data Services. Clicking this option by itself is useful if you want to create a custom task to write lineage tracking and auditing information.

Write lineage to repository

Specify to always write data lineage variables to the repository database when saving a package.

Options

Set Meta Data Services scanning options for the package.

Use transactions

Allow the definition and use of transactional units of work in the package.

Commit on successful package completion

Specify that each individual SQL statement is a transaction. If the statement completes successfully, the transaction is automatically committed; if the statement has an error, the statement is rolled back (the default setting for OLE DB).

When this setting is cleared, the DTS connections operate in implicit transaction mode. (The first SQL statement begins a transaction that remains in effect until DTS commits it or rolls it back. A new transaction is started by the next SQL statement executed after any commit or rollback.)

Transaction isolation level

Select from one of these levels:

  • Chaos. You can see uncommitted changes made by other transactions, but update locks are not held to the end of the transaction. Rollback is not supported. This isolation level is not supported by SQL Server.

  • Read Committed. You cannot see changes made by other transactions until those transactions are committed.

  • Read Uncommitted. You can see uncommitted changes made by other transactions.

  • Repeatable Read. You are guaranteed not to see any changes made by other transactions in values it has already read.

  • Serializable. This option guarantees that all concurrent transactions will interact only in ways that produce the same effect as if each transaction were executed entirely one after the other.

Use OLE DB service components

Select to instantiate the OLE DB provider data source objects using the OLE DB service components (IDataInitialize::CreateDBInstance,the default), or clear to instantiate the data source objects directly with CoCreateInstance.

OLE DB service components provide services like session pooling and IRowsetChange, which may not be supported by some OLE DB providers. This setting is ignored by the DTS providers (PackageDSO, RowQueue, FlatFile) and by the Microsoft OLE DB Provider for SQL Server.

See Also

Incorporating Transactions in a DTS Package

Recording Data Lineage in DTS

Managing DTS Package Properties