DTS Programming

Parallel Data Pump Example

This sample Microsoft® Visual Basic® function sCopyCustOrderProd creates and runs a package that transforms parts of the Customers, Orders, Order Details, and Products tables from the Northwind database that ships with Microsoft SQL Server™ 2000. The function generates a hierarchical rowset consisting of the customers located in the U.K., their orders, the order details, and the products.

Creating the sCopyCustOrderProd Rowset

This function copies the data to tables in a database called DTSTest that have the following structure:

CREATE TABLE dbo.customers (
   customer_key NCHAR (5) NOT NULL ,
   company_name NVARCHAR (40) NOT NULL )

CREATE TABLE dbo.orders (
   customer_key NCHAR (5) NULL ,
   order_key INT NOT NULL )

CREATE TABLE dbo.products (
   product_key INT NOT NULL ,
   product_name NVARCHAR (40) NOT NULL )

CREATE TABLE dbo.order_details (
   order_key INT NOT NULL ,
   product_key INT NOT NULL ,
   discount REAL NOT NULL )

The number of rows copied depends on whether Flattened or Hierarchical mode is used. In Flattened mode, the entire Orders, Order Details, and Products tables are copied. In Hierarchical mode, only the rows referenced by the U.K. customers are copied, although there are many duplicates of these rows in the products table in the destination database.

The Visual Basic Function for sCopyCustOrderProd

This is the Visual Basic source code for the sCopyCustOrderProd code:

Private Function sCopyCustOrderProd( _
    ByVal TranSetOpt As DTS.DTSTransformationSetOptions) As String
Dim oPackage            As New DTS.Package
Dim oConnection         As DTS.Connection
Dim oTask               As DTS.Task
Dim oStep               As DTS.Step
Dim oTransform          As DTS.Transformation
Dim oTransformationSet  As DTS.TransformationSet
Dim oParallelPumpTask   As DTS.ParallelDataPumpTask
  
Const SHAPE_NW_CUST_ORDER_PROD = _
    "SHAPE {SELECT CustomerID, CompanyName " & _
           "FROM Customers WHERE Country = 'UK'} " & _
    "APPEND ((SHAPE {SELECT OrderID, CustomerID FROM Orders} " & _
            "APPEND ((SHAPE {SELECT OrderID, ProductID, Discount " & _
                            "FROM [Order Details]} " & _
                    "APPEND ({SELECT ProductID, ProductName " & _
                             "FROM Products} " & _
                            "AS ProductChap " & _
                            "RELATE ProductID TO ProductID)) " & _
                    "AS DetailChap RELATE OrderID TO OrderID)) " & _
            "AS OrderChap RELATE CustomerID TO CustomerID)"
            
