Creating a Transformable Subscription Using Visual Basic

Advanced users familiar with the Data Transformation Services (DTS) object model can build their own transformable subscription DTS packages in Microsoft® Visual Basic™. For information about programming to the DTS object model, see DTS Programming Reference.

A transformable subscription is a subscription in which the data is modified as it flows from Publisher to Subscriber. In replication programming, a Visual Basic program for a transformable subscription closely resembles that of a DTS Visual Basic program used outside of replication. This sample is located in C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\sqlrepl\repldts.

How to Run the Transformable Subscription Sample

The program in the repldts folder is a sample of how to write a transformable subscription using Visual Basic.

To run the sample program

On the computer that will be a Publisher with a local Distributor, verify that the SQL Server Agent is running. If it is not, start it.
Open Visual Basic 6.0. Open ReplDTS.vbp, and then open the code window for ModReplDTS (ReplDTS.bas).
Select the following Project / References:
- Microsoft DTSPackage Object Library (required)
- Microsoft DTSDataPump Scripting Object Library (required to use a Microsoft ActiveX® script or custom transformation)
- Microsoft DTS Custom Tasks Object Library (required to use one of the DTS custom tasks)
Edit the following line of code to include your own connection information:
```
goPackage.SavetoSQLServer "MyServerName", "sa", ""
```
In Visual Basic, start the ReplDTS program.
When the program is finished executing, you should receive a message indicating the Employees package was saved successfully. When you receive this message, save the project, and then close Visual Basic.
In SQL Server Enterprise Manager, ensure that your server is configured for replication.
In SQL Server Enterprise Manager, click Data Transformation Services, click Local Packages, and then on the Action menu, click Refresh to refresh the view. The package Employees should appear in the right pane.
Open SQL Query Analyzer, open the repldts.sql script supplied with sample, and then edit the @subscriber parameter of sp_addsubscription so that it contains your server name. The sp_addsubscription stored procedure is the last SQL statement in the file.
Run the repldts.sql script. You can run the entire script at once, or you can run the script a block at a time and check each message.
After the repldts.sql script has completed successfully, in SQL Server Enterprise Manager, expand Replication Monitor, expand the Agents folder, and then click the Snapshot Agent folder.
In the right pane, right-click the Snapshot Agent for the Employees publication, and then click Start Agent.
When the Snapshot Agent has completed, view or query the data in MyEmployees table of the subscription database ReplDTS_SubDB, and then compare it to the data in the Employees table of the publication database ReplDTS_PubDB.
The Address column of the MyEmployees table in the subscription database, ReplDTS_subDB, contains data concatenated from several columns of the Employees table of the publication database.

Examining the Sample Code

This section describes key parts of the sample Visual Basic program.

The Publisher connection is always set to the Microsoft SQL Server Replication OLE DB Provider for DTS. This is specified in the section of code where the connections are created. In addition, a required property specified for this provider is the column list for ConnectionProperties, which provides DTS Designer with the number and names of the source columns in the package. The Subscriber connection (not shown here) is set to the Microsoft OLE DB Provider for SQL Server.

Dim oConnection As DTS.Connection
Set Connection = goPackage.Connections.New("SQLReplication.OLEDB")
oConnection.Name = "Publisher article 'Employees'"
oConnection.ID = 1
oConnection.ConnectImmediate = False
oConnection.ConnectionProperties("Column List") = _
  "[EmployeeID],[LastName],[FirstName],[Title],
  [BirthDate],[HireDate],[Address],[City],[Region],
  [PostalCode],[Country],[HomePhone],[Extension]
  [ReportsTo]"
goPackage.Connections.Add oConnection
Set oConnection = Nothing

If the destination table schema is not defined, use an Execute SQL custom task containing an SQL statement to generate the schema for the destination (Subscriber) table.
Use additional Execute SQL custom tasks to define SQL scripts to be applied after the data has been copied to the destination (for example, a script containing index generation statements (not shown)).

Use the following conventions when naming tasks in a replication DTS program (required by the replication agents):
- For a Data Driven Query task, the task name is the same as the article name.
- For custom tasks, such as an Execute SQL task, the name is a concatenated string consisting of the article name, the prefix "pre" (if the task executes before the snapshot data is copied) or "post" (if the task executes after the snapshot data is copied), and an optional part ("ignore_error") if an instruction is given to continue program execution when a query script error is encountered. To determine what the name should be, run sp_helparticledts in SQL Query Analyzer. For more information, see sp_helparticledts.
In the following code sample, the Execute SQL task name "Employees_pre_ignore_error" (line 5) means that the article name is Employees, the task occurs before the snapshot data is copied, and that program execution should continue if a script error is encountered.
```
Dim oTask As DTS.Task
Dim oCustomTask0 As DTS.ExecuteSQLTask
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask0 = oTask.CustomTask
oCustomTask0.Name = "Employees_pre_ignore_error"
oCustomTask0.Description = "Pre script for article employees"
oCustomTask0.SQLStatement = _
   "If object_id('MyEmployees') is NOT NULL _
   BEGIN Drop Table MyEmployees END _
   Create Table MyEmployees _
   ([EmployeeID] [int] NOT NULL,
    [LastName] [nvarchar] (20) NOT NULL,
    [FirstName] [nvarchar] (10) NOT NULL,
    [Title] [nvarchar] (30) NULL,
    [Birthdate] [datetime] NULL,
    [HireDate] [datetime] NULL,
    [Address] [nvarchar] (255) NULL,
    [HomePhone] [nvarchar] (24) NULL,
    [Extension] [nvarchar] (4) NULL,
    [ReportsTo] [int] NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY _
       CLUSTERED([EmployeeID]))"
CustomTask0.ConnectionID = 2
goPackage.Tasks.Add oTask
Set CustomTask0 = Nothing
Set oTask = Nothing
```

