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.
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.
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:
Copy ShippingBudget.xls to his work area and customize it as required.
Run the application.
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:
Start the DTS Designer by opening C:DTS2000Data CreateExcelReports.dts [1] and saving it as a VB .bas file.
Create a sample managers directory (C:TempDTS2000Manager).
Copy C:DTS2000DataShippingBudget.xls [2] into this directory.
Start VB, and begin a new standard .exe, as follows:
Add a reference for the DTS package.
Add a file reference to the script generated by DTS.
Save the project and compile. (This step is required to ensure that the
App.Pathworks correctly during debugging.)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.
Run the application, and when prompted for the budget location, specify C:TempDTS2000ManagerShippingBudget.xls.
Use Excel to view the generated report (C:TempDTS2000Manager ShippingReports.xls).
The first step in implementing your solution is to save the CreateExcelReport package as a VB .bas file by following these steps.
Start Enterprise Manager, and select your server.
Right-click Data Transformation Services, select Open Package, and load C:DTS2000PackagesCreateExcelReport.dts [3] .
Use the Save As menu option to open the Save DTS Package dialog box (see Figure 9.2).
Type CreateExcelReport in the Package Name text box.
Ignore the password options, because they have no effect on the VB file.
From the Location drop-down menu, choose Visual Basic File.
Type C:DTS2000PackagesCreateExcelReport in the File Name text box.
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.
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).
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.
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.
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.
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.
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.
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.
While working on your VB application, use the diagram and the object browser in VB to familiarize yourself with the DTS object model.
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).
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 |
|---|---|
|
|
Describe the package. |
|
|
Sets the Windows 32 process priority. |
|
|
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. |
|
|
If |
|
|
If |
|
|
|
|
|
If |
|
|
Sets the package transaction’s isolation level. The default is |
|
|
If |
|
|
If |
|
|
Enable logging to an instance of SQL Server 2000, identify the server, and provide authentication information. If |
|
|
Provides information about the application that creates the package. |
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 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 |
|---|---|
|
|
If |
|
|
If |
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.
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 |
|---|---|
|
|
If |
|
|
If |
|
|
Must be |
|
|
Enables or disables a step. |
|
|
If |
|
|
If |
|
|
If |
|
|
If |
|
|
Must be set to Also must be |
|
|
Determines the step priority relative to the rest of the package. If |
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 |
|---|---|
|
|
If |
|
|
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. |
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) ...
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.
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.
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.
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:
Define the
DataPumpTaskoptions.Define the task’s transformation script.
Define the task’s transformation script columns.
Define the task’s ActiveX script.
Define the task’s transformation script lookup.
Add the DataPumpTask to the package tasks object.
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 |
|---|---|
|
|
Identify the source table. |
|
|
Identify the destination table. |
|
|
Sets the number of rows between event notifications. |
|
|
Sets the number of errors the task can tolerate before giving up. The default, 0, causes termination after the first error. |
|
|
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, |
|
|
Similar to bcp batch |
|
|
If |
|
|
If |
|
|
Allows processing of a subset of the source. |
|
|
If you allow errors, trapped rows are logged to this file, using the delimiters specified by |
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.
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 |
|---|---|
|
|
Defines the column name. |
|
|
Sets the column number. |
|
|
Correspond to the OLE DB data type (DBTYPE) and column flags (DBCOLUMNFLAGS). |
|
|
Sets the maximum length. |
|
|
Defines decimal and numeric data types. |
|
|
If |
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.
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 Table 9.8 describes the lookup options.
Table 9.8. Lookup Options
|
Option |
Description |
|---|---|
|
|
Define the actual lookup. Any question marks in the query are replaced with parameters when the lookup is performed. |
|
|
Improves lookup performance. Lookups can use a cache to store values from previous executions. Use |
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 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.
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:
Started the DTS Designer.
Opened C:DTS2000DataCreateExcelReport.dts [4] .
Saved the .dts file as a VB script (C:DTS2000DataCreateExcelReport.bas).
Created a sample managers’ directory, C:TempDTS2000Manager.
Copied C:DTS2000DataShippingBudget.xls into this directory.
Started VB and begun a new standard .exe.
Added a reference for the DTS package object.
Added a file reference to the script generated by DTS (C:DTS2000 DataCreateExcelReport.bas).
Saved the project (C:DTS2000DataCreateExcelReport.vbp) and compiled it.
This step is required to ensure that the
App.Pathworks correctly during debugging.
To complete the solution, you must do the following:
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.
Run the application, and when prompted for the budget location, specify C:TempDTS2000ManagerShippingBudget.xls.
Use Excel to view the generated report (C:TempDTS2000Manager ShippingReports.xls).
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:
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.
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).
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.