Chapter 9. Building a Package Using Visual Basic

Building a Package Using Visual Basic

In Chapter 2, “DTS—An Overview,” we used DTS Designer to create a sample DTS package. DTS Designer, a simple yet effective tool, provides a graphic interface for manipulating the underlying DTS object model. Although providing ample functionality for most applications, it does not expose all objects, properties, and methods of the model. As a result, it is not the best mechanism for certain situations, such as the following:

  • Standalone applications or servers

  • GUI applications in which you want to interact with users and allow them to control or monitor DTS progress

  • Automated processing, such as:

    • Processing all the files in a directory

    • Processing spreadsheets in an Excel workbook

    • Processing XML input

    • Processing Outlook messages

  • Processing events, allowing corrective action at run time

In these cases, it’s simpler to use the DTS object model to either create a new package or access and manipulate an existing one from a custom application. Although any language that supports COM automation can be used, we will confine our discussions to Visual Basic (VB) 6.0.

Prior to SQL 2000, developing a package in VB required considerable effort. With SQL 2000, the capability to save a DTS package to a VB .bas file has eliminated most of this work.

The Scenario

As you may recall in Chapter 2, you produced several reports for Bob by using DTS Designer. At the last managers’ meeting, other product managers expressed an interest in Bob’s new reports. However, each needed to see a different product mix. You have been asked to set up DTS to generate reports based on inventory, such as Bob’s Shipping Reports, but to use a Shipping Budget provided by individual managers.

You could copy Bob’s package (see Figure 9.1) and modify the location of the budget for each manager. This would be a great deal of work. In addition, if anything were to change, such as the location of the Manufacturing database, each package would require maintenance.

DTS package.

Figure 9.1. DTS package.

One of the nicest features added to SQL 2000 is the capability to develop a package by using DTS Package Wizard or DTS Designer and then save it as a VB .bas file.

In this chapter, you will save the package as a VB .bas file, include it in a VB project, and then add a simple GUI to allow each manager to specify the location for his or her budget and report.

In this scenario, building a package in VB is an attractive solution. To create a report, a manager would do the following:

  1. Copy ShippingBudget.xls to his work area and customize it as required.

  2. Run the application.

  3. When prompted, provide the location for the copy of the ShippingBudget.xls. ShippingReport.xls will be copied to the same directory as the budget. The ShippingReports copy is processed by DTS to produce the desired report.

To build this solution, you do the following:

  1. Start the DTS Designer by opening C:DTS2000Data CreateExcelReports.dts [1] and saving it as a VB .bas file.

  2. Create a sample managers directory (C:TempDTS2000Manager).

  3. Copy C:DTS2000DataShippingBudget.xls [2] into this directory.

  4. Start VB, and begin a new standard .exe, as follows:

    1. Add a reference for the DTS package.

    2. Add a file reference to the script generated by DTS.

    3. Save the project and compile. (This step is required to ensure that the App.Path works correctly during debugging.)

    4. Modify the VB application to prompt for the location of ShippingBudget.xls, copy ShippingReports.xls to the same directory as the budget, and run the DTS package to update the copy of ShippingReports.xls.

    5. Run the application, and when prompted for the budget location, specify C:TempDTS2000ManagerShippingBudget.xls.

  5. Use Excel to view the generated report (C:TempDTS2000Manager ShippingReports.xls).

Generating a VB DTS Package with SQL 2000 DTS Designer

The first step in implementing your solution is to save the CreateExcelReport package as a VB .bas file by following these steps.

  1. Start Enterprise Manager, and select your server.

  2. Right-click Data Transformation Services, select Open Package, and load C:DTS2000PackagesCreateExcelReport.dts [3] .

  3. Use the Save As menu option to open the Save DTS Package dialog box (see Figure 9.2).

    The Save DTS Package dialog box.

    Figure 9.2. The Save DTS Package dialog box.

  4. Type CreateExcelReport in the Package Name text box.

  5. Ignore the password options, because they have no effect on the VB file.

  6. From the Location drop-down menu, choose Visual Basic File.

  7. Type C:DTS2000PackagesCreateExcelReport in the File Name text box.

  8. Click OK.

The result is a VB .bas file that contains all the code needed to create and execute the DTS package. This file can be included in a new or existing VB application or an ActiveX .dll.

About Saving Packages

After you save a package as a VB file, you can no longer use the DTS Designer to edit the package. If you spend a great deal of time developing a template that you might want to use a basis for other projects, save it first in SQL Server or as a DTS structured (.dts) or DTS template (.dtt) file. Then use the DTS Designer to script the =.bas file.

You can use SQL Server 2000 DTS Designer to edit packages created with the SQL Server 7.0 DTS Designer. Although it can be used to script a VB .bas file, scripts will use the SQL 2000 DTS object model.

Including the DTS Script in a VB Application

To use the generated DTS script, you need to integrate it into a new or existing VB project. The following project types are supported:

  • Standard .exe

  • ActiveX .exe

  • ActiveX .dll

  • ActiveX Document .exe

To implement your solution, open a new standard .exe. Choose Add Module from the Project menu to include the C:DTS2000DataCreateExcelReport.bas module.

You will need to add a reference to the DTS object library. Choose References from the Project menu to open the References dialog box, and select Microsoft DTSPackage Object Library (see Figure 9.3).