Const SHAPE_UE_CUST_ORDER_PROD = _
    "SHAPE {SELECT * FROM customers} " & _
    "APPEND ((SHAPE {SELECT * FROM orders} " & _
            "APPEND ((SHAPE {SELECT * FROM order_details} " & _
                    "APPEND ({SELECT * FROM products} " & _
                            "AS product_chap " & _
                            "RELATE product_key TO product_key)) " & _
                    "AS detail_chap RELATE order_key TO order_key)) " & _
            "AS order_chap RELATE customer_key TO customer_key)"
            
    '----- define source connection - Northwind
    Set oConnection = oPackage.Connections.New("MSDataShape")
    With oConnection
        .ConnectionProperties("Data Provider") = "SQLOLEDB"
        .ID = 1
        .Catalog = "Northwind"
        .UserID = "sa"
    End With
    oPackage.Connections.Add oConnection
   
    '----- define destination connection - (local) DTSTest
    Set oConnection = oPackage.Connections.New("MSDataShape")
    With oConnection
        .ConnectionProperties("Data Provider") = "SQLOLEDB"
        .ID = 2
        .DataSource = "(local)"
        .Catalog = "DTSTest"
        .UseTrustedConnection = True
    End With
    oPackage.Connections.Add oConnection
    
    '----- set hierarchical/flattened, set connections and commands
    Set oTask = oPackage.Tasks.New("DTSParallelDataPumpTask")
    Set oParallelPumpTask = oTask.CustomTask
    With oParallelPumpTask
        .TransformationSetOptions = TranSetOpt
        .SourceConnectionID = 1
        .SourceSQLStatement = SHAPE_NW_CUST_ORDER_PROD
        .DestinationConnectionID = 2
        .DestinationSQLStatement = SHAPE_UE_CUST_ORDER_PROD
    End With
    
    '----- create TransformationSet for customers
    Set oTransformationSet = oParallelPumpTask. _
        TransformationSets.New("TransformSet_Customers")
    oParallelPumpTask.TransformationSets.Add oTransformationSet
    Set oTransform = oTransformationSet. _
        Transformations.New("DTS.DataPumpTransformCopy")
    With oTransform
        .SourceColumns.AddColumn "CustomerID", 1
        .SourceColumns.AddColumn "CompanyName", 2
        .DestinationColumns.AddColumn "customer_key", 1
        .DestinationColumns.AddColumn "company_name", 2
        .Name = "Transform"
    End With
    oTransformationSet.Transformations.Add oTransform
        
    '----- create TransaformationSet for orders
    Set oTransformationSet = oParallelPumpTask. _
        TransformationSets.New("TransformSet_Orders")
    oParallelPumpTask.TransformationSets.Add oTransformationSet
    Set oTransform = oTransformationSet. _
        Transformations.New("DTS.DataPumpTransformCopy")
    With oTransform
        .SourceColumns.AddColumn "OrderID", 1
        .SourceColumns.AddColumn "CustomerID", 2
        .DestinationColumns.AddColumn "order_key", 1
        .DestinationColumns.AddColumn "customer_key", 2
        .Name = "Transform"
    End With
    oTransformationSet.Transformations.Add oTransform
    
    '----- create TransformationSet for order details
    Set oTransformationSet = oParallelPumpTask. _
        TransformationSets.New("TransformSet_Details")
    oParallelPumpTask.TransformationSets.Add oTransformationSet
    Set oTransform = oTransformationSet. _
        Transformations.New("DTS.DataPumpTransformCopy")
    With oTransform
        .SourceColumns.AddColumn "OrderID", 1
        .SourceColumns.AddColumn "ProductID", 2
        .SourceColumns.AddColumn "Discount", 3
        .DestinationColumns.AddColumn "order_key", 1
        .DestinationColumns.AddColumn "product_key", 2
        .DestinationColumns.AddColumn "discount", 3
        .Name = "Transform"
    End With
    oTransformationSet.Transformations.Add oTransform
        
    '----- create TransaformationSet for products
    Set oTransformationSet = oParallelPumpTask. _
        TransformationSets.New("TransformSet_Products")
    oParallelPumpTask.TransformationSets.Add oTransformationSet
    Set oTransform = oTransformationSet. _
        Transformations.New("DTS.DataPumpTransformCopy")
    With oTransform
        .SourceColumns.AddColumn "ProductID", 1
        .SourceColumns.AddColumn "ProductName", 2
        .DestinationColumns.AddColumn "product_key", 1
        .DestinationColumns.AddColumn "product_name", 2
        .Name = "Transform"
    End With
    oTransformationSet.Transformations.Add oTransform
    
    '----- add task, step to package
    oTask.Name = "ParallelDPTask"
    With oPackage
        Set oStep = oPackage.Steps.New
        oStep.Name = "ParallelDPStep"
        oStep.TaskName = oTask.Name
        .Tasks.Add oTask
        .Steps.Add oStep
        .Name = "ParallelDataPumpTask Package"
        .FailOnError = True
        
        .Execute            'run the package
   
   End With
End Function
Running sCopyCustOrderProd

This example can be run on a computer on which Microsoft Visual Basic 6.0 and SQL Server 2000 have been installed.

The basic steps for running sCopyCustOrderProd are as follows:

  1. Create a database named DTSTest using SQL Server Enterprise Manager, and then create the tables defined above in DTSTest. If you use another database, change the line in the example that sets the database name for the destination connection.

  2. Create a new Standard EXE project in the Visual Basic development environment. In the Project/References dialog box, select Microsoft DTSPackage Object Library.

  3. Copy the following code for function sCopyCustOrderProd to the code window for Form1.

  4. Place a command button and another control, such as a check box, on the form Form1. In the _Click sub for the command button, call sCopyCustOrderProd, and use the other control to provide values for the parameter TranSetOpt.

  5. You can add completion notification, such as a message box, and an error handler. For more information about returning meaningful error information, see Handling DTS Errors in Visual Basic.

  6. If you are using a database other than DTSTest, change the setting of the Catalog property of connection 2.

  7. Run the project, providing the value DTSTranSetOpt_Flattened for the TranSetOpt parameter. View, truncate the destination tables, and then run the sample again with TranSetOpt set to DTSTranSetOpt_Hierarchical.

See Also

Hierarchical Rowsets