With transformable subscriptions, data movement is always done with a Data Driven Query task, never with a Transform Data task, which is commonly used in DTS packages that do not use replication. Therefore, you must define a Data Driven Query custom task:

Dim oTransformation As DTS.Transformation
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Dim oCustomTask1 As DTS.DataDrivenQueryTask
Set oTask = goPackage.Tasks.New("DTSDataDrivenQueryTask")
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Employees"
oCustomTask1.Description = "Transformations for article Employees"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceObjectName = "nothing" 'Experiment
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "MyEmployees"

Among the properties you need to define for the Data Driven Query task are the parameterized queries associated with each type of incremental update (INSERT, UPDATE, and DELETE). The parameterized query works by selecting for data movement source rows that satisfy the conditions in the query statement.

oCustomTask1.InsertQuery = "INSERT INTO _
   MyEmployees values (?,?,?,?,?,?,?,?,?,?)"
oCustomTask1.UpdateQuery = "UPDATE _MyEmployees _
   SET LastName=?, FirstName=?, Title=?, Birthdate=?, _
   HireDate=?, Address=?, HomePhone=?, Extension=?, _
   ReportsTo=? where EmployeeID=?"
oCustomTask1.DeleteQuery = "DELETE MyEmployees _
   WHERE EmployeeID = ?"

Another essential set of operations is setting the source and destination column collections. Because one of the transform operations performed by the DTS package is a concatenation of several of the source columns, the number of destination columns added to the destination collection is fewer than the number of source columns added to the source collection. An example of one source column and one destination column added to their respective collections is shown here. The column "Address" for the destination is actually the concatenation of the source columns "Address", "City", "Region", and "Postal Code".
```
Set oColumn = oTransformation.DestinationColumns.New("HireDate", 6)
oColumn.Name = "HireDate"
oColumn.Ordinal = 6
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
...

Set oColumn = oTransformation.DestinationColumns.New("Address", 7)
oColumn.Name = "Address"
oColumn.Ordinal = 7
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
...
```
To complete the Data Driven Query task transformation, add column definitions (in sequential order) to each of the incremental update operations described earlier. For example, because an INSERT operation was defined with parameters for the 10 destination columns of the article, INSERT column definition code for each of the 10 columns is required (only the first two are shown here):
```
Set oColumn = oCustomTask1.InsertQueryColumns.New("EmployeeID", 1)
oColumn.Name = "EmployeeID"
oColumn.Ordinal = 1
oCustomTask1.InsertQueryColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oCustomTask1.InsertQueryColumns.New("LastName", 2)
oColumn.Name = "LastName"
oColumn.Ordinal = 2
oCustomTask1.InsertQueryColumns.Add oColumn
Set oColumn = Nothing
...
```
Similar code is used for the UPDATE and DELETE queries. When defining the columns for those queries, remember that the order of the columns must follow the order of the parameters specified by the question mark (?) characters in the InsertQuery and DeleteQuery definitions. For example, in this sample, the DELETE query uses only the EmployeeId column; therefore, only code for that column is used.
```
Set oColumn = oCustomTask1.DeleteQueryColumns.New("EmployeeID", 1)
oColumn.Name = "EmployeeID"
oColumn.Ordinal = 1
oCustomTask1.DeleteQueryColumns.Add oColumn
Set oColumn = Nothing
```
In the example, a Microsoft ActiveX® script performs the actual transformation of data, which is the concatenation of several columns. The entire ActiveX script code should be viewed in the sample. This example shows how to set the transform server property to handle scripts, and how to set the line of script code used to perform the column concatenation.
```
Set oTransProps = oTransformation.TransformServerProperties
...
oTransProps("Text") = oTransProps("Text") & 
   "   DTSDestination(""Address"") = DTSSource(""Address"") _
   & "","" & DTSSource(""City"") &"",""& DTSSource(""Region"") _
   &"",""& DTSSource(""PostalCode"")" & vbCrLf
...
```
Transformable subscription DTS packages are typically saved to an instance of SQL Server. They can also be saved as a .dts structured storage file, but cannot be saved to the repository. In the following line of code, the package is saved to an instance of SQL Server:
```
GoPackage.SaveToSQLServer "myServerName", "sa", ""
...
```
DTS includes several options for handling data conversions. These options are handled by a set of transformation flags whose values can be viewed in the Visual Basic Object Browser under the DTSPump component, DTSTransformFlags enumeration. When building a replication DTS package in Visual Basic, if an ActiveX script transformation is used, as in this sample, the transformation flags do not need to be explicitly set. If you build a replication DTS package without ActiveX scripts (using only Copy Column mappings), the TransformFlags property must be explicitly set to DTSTransformFlag_Default (a value of 63).
```
oTransformation.TransformFlags = 63
```