VB DTS package-object reference.

Figure 9.3. VB DTS package-object reference.

If you also have SQL 7.0 client tools installed, be sure that you select the most recent version of the DTS package object model.

DTS Dynamic Link Libraries

SQL 2000’s Server or SQL Client Tools installation includes several DTS dynamic link libraries. By default, the files are installed in C:Program FilesMicrosoft SQL Server80ToolsBinn, as follows:

  • Dtspkg.dll. Implements the Microsoft DTS Package Object Library, DTSPkg, which allows you to create and modify DTS packages.

  • Dtspump.dll. Implements the Microsoft DTS DataPump Scripting Object Library, DTSPump, which allows you to write directly to the data pump. The Transform Data Task, Data Driven Query, and Parallel Data Driven Query Task use the DTS pump interface.

  • Custtask.dll—. Implements the Microsoft DTS Custom Tasks Object Library, CustTask, which provides the Message Queue Task, the File Transfer Protocol Task, and the Dynamic Properties Task.

Depending on your application and the DTS package requirements, running the application on a machine that is not running SQL Server may require one or all of these .dlls, DMOs, dependent .rlls, and util libraries. Microsoft recommends installing SQL client tools in advance or using the VB Package Wizard, which will take care of the details.

VB Threading Considerations

When executing a package from a program written in Visual Basic and monitoring package or task events, all steps in the package must be configured to execute on the main thread. This is because a limitation in VB prevents it from properly handling multiple simultaneous calls to its events.

Connection Considerations

Take care when you set up your connections. When a package is included in a VB application, it executes on the machine running the program, not on the SQL Server host, as is the case with packages run by SQL Server Agent. Any connections set to local when you designed the package must be updated to include a reference to the server.

Compiling and Running the Package

You can run the package under the debugger or compile it to an executable.

If you hope to run the application on another computer, in addition to the SQL client tools, you will need to ensure that the necessary VB runtime libraries are installed. Once again, the VB Package Wizard will take care of these details.

The DTS Object Model

All interaction with the package occurs through the DTS object model, which provides an extensive set of components designed to extract, cleanse, transform, and load data between data stores. Figure 9.4 provides a high-level map of the DTS object model. This diagram shows an overview of objects that are covered in Chapters 3 through 6.

DTS object model.

Figure 9.4. DTS object model.

While working on your VB application, use the diagram and the object browser in VB to familiarize yourself with the DTS object model.

The DTS VB Script

Before implementing your solution, a review of the code generated would be instructive.

The package scripted by DTS Designer is rather large. The code sets all properties for all objects, including properties set to their default values. Even for a simple package, this situation can result in thousands of lines of code.

At first, this code can be intimidating. Analysis shows a simple scripting strategy (see Listing 9.1).

Example 9.1. Package Scripting Strategy

'**************************************************************** 
'Microsoft SQL Server 2000 
'Visual Basic file generated for DTS Package 
' .. . . 
'**************************************************************** 

Option Explicit 
Public goPackageOld As New DTS.Package 
Public goPackage As DTS.Package2 

Private Sub Main() 
   'Set Package Options 
   'Define Global Variables 
   'Define Connections 
   'Define Steps 
   'Define Precedents 
   'Call Routines to Define Each Task 
   'Call Routines to Define Data Transformations 
   'Execute or Save Package 
End Function 'Main 

Subroutines to Define Each Task and Transformation 
.. . 

The same scheme is used to script a package from either DTS 2000 or DTS 7.0. The object models for both versions are similar. In places where new functionality has been included, rather than augmenting the original, a new object has been added that is indicated with the number 2 appended (Package2, Connection2, Step2, and so on). The original objects (Package, Connection, Step, and so on) remain for compatibility.

About the Type-Mismatch Error

You’ll receive a type-mismatch error if you attempt to run a DTS application that uses any of the extended objects on a computer that has SQL Server 7.0 client tools installed.

As mentioned earlier in this chapter, the DTS Designer generates code that sets most, if not all, properties for an object—even default values. You could reduce the amount of code by removing these redundant statements. These statements, however, document what is going on, and if you remove the wrong line, your package will fail (at best) or run and quietly produce garbage (at worst).

Setting Package Options

The first thing scripted is the creation of the package object, which holds the basic information as well as collections for connections, steps, tasks, and so on. You cannot create a new DTS.Package2 directly or declare one by using WithEvents. Instead, you create a standard DTS package and cast it into a Package2, as shown in Listing 9.2.

Example 9.2. The Package Object in Visual Basic

Public goPackageOld As New DTS.Packagect 

Public goPackage As DTS.Package2 
set goPackage = goPackageOld 

    goPackage.Name = "CreateExcelReports" 
goPackage.WriteCompletionStatusToNTEventLog = False 
    goPackage.FailOnError = False 
    goPackage.PackagePriorityClass = 2 
    goPackage.MaxConcurrentSteps = 4 
    goPackage.LineageOptions = 0 
    goPackage.UseTransaction = True 
    goPackage.TransactionIsolationLevel = 4096 
    goPackage.AutoCommitTransaction = True 
    goPackage.RepositoryMetadataOptions = 0 
    goPackage.UseOLEDBServiceComponents = True 
    goPackage.LogToSQLServer = False 
    goPackage.LogServerFlags = 0 
    goPackage.FailPackageOnLogFailure = False 
    goPackage.ExplicitGlobalVariables = False 
    goPackage.PackageType = 0 

