Parallel Data Driven Query Example

This sample Microsoft® Visual Basic® function, sDDQTitleAuthors, creates and runs a package that transforms parts of the authors, titleauthor, and titles tables from the pubs database that ships with Microsoft SQL Server™ 2000. The function generates a hierarchical rowset consisting of the authors from the pubs database and the titles with which they are associated.

Creating sDDQTitleAuthors Rowset

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

CREATE TABLE dbo.AuthNames (
   AuthID VARCHAR (11) NOT NULL ,
   LastName VARCHAR (40) NOT NULL ,
   FirstName VARCHAR (20) NOT NULL )

CREATE TABLE dbo.TitleNames (
   AuthID VARCHAR (11) NOT NULL ,
   TitleName VARCHAR (80) NOT NULL )

As in flattened mode, the component rowsets are copied without regard to the chapters.

Running sDDQTitleAuthors

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

The steps for running sDDQTitleAuthors are as follows:

Create a database named DTSTest using SQL Server Enterprise Manager, and then create the tables defined earlier in DTSTest. If you use another database, change the line in the example that sets the database name for the destination connection.
Create a new Standard EXE project in the Visual Basic development environment. In the Project/References dialog box, check Microsoft DTSPackage Object Library and Microsoft DTSDataPump Scripting Object Library.
Copy the following code for function sDDQTitleAuthors to the code window for Form1.
Place a command button on the form Form1. In the _Click sub for the command button, call sDDQTitleAuthors.
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.
If you are using a database other than DTSTest, change the setting of the Catalog property of connection 2.
Run the project, click the command button, and then view the destination tables.

Writing sDDQTitleAuthors Code

This is the Visual Basic source code for the sDDQTitleAuthors function:

Private Function sDDQTitleAuthors() 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 oScriptTransform    As DTSPump.DataPumpTransformScript
    Dim oTransformationSet  As DTS.TransformationSet
    Dim oParallelPumpTask   As DTS.ParallelDataPumpTask
    Dim sScript(1 To 3)     As String
    Dim sScriptLanguage     As String
    Dim sScriptFunction     As String
  
Const SHAPE_PUBS_TITLEAUTHORS = _
    "SHAPE {SELECT au_id, au_lname, au_fname FROM authors} " & _
    "APPEND ({SELECT au_id, title FROM titleauthor TA, titles TS " & _
             "WHERE TA.title_id = TS.title_id} " & _
            "AS title_chap RELATE au_id TO au_id)"
    
Const SHAPE_DTSUE_TITLEAUTHORS = _
    "SHAPE {SELECT * FROM AuthNames} " & _
    "APPEND ({SELECT * FROM TitleNames} " & _
            "AS TitleChap RELATE AuthID TO AuthID)"
    
    '----- generate scripts, one needs 2 col, other needs 3
    sScriptLanguage = "VBScript"
    sScriptFunction = "Transform"
    sScript(1) = "Function Transform()" & vbCrLf & _
                 "DTSDestination(1) = DTSSource(1)" & vbCrLf & _
                 "DTSDestination(2) = DTSSource(2)" & vbCrLf
    sScript(2) = "DTSDestination(3) = DTSSource(3)" & vbCrLf
    sScript(3) = "Transform = DTSTransformStat_InsertQuery" & _
                 vbCrLf & "End Function"
    
    '----- define source connection - pubs
    Set oConnection = oPackage.Connections.New("MSDataShape")
    With oConnection
        .ConnectionProperties("Data Provider") = "SQLOLEDB"
        .ID = 1
        .Catalog = "pubs"
        .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
    
    '----- Create ParallelDPTask set DDQ, connections and commands
    Set oTask = oPackage.Tasks.New("DTSParallelDataPumpTask")
    Set oParallelPumpTask = oTask.CustomTask
    With oParallelPumpTask
        .TransformationSetOptions = DTSTranSetOpt_DataDrivenQueries
        .SourceConnectionID = 1
        .SourceSQLStatement = SHAPE_PUBS_TITLEAUTHORS
        .DestinationConnectionID = 2
        .DestinationSQLStatement = SHAPE_DTSUE_TITLEAUTHORS
    End With
    
    '----- create TransformationSet for parent rowset
    Set oTransformationSet = oParallelPumpTask. _
        TransformationSets.New("TransformSet_author")
    oParallelPumpTask.TransformationSets.Add oTransformationSet
    Set oTransform = oTransformationSet. _
        Transformations.New("DTS.DataPumpTransformScript")
    Set oScriptTransform = oTransform.TransformServer
    With oScriptTransform
        .Language = sScriptLanguage
        .FunctionEntry = sScriptFunction
        .Text = sScript(1) & sScript(2) & sScript(3)
    End With
    
    '----- define source/dest columns for parent
    With oTransform
        .SourceColumns.AddColumn "au_id", 1
        .SourceColumns.AddColumn "au_lname", 2
        .SourceColumns.AddColumn "au_fname", 3
        .DestinationColumns.AddColumn "AuthID", 1
        .DestinationColumns.AddColumn "LastName", 2
        .DestinationColumns.AddColumn "FirstName", 3
        .Name = "Transform"
    End With
        
    '----- define INSERT query, params for parent
    With oTransformationSet
        .InsertQuery = "INSERT AuthNames VALUES (?, ?, ?)"
        .InsertQueryColumns.AddColumn "AuthID", 1
        .InsertQueryColumns.AddColumn "LastName", 2
        .InsertQueryColumns.AddColumn "FirstName", 3
        .Transformations.Add oTransform
    End With

    '----- create TransaformationSet for child rowset
    Set oTransformationSet = oParallelPumpTask. _
        TransformationSets.New("TransformSet_title")
    oParallelPumpTask.TransformationSets.Add oTransformationSet
    Set oTransform = oTransformationSet. _
        Transformations.New("DTS.DataPumpTransformScript")
    Set oScriptTransform = oTransform.TransformServer
    With oScriptTransform
        .Language = sScriptLanguage
        .FunctionEntry = sScriptFunction
        .Text = sScript(1) & sScript(3)
    End With
    
    '----- define source/dest columns for child
    With oTransform
        .SourceColumns.AddColumn "au_id", 1
        .SourceColumns.AddColumn "title", 2
        .DestinationColumns.AddColumn "AuthID", 1
        .DestinationColumns.AddColumn "TitleName", 2
        .Name = "Transform"
    End With
    
    '----- define INSERT query, params for child
    With oTransformationSet
        .InsertQuery = "INSERT TitleNames VALUES (?, ?)"
        .InsertQueryColumns.AddColumn "AuthID", 1
        .InsertQueryColumns.AddColumn "TitleName", 2
        .Transformations.Add oTransform
    End With
    
    '----- add task, step to package
    oTask.Name = "ParallelDDQTask"
    With oPackage
        Set oStep = .Steps.New
        oStep.Name = "ParallelDPStep"
        oStep.TaskName = oTask.Name
        .Tasks.Add oTask
        .Steps.Add oStep
        .Name = "ParallelDDQTask Package"
        .FailOnError = True
    
        .Execute            'run the package
    
    End With
End Function