DTS Package Details

A Data Transformation Services (DTS) package containing a specific configuration of DTS custom tasks, connection settings, and workflow is used to create a transformable subscription.

Connections

• To connect to the distribution database and provide published data to the DTS package (source connection), a special OLE DB provider, the Microsoft SQL Server Replication OLE DB Provider for DTS is used. When you create a transformable subscription, this provider is installed automatically on the DTS package, and cannot be changed. This provider can be used only with transformable subscriptions.

  The connection from the DTS package to the Subscriber (destination connection) does not use the Microsoft SQL Server Replication OLE DB Provider for DTS; it uses whatever OLE DB provider is required to connect to the Subscriber. For example, you would use the Microsoft OLE DB Provider for SQL Server to send transformed data to a Microsoft® SQL Server™ 2000 Subscriber.

• Only SQL Server (the Microsoft OLE DB Provider for SQL Server) Subscribers and other OLE DB Subscribers can use transformable subscriptions; ODBC Subscribers will not work with transformable subscriptions.

Package Storage

A DTS package created through replication is saved by default as a DTS SQL Server package (a DTS save option where the package is saved to local SQL Server tables in the msdb database); however, packages can also be saved as a DTS structured storage (.dts) file. Packages used with transformable subscriptions cannot be saved in the other available DTS save formats (repository, Microsoft Visual Basic® Script file, Visual Basic .bas file).

Package Security

The DTS package can be read or overwritten only by a user operating under the sysadmin role, or by the package owner. Thus, Distribution Agents need to be run under the sysadmin account or the package owner account at the Distributor or Subscriber site. The optional package password is stored in MSDistribution_agents at the Distributor, or in the MSSubscription_properties table. If the package is stored at the Distributor, all the publication access list (PAL) users will have access to the package passwords defined for subscriptions on the publication. If the package is stored on the Subscriber, members of db_owner for the Subscriber database have access.

Package Components

A DTS package used for transformable subscriptions consists of several DTS objects:

• Connections for the Subscriber (multiple Subscribers share the same connection) and the Publisher. The Publisher connection is always a Microsoft SQL Server Replication OLE DB Provider for DTS data source. A different Publisher connection is necessary for each article.
  
  
• One or more Execute SQL tasks. These include, at minimum, create table scripts, per article, for each published article.
  
  
• Data Driven Query task. This task is able to match different types of replication change request (INSERT, DELETE, UPDATE) with the data movement operations required to implement the request on the Subscriber correctly. Each article requires a different Data Driven Query task in the DTS package. For the data movement to work, the destination column values must bind to parameters in the INSERT, DELETE, and UPDATE statements. The Data Driven Query task handles all data movement from the distribution database to the Subscriber through its underlying data pump. For the snapshot, the InsertQuery property is used.
  
  
• If a replication DTS package is customized later in DTS Designer by adding a Microsoft ActiveX® script transformation, the return status specified in the script must be changed to:

  Main = DTSTransformStat_OK
  

• A global variable, ReplicationChangeType, is set by the Distribution Agent to signify the change type of a particular row, and can be accessed programmatically. Here are the available change types.

  Change Type	Value	Action
  INSERT	1	Source data is from a row that was inserted at the source.
  UPDATE	2	Source data is from a row that was updated at the source. The data are values after the update.
  DELETE	3	Source data is from a row that was deleted at the source.
  BEFORE UPDATE	4	Source data is from a row that was updated at the source. The data are values before the update. This is used with horizontal partitions.

  
  

  Declaring the global variable is optional. When used, its value is set by the Distribution Agent. The global variable can be used with an ActiveX script or other transformation servers to determine the change type associated with the current row. Following is sample Microsoft Visual Basic code you might use to declare and use this global variable:

  Dim oConnProperty As DTS.OLEDBProperty
  Dim gVar As Integer
  Dim oGlobal As DTS.GlobalVariable
  Set oGlobal = goPackage.GlobalVariables.New("ReplicationChangeType")
  oGlobal.Name = "ReplicationChangeType"
  oglobal.Value = 0
  goPackage.GlobalVariables.Add oGlobal
  Set oGlobal = Nothing
  

Debugging ActiveX Scripts in DTS Packages

You can debug ActiveX scripts in transformable subscription DTS packages. To debug your scripts:

• Turn on debugging in the Data Transformation Services Package Properties dialog.
  
  
• Run the Distribution Agent from the command line or ActiveX control. Do not run it under the SQL Server Agent NT service.

For more information about how to debug scripts, see Debugging ActiveX Scripts.

To turn on just-in-time debugging

Enterprise Manager

.