Table 9.1 describes each package option from the preceding code listings.

Table 9.1. Package Options

Option

Description

Name and Description

Describe the package.

PackagePriorityClass

Sets the Windows 32 process priority.

MaxConcurrentSteps

Controls the number of steps that can execute concurrently. Each step runs in its own thread.

However, if the steps are set to execute on the main thread, this number has no effect.

Usually, a value larger than the number of CPUs will be of little use.

If you have tasks with long CPU waits (results pending from another server or MSMQ), you can set this option higher.

FailOnError

If True, disables package-error processing and raises aVB error. Great for debugging.

WriteCompletionStatusToNTEventLog

If True, writes the status on completion to the NT application’s event log.

LineageOptions and RepositoryMetadataOptions

LineageOptions defines how the package lineage is presented and recorded.

RepositoryMetadataOptions defines meta data scanning and resolution options when storing the package in the SQL Server 2000 Meta Data Services.

UseTransaction

If True, runs package steps under a single transaction contingent on the correct transactional setting for steps and providers.

TransactionIsolationLevel

Sets the package transaction’s isolation level. The default is Read-Committed.

AutoCommitTransaction

If True, commits pending transactions upon successful completion.

ExplicitGlobalVariables

If True, requires that global variables are declared before use. Similar to MicrosoftVisual Basic when you turn on Option Explicit.

LogToSQLServer, LogServerFlags, LogServerName, LogServerPassword, LogServerUserName, and FailPackageOnLogFailure UseOLEDBServiceComponents

Enable logging to an instance of SQL Server 2000, identify the server, and provide authentication information.

If True, specifies that OLE DB components will be used.

PackageType

Provides information about the application that creates the package.

Defining the Package’s GlobalVariables Collection

Next, you script the GlobalVariables collection. This collection is used to store and share data between steps and ActiveX scripts in the package, and even between packages. You can add, remove, or change the value of GlobalVariables objects dynamically during execution. Each GlobalVariables object provides a variant data type.

DTS scripts a single global reference that is used to create all the necessary GlobalVariables objects and appends them to the package object’s GlobalVariables collection (see Listing 9.3).

Example 9.3. Working with Global Variables in Visual Basic

'---------------------------------------------------------------
' Begin to write package global variables information 
'---------------------------------------------------------------
   Dim oGlobal As DTS.GlobalVariable 

   Set oGlobal = _ 
      goPackage.GlobalVariables.New("ActualsThroughMonth") 
      oGlobal = 0 
   goPackage.GlobalVariables.Add oGlobal 
   set oGlobal = Nothing 

   Set oGlobal = _ 
      goPackage.GlobalVariables.New("ErrorMessage") 
   oGlobal = "" 
   goPackage.GlobalVariables.Add oGlobal 
   set oGlobal = Nothing 

   Set oGlobal = _ 
      goPackage.GlobalVariables.New("CurrentYear") 
   oGlobal = 0 
   goPackage.GlobalVariables.Add oGlobal 
   set oGlobal = Nothing 

Defining Package Connections

The Connections collection, which is used to store package OLE DB data provider information, is scripted next. Each Connection object describes a single data provider. If the OLE DB provider can support connection pooling, you can reuse these connections across multiple steps or tasks.

DTS scripts a single Connection reference, which is used to create all the necessary Connection objects and appends them to the package object’s Connections collection.

For your solution, DTS scripts three connections (see Listing 9.4). The first one represents the Access data source; the second two represent the Excel spreadsheets.

Example 9.4. Working with Package Connections in Visual Basic

'------------------------------------------------------------------
'Create package connection information 
'For security purposes, the password is never scripted 
'------------------------------------------------------------------
Dim oConnection as DTS.Connection2 

'------------- a new connection defined below. 
Set oConnection = _ 
goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0") 
   oConnection.ConnectionProperties("User ID") = "Admin" 
   oConnection.ConnectionProperties("Data Source") = _ 
       "C:...Manufacturing.mdb" 
   oConnection.Name = "Inventory Actuals" 
   oConnection.ID = 1 
   oConnection.Reusable = True 
   oConnection.ConnectImmediate = False 
   oConnection.DataSource = "C:...Manufacturing.mdb" 
   oConnection.UserID = "Admin" 
   oConnection.ConnectionTimeout = 60 
   oConnection.UseTrustedConnection = False 
   oConnection.UseDSL = False 
   'If a password is required, uncomment and add here 
   'oConnection.Password = "<put the password here>" 
goPackage.Connections.Add oConnection 
Set oConnection = Nothing 
... 

In general, connection information depends heavily on the OLE DB provider.

Simple options such as DataSource, UseTrustedConnection, UserID, Password, and Timeout are self-explanatory. Table 9.2 describes some additional options.

Table 9.2. Connection Options

Option

Description

Reusable

If True, multiple steps can share the connection.

ConnectImmediate

If True, the connection to the data store occurs when the package starts; otherwise, the connection waits until a task using it begins.

Options that define the provider are often poorly documented or difficult to find. In most cases, it is significantly less effort to let DTS Designer do the work.

As you can see, passwords are not scripted. If passwords are required, simply uncomment the appropriate lines and provide the required information.

Defining Package Steps

DTS next scripts the Steps collection, which defines task workflow for a package. Each step specifies one or more tasks to be run. A step can execute in parallel with other steps, serially, or conditionally based on their execution.

The DTS Designer creates steps automatically for each task you define. All that remains is for you to define the precedence constraints. In code, steps need to be defined explicitly, and tasks need to be assigned to them. Tasks that are not assigned to a step will not execute.

Tasks and steps can be created in any order. The DTS Designer follows a top-down approach, scripting first the steps, then the precedence, and finally the task details (see Listing 9.5).

Example 9.5. Working with Package Steps in Visual Basic

'----------------------------------------------------------------
' Create package steps information 
'----------------------------------------------------------------
Dim oStep as DTS.Step2 
Dim oPrecConstraint as DTS.PrecedenceConstraint 

'------------- a new step defined below 
Set oStep = goPackage.Steps.New 
   oStep.Name = "DTSStep_DTSActiveScriptTask_1" 
   oStep.Description = "Prepare Spreadsheet" 
   oStep.ExecutionStatus = 1 
   oStep.TaskName = "DTSTask_DTSActiveScriptTask_1" 
   oStep.CommitSuccess = False 
   oStep.RollbackFailure = False 
   oStep.ScriptLanguage = "VBScript" 
   oStep.AddGlobalVariables = True 
   oStep.RelativePriority = 3 
   oStep.CloseConnection = False 
   oStep.ExecuteInMainThread = False 
   oStep.IsPackageDSORowset = False 
   oStep.JoinTransactionIfPresent = False 
   oStep.DisableStep = False 
   oStep.FailPackageOnError = False 
goPackage.Steps.Add oStep 
Set oStep = Nothing 
... 

Table 9.3 describes each package step option.

Table 9.3. Package Step Options

Option

Description

AddGlobalVariables

If True, allows the step to access global variables.

CloseConnection

If True, closes the connection when the step finishes.

IsPackageDSORowset

Must be True if the Transform Data Task returns a rowset.

DisableStep

Enables or disables a step.

FailPackage

If True, aborts the package if the step fails.

JoinTransactionIfPresent

If True, executes the task within the scope of the trans-action when the package is using transactions.

CommitSuccess

If True, commits data on step success.

RollbackFailure

If True, performs a rollback on step failure.

ExecuteInMainThread

Must be set to True if you are using a COM language, such asVB, that does not support free threading Write Custom Tasks or Execute Package Tasks while hooking into events.

Also must be True if a step or task uses ActiveX script code to call, via CreateObject, a COM object that is not free-threaded or if you are using an OLE DB provider that is not free-threaded.

Relative Priority

Determines the step priority relative to the rest of the package. If ExecuteInMainThread is True, the parameter is not used.

Defining Package Constraints

Unless otherwise constrained, package steps run in parallel, up to the limit specified by the package’s MaxConcurrentSteps property. To order execution, Precedence Constraints are added to a particular step. Precedence Constraints define conditions to be met before step execution. If a step has more than one Precedence Constraint, it will not begin until all its Precedence Constraints are satisfied.

For your package, DTS scripts Precedence Constraints as shown in Listing 9.6.

Example 9.6. Working with Package Constraints in Visual Basic

'------------- a precedence constraint for steps defined below 
Set oStep = goPackage.Steps("DTSStep_DTSDataPumpTask_1") 
Set oPrecConstraint = _ 
oStep.PrecedenceConstraints.New("DTSStep_DTSActiveScriptTask_1") 
   oPrecConstraint.StepName = "DTSStep_DTSActiveScriptTask_1" 
   oPrecConstraint.PrecedenceBasis = 0 
   oPrecConstraint.Value = 0 
oStep.precedenceConstraints.Add oPrecConstraint 
Set oPrecConstraint = Nothing 
... 

Table 9.4 describes some package constraint options.

Table 9.4. Package Constraint Options

Option

Description

PrecedenceBas

If 1, precedence is determined by the value returned by the step. If 0, precedence is based on step success or failure.

Value

Determines the conditions to be met:

4—Completion. Step executes after preceding step, regardless of status.

1—Success. Step executes only when the preceding step succeeds.

0—Failure. Step executes only when the preceding step fails.

Defining Package Tasks

The DTS Designer places the code that defines tasks and transformations in separate subroutines titled Task_Sub1, Task_Sub2, and Task_Sub3.

At this point the routines are executed to define the tasks transformations and add them to the appropriate steps (see Listing 9.7).

Example 9.7. Working with Tasks in Visual Basic

'------------------------------------------------------------------
' Create package tasks information 
'-----------------------------------------------------------------

'Task_Sub1 
'   task DTSTask__DTSActiveScriptTask_1 
'   (Prepare Spreadsheet) 
Call Task_Sub1( goPackage) 

'Task_Sub2 
'   task DTSTask__DTSDataPumpTask_1 
'   (Load Variance Report) 
Call Task_Sub2( goPackage) 
... 

Executing the Package

Finally, DTS scripts the package execution as shown in Listing 9.8.

Example 9.8. Executing the Package in Visual Basic

'----------------------------------------------------------------
' Save or execute package 
'----------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", "" 
goPackage.Execute 

goPackage.Uninitialize 
set goPackage = Nothing 
set goPackageOld = Nothing 

End Sub 

Uninitialize cleans up the package and releases all memory references.

If you want to save the package to the local SQL Server, uncomment the SaveToSQLServer line and provide the required login information.

Adding an ExecuteSQLTask

After the main procedure, DTS scripts package tasks. Each task is scripted as a separate subroutine. The Tasks collection defines package tasks. Each task defines a unit of work to be performed. Any task described in Chapter 4, “DTS Tasks,” and Chapter 5, “More DTS Tasks,” can be used. The CreateExcelReports package uses an ActiveScriptTask and a DataPumpTask.

Adding an ActiveScriptTask

Listing 9.9 shows the code DTS scripts to define the ActiveScriptTask used in your solution.

Example 9.9. Working with ActiveScriptTask in Visual Basic

'Task_Sub2 
'   DTSTask__DTSActiveScriptTask_1 (Prepare Spreadsheet) 
Public Sub Task_Sub2(ByVal goPackage As Object) 

Dim oTask As DTS.Task 
Dim oLookup As DTS.Lookup 

Dim oCustomTask2 As DTS.ActiveScriptTask 
Set oTask = goPackage.Tasks.New("DTSActiveScriptTask") 
Set oCustomTask2 = oTask.CustomTask 

   oCustomTask2.Name = "DTSTask_DTSActiveScriptTask_1" 
   oCustomTask2.Description = "Prepare Spreadsheet" 
   oCustomTask2.ActiveXScript = _ 
     "'**********************************" & vbCrLf & _ 
     "' Visual Basic ActiveX Script" & vbCrLf & _ 
     "'********************************" & vbCrLf & _ 
     "Function Main()" & vbCrLf & _&_ 
     " Dim xlApp"& vbCrLf &_ 
     " Set xlApp = & vbCrLf &_ 
     " CreateObject(""Excel.Application"")" & vbCrLf & _ 
     " xlApp.Workbooks.Open " & vbCrLf & _ 
     "   ""C:..Shipping Reports.xls""" & vbCrLf & _ 
     " xlApp.Run (""ResetReports"")" & vbCrLf & _ 
     " For each c in xlApp.Range(""ActualsMetaData"")" & vbCrLf & 
     " If c.Value = ""ActualsThroughMonth"" or c.Value = ""CurrentYear"" Then" & vbCrLf & 
     "   "DTSGlobalVariables(c.Value).Value = c.offset(0,1).Value" & vbCrLf & 
     " End If" vbCrLf & 
     " Next " vbCrLf & 
     " xlApp.ActiveWorkbook.Close True" & vbCrLf & _ 
     " xlApp.Quit" & vbCrLf & _ 
     " Set xlApp = Nothing" & vbCrLf & _ 
     " Main = DTSTaskExecResult_Success" & vbCrLf & _ 
     "End Function" 
   oCustomTask2.FunctionName = "Main" 
   oCustomTask2.ScriptLanguage = "VBScript" 
   oCustomTask2.AddGlobalVariables = True 

goPackage.Tasks.Add oTask 
Set oCustomTask2 = Nothing 
Set oTask = Nothing 

End Sub 

This procedure is not complicated. Create a task, set its task properties, and append the task to the package’s Tasks collection. The Lookup reference is scripted, but it is not used for this task. Steps and precedences have already been defined to execute the task, so no further work is required.

Table 9.5 describes some active script task options.

Table 9.5. Active Script Task Options

Option

Description

ScriptLanguage, FunctionName, and ActiveXScript

Define the task.

AddGlobalVariables

Allows the script to access the package’s global variables.

Adding a DataPumpTask

The DataPumpTask scripted for your solution defines an instance of the Transform Data Task that implements a data pump object to transform data as it is moved from source to destination.

This task is more complex, consisting of the following steps:

  1. Define the DataPumpTask options.

  2. Define the task’s transformation script.

  3. Define the task’s transformation script columns.

  4. Define the task’s ActiveX script.

  5. Define the task’s transformation script lookup.

  6. Add the DataPumpTask to the package tasks object.

Defining the DataPumpTask Options

Listing 9.10 shows the code that defines the DataPumpTask options.

Example 9.10. Working with the DataPumpTask in Visual Basic

'Task_Sub3 
'   DTSTask__DTSDataPumpTask_1 (Load Actuals) 
Public Sub Task_Sub3(ByVal goPackage As Object) 

Dim oTask As DTS.Task 
Dim oLookup As DTS.Lookup 

Dim oCustomTask3 As DTS.DataPumpTask2 
Set oTask = goPackage.Tasks.New("DTSDataPumpTask") 
Set oCustomTask3 = oTask.CustomTask 

   oCustomTask3.Name = "DTSTask_DTSDataPumpTask_1" 
   oCustomTask3.Description = "Load Actuals" 
   oCustomTask3.SourceConnectionID = 2 
   oCustomTask3.SourceObjectName = "Budget" 
   oCustomTask3.DestinationConnectionID = 3 
   oCustomTask3.DestinationObjectName = "Actuals" 
   oCustomTask3.ProgressRowCount = 1000 
   oCustomTask3.MaximumErrorCount = 0 
   oCustomTask3.FetchBufferSize = 1 
   oCustomTask3.UseFastLoad = True 
   oCustomTask3.InsertCommitSize = 0 
   oCustomTask3.ExceptionFileColumnDelimiter = "|" 
   oCustomTask3.ExceptionFileRowDelimiter = vbCrLf 
   oCustomTask3.AllowIdentityInserts = False 
   oCustomTask3.FirstRow = "0" 
   oCustomTask3.LastRow = "0" 
   oCustomTask3.FastLoadOptions = 2 
   oCustomTask3.ExceptionFileOptions = 1 
   oCustomTask3.DataPumpOptions = 0 

Table 9.6 describes the DataPumpTask options.

Table 9.6. DataPumpTask Options

Option

Description

SourceConnectionId and SourceObjectName

Identify the source table. SourceSQLStatement can be used instead of SourceObjectName when you want to use a SQL statement against the source.

DestinationConnectionId and DestinationObjectName

Identify the destination table.

ProgressRowCount

Sets the number of rows between event notifications.

MaximumErrorCount

Sets the number of errors the task can tolerate before giving up. The default, 0, causes termination after the first error.

FetchBufferSize

Sets the number of rows the data provider will return each fetch when retrieving nonbinary data, and adjusts the size to balance memory and the overhead that calls incur. The default value, 100, works well in most cases.

InsertCommitSize

Similar to bcp batch size; sets rows processed between each commit.

UseFastLoad

If True, DTS will use the OLE DB interface IrowsetFastLoad. The data path inside SQL Server will be the same as when you are using bcp and bulk insert.

AllowIdentityIinserts

If True, allows you to insert user-provided values for identity columns.

FirstRow and LastRow

Allows processing of a subset of the source. FirstRow=11 would exclude the first 10 rows, and LastRow=99 would stop at row 99. Use 0, 0 to process all rows.

ExceptionFileName

If you allow errors, trapped rows are logged to this file, using the delimiters specified by ExceptionFileRowDelimiter and ExceptionFileColumnDelimiter.

Defining the DataPumpTask’s Transformation Script

After the task is initialized, DTS scripts the transformations to perform. Each transformation is scripted in a separate subroutine (see Listing 9.11).

Example 9.11. Adding a Transformation Script to the DataPumpTask from Visual Basic

Dim oTransformation As DTS.Transformation2 
   Dim oTransProps as DTS.Properties 
   Dim oColumn As DTS.Column 
   Set oTransformation = _ 
   oCustomTask3.Transformations.New("DTSPump.DataPumpTransformScript") 
   oTransformation.Name = "Copy Actuals" 
   oTransformation.TransformFlags = 63 
   oTransformation.ForceSourceBlobsBuffered = 0 
   oTransformation.ForceBlobsInMemory = False 
   oTransformation.InMemoryBlobSize = 1048576 
   oTransformation.TransformPhases = 4 

In general, the default transformation options will work. Discussion of specific options is beyond the scope of this book. See Books on Line (BOL) if you require further clarification.

Defining the DataPumpTask’s Transformation Script Columns

Although a task can access the source and destination rowsets, the transformation can access columns only you assign. Next, DTS scripts the source and destination for any column processed. Listing 9.12 shows the code for the Product column.

Example 9.12. Working with Transformation Script Column Definitions for the Product Data Field in Visual Basic

'Define Source Columns 
Set oColumn = _ 
oTransformation.SourceColumns.New("Product" , 1) 
   oColumn.Name = "Product" 
   oColumn.Ordinal = 1 
   oColumn.Flags = 102 
   oColumn.Size = 255 
   coColumn.DataType = 130 
   oColumn.Precision = 0 
   oColumn.NumericScale = 0 
   oColumn.Nullable = True 
   oTransformation.SourceColumns.Add oColumn 
   Set oColumn = Nothing 
... 
'Define Destination Columns 
Set oColumn = 
oTransformation.DestinationColumns.New("Product" , 1) 
   oColumn.Name = "Product" 
   oColumn.Ordinal = 1 
   oColumn.Flags = 102 
   oColumn.Size = 255 
   oColumn.DataType = 130 
   oColumn.Precision = 0 
   oColumn.NumericScale = 0 
   oColumn.Nullable = True 
   oTransformation.DestinationColumns.Add oColumn 
Set oColumn = Nothing 
... 

Table 9.7 describes column options.

Table 9.7. Column Options

Option

Description

Name

Defines the column name.

Ordinal

Sets the column number.

DataType and Flags

Correspond to the OLE DB data type (DBTYPE) and column flags (DBCOLUMNFLAGS).

Size

Sets the maximum length.

Precision and NumericScale

Defines decimal and numeric data types.

Nullable

If True, allows the column to accept null values.

Defining the DataPumpTask’s Transformation Script ActiveX Script

Next, DTS scripts an ActiveX script to process and transform the data (see Listing 9.13).

Example 9.13. Using Visual Basic to Add the Transformation Script’s ActiveX Script

Set oTransProps = oTransformation.TransformServerProperties 
oTransProps("Text") = _ 
   "'***************************************" & _ 
   "' Visual Basic Transformation Script" & _ 
   "'***************************************" & _ 
   "' Copy each source to the destination "& _ 
   "Function Main()" & _ 
      "   Dim I,, CMActuals "& vbCrLf & _ 
      "   DTSDestination(""Product"") = " & _ 
      "         DTSSource(""Product"")" & vbCrLf & 
      "   'Load Actuals for each month" & _ vbCrLf & 
      "   For i = 1 to DTSGlobalVariables(" & _ 
      """ActualsThroughMonth"").Value" & _ vbCrLf & 
      "     DTSDestination(i+1) = " & _ 
      "     DTSLookups(" & _ 
      "    ""GetActualsCurrentMonth"").Execute (" & _ 
      "        DTSSource(""Product""),) " & 
" DTSGlobalVariables(""CurrentYear"").Value,i) " __& vbCrLf & 
      "  Next" & vbCrLf & _ 
      "  DTSDestination(""Full Year"") = " & _ 
      "  DTSLookups(""GetActualsYTD"").Execute(" & _ 
      "  DTSSource(""Product""), " & _ 
      "  DTSGlobalVariables(
      "   ""CurrentYear"").Value), " & 
"      DTSGlobalVariables(
      "   ""ActualsThroughMonth"").Value) " & vbCrLf &_ 
      "   Main = DTSTransformStat_OK " & _ vbCrLf & 
      "End Function" 
oTransProps("Language") = "VBScript" 
oTransProps("FunctionEntry") = "Main" 

Set oTransProps = Nothing 

oCustomTask3.Transformations.Add oTransformation 
   Set oTransformation = Nothing 

Note that lookups, which are yet to be defined, have been forward-referenced.

Defining the DataPumpTask’s Transformation Script Lookup

The DataPumpTask, like the DataDrivenQueryTask, supports Select statements and lookups, which you can use to perform parameterized queries. The Lookup feature lets a transformation retrieve data from locations other than the immediate source or destination row being transformed. Listing 9.14 shows sample code.

Example 9.14. Using Visual Basic to Script the Lookup Query Used in a DataPumpTask

'------- A Lookup is defined here 
Set oLookup = oCustomTask3.Lookups.New("GetActualCurrentMonth") 
  oLookup.Name = "GetActualCurrentMonth" 
  oLookup.ConnectionID = 1 
  oLookup.Query = _ 
"SELECT SUM([OrderDetails].[Quantity]) AS CumTotal" & 
"FROM ([Order] INNER JOIN " & 
"  OrderDetails ON [Order].[OrderID] = " & 
"  [OrderDetails].[OrderID]) INNER JOIN " & 
"    Product ON [OrderDetails].[ProductID] = " & 
"  [Product].[ProductID] " & 
" WHERE (([Product].[ProdName] = ?) AND " & 
"  (YEAR([Order].[ShippedDate]) = ?) AND " & 
"  (MONTH([Order].[ShippedDate]) = ?) "; 
  oLookup.MaxCacheRows = 0 

  oCustomTask3.Lookups.Add oLookup 
  Set oLookup = Nothing 

About Lookups

Lookups are associated with a particular task and can’t be shared.

Table 9.8 describes the lookup options.

Table 9.8. Lookup Options

Option

Description

ConnectionId and Query

Define the actual lookup. Any question marks in the query are replaced with parameters when the lookup is performed.

MaxCacheRows

Improves lookup performance. Lookups can use a cache to store values from previous executions. Use 0 for no caching. The default, 100, works well in most cases.

Saving the DataPumpTask to the Package’s Tasks Collection

Finally, the task is appended to the package’s Tasks collection (see Listing 9.15).

Example 9.15. Scripted DataPumpTask Append

goPackage.Tasks.Add oTask 
Set oCustomTask3 = Nothing 
Set oTask = Nothing 

End Sub 

ExecuteSQLTask

ExecuteSQLTask is another common task used in many DTS packages. This task is very straightforward and is much like ActiveScriptTask. Listing 9.16 shows the code scripted by DTS to define the ExecuteSQLTask.

Example 9.16. Working with the ExecuteSQLTask in Visual Basic

'Task_Sub1 
'  DTSTask__DTSExecuteSQLTask_1 (Reports Get ActualsThroughMonth) 
Public Sub Task_Sub1(ByVal goPackage As Object) 

Dim oTask As DTS.Task 
Dim oLookup As DTS.Lookup 

Dim oCustomTask1 As DTS.ExecuteSQLTask2 
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask") 
Set oCustomTask1 = oTask.CustomTask 

   oCustomTask1.Name = "DTSTask_DTSExecuteSQLTask_1" 
   oCustomTask1.Description = "Reports Get ActualsThroughMonth" 
   oCustomTask1.SQLStatement = _ 
     "SELECT  Value" & vbCrLf & _ 
     "FROM     ActualsMetaData" & vbCrLf & _ 
     "WHERE  (VariableName " & vbCrLf & _ 
     "     = 'ActualsThroughMonth')" 
   oCustomTask1.ConnectionID = 3 
   oCustomTask1.CommandTimeout = 0 
   oCustomTask1.InputGlobalVariableNames = """""" 
   oCustomTask1.OutputGlobalVariableNames = _ 
      """ActualsThroughMonth""" 
   oCustomTask1.OutputAsRecordset = False 

goPackage.Tasks.Add oTask 
Set oCustomTask1 = Nothing 
Set oTask = Nothing 

End Sub 

This code is not very complicated. Create a task, set its properties, and append it to the package’s Tasks collection. The Lookup reference is scripted, but it is not used for this task.

Table 9.9 describes some task options.

Table 9.9. Task Options

Option

Description

ConnectionID, CommandTimeout, and SQLStatement

Define the data source and statement to run.

InputGlobalVariableNames and OutputGlobalVariableNames

Map statement parameters to global variables.

OutputAsRecordSet

Must be True if the task returns a recordset.

Building Your Solution

Now that you understand how the package is scripted, you can begin to build a solution.

At this point, you should have already done the following:

  1. Started the DTS Designer.

  2. Opened C:DTS2000DataCreateExcelReport.dts [4] .

  3. Saved the .dts file as a VB script (C:DTS2000DataCreateExcelReport.bas).

  4. Created a sample managers’ directory, C:TempDTS2000Manager.

  5. Copied C:DTS2000DataShippingBudget.xls into this directory.

  6. Started VB and begun a new standard .exe.

  7. Added a reference for the DTS package object.

  8. Added a file reference to the script generated by DTS (C:DTS2000 DataCreateExcelReport.bas).

  9. Saved the project (C:DTS2000DataCreateExcelReport.vbp) and compiled it.

  10. This step is required to ensure that the App.Path works correctly during debugging.

To complete the solution, you must do the following:

  1. Modify the VB application to prompt for the location of ShippingBudget.xls, copy ShippingReports.xls to the same directory as the budget, and run the DTS package to update the copy of ShippingReports.xls.

  2. Run the application, and when prompted for the budget location, specify C:TempDTS2000ManagerShippingBudget.xls.

  3. Use Excel to view the generated report (C:TempDTS2000Manager ShippingReports.xls).

Modifying the VB Application

Next, you modify the beginning of the DTS script to prompt for the location of ShippingBudget.xls, copy ShippingReports.xls to the same directory as the budget, and run the DTS package to update the copy of ShippingReports.xls. Listing 9.17 shows the final changes.

Example 9.17. Application Customizations

Option Explicit 
Public goPackageOld As New DTS.Package 
Public goPackage As DTS.Package2 

'File References 
Dim msInventory As String 
Dim msReports As String 
Dim msBudget As String 
Dim msPath 

Private Sub Main() 
    'Find Inventory 
    msInventory = App.Path + "Manufacturing.mdb" 

    'Prompt for Budget 
    msBudget = InputBox("Enter", "Budget File", _ 
        App.Path + "" + "Shipping Budget.xls") 

    'Copy Report (if required) 
    msPath = msBudget 
    While Len(msPath) > 1 And Right(msPath, 1) <> "" __ 
      And Right(msPath, 1) <> ":" 
        msPath = Left(msPath, Len(msPath) - 1) 
    Wend 
msReports = msPath + "Shipping Reports.xls" 
If msPath + "Shipping Reports.xls" <> App.Path + "Shipping Reports.xls" __ 
  And Dir(msPath + "Shipping Reports.xls") Then 
    FileCopy App.Path + "Shipping Reports.xls", _ 
        msPath + "Shipping Reports.xls" 
End If 

DTS explicitly scripts file locations in property assignments or property statements. The way this script is written makes it impossible to change them programmatically at run time. You must use the VB editor to make replacements, as follows:

  1. Replace C:DTS2000DataInventory.mdb with “ + msInventory + “.

  2. Replace C:DTS2000DataShippingReports.xls with “ + msReports + “.

  3. Replace C:DTS2000DataShippingBudget.xls with “ + msBudget+ “.

  4. Compile and test.

Running the Application

Use VB’s Run command to start the application. When prompted for the location of your budget as shown in Figure 9.5, specify C:TempDTS2000Manager ShippingBudget.xls.

VB DTS budget location.

Figure 9.5. VB DTS budget location.

Viewing the Output

The application will have copied C:DTS2000DATA ShippingReports. xls [5] to C:TempDTS2000ManagerShippingReports.xls and run the DTS package to merge it with C:TempDTS2000Manager ShippingBudget.xls.

Use Excel to view the generated reports (C:TempDTS2000Manager ShippingReports.xls).

Summary

In this chapter, you saw that in some situations, the DTS Wizard or the DTS Designer does not provide the functionality needed.

For one such problem, you examined a quick and efficient solution using VB. You designed a package using DTS Wizard, saved it to a VB script, and then include the script in a VB project where you could provide a solution that extended and complemented the DTS engine.

Chapter 10, “Interacting with a Package Using Visual Basic,” builds on this topic, covering more complex and interesting problems.



[1] All data files and scripts used in the book are available to be downloaded from http://www.magenic.com/publications or http://www.newriders.com. The self-extracting ZIP file will create a directory structure starting with DTS2000. You can extract the files to any drive or directory, but all file references in this book will be based on C:DTS2000.

[2] If you want to follow along, all data files and scripts used in the book are available to be downloaded from http://www.magenic.com/publications or http://www.newriders.com.

[3] If you want to follow along, all data files and scripts used in the book are available to be downloaded from http://www.magenic.com/publications or http://www.newriders.com.

[4] If you want to follow along, all data files and scripts used in the book are available to be downloaded from http://www.magenic.com/publications or http://www.newriders.com.

[5] If you want to follow along, all data files and scripts used in the book are available to be downloaded from http://www.magenic.com/publications or http://www.newriders.com.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.102.198