On the CD-ROM accompanying this book are two sample applications in Visual Basic that are based on DTS packages that were created and saved to a Visual Basic file. The code samples are located in the "SQL Server 2000 to DB2 Data Transfer" folders. These samples use DTS to import the Northwind sample database and data tables included with SQL Server 2000 and export these data tables to IBM DB2. The Visual Basic source code exported from the DTS Import/Export Wizard has been modified with an initial dialog box that requests a pointer to UDL file to the SQL Server used for import and a UDL file to the DB2 system targeted for the export.
One sample directory contains a version of the source code and application using Visual Basic 6. The other directory contains a version of the code using Visual Basic.NET for the application. The sample code illustrates what a DTS package looks like when it is exported as Visual Basic source code for use in an application. The simple modifications to the exported DTS package show how easy it is to use DTS for developing custom applications for data integration.
An excerpt from these DTS samples in Visual Basic is listed below. The sample code illustrates the use of the DTS COM objects supported by SQL Server 2000 in an application. Most of the source code in these samples was generated by the DTS Import/Export Wizard. The sample code creates a new package then adds connection objects, DTS step objects, and DTS task objects and sets properties for these objects.
The following excerpt from the sample code defines the package, connections, and tasks in a DTS package.
Public goPackage As DTS.Package2 Public Sub Main() Set goPackage = New DTS.Package2 goPackage.Name = "NWSQLS2DB2-Data" goPackage.Description = "Northwind - SQL Server to DB2" ' need to set other package properties ' create package connection information Dim oConnection As DTS.Connection2 '------------- add new connection defined below for DB2. 'For security purposes, the password is never scripted Set oConnection = goPackage.Connections.NewDataLink(App.Path & "\IBM-DB2.udl") oConnection.Name = "DB2 Connection" oConnection.ID = 2 oConnection.ConnectionTimeout = 60 oConnection.UserID = frmPackage.txtDB2User.Text oConnection.Password = frmPackage.txtDB2Password.Text ' Need to set other connection properties for the UDL to DB2 goPackage.Connections.Add oConnection Set oConnection = Nothing '------------- a new connection defined below for SQL Server. Set oConnection = goPackage.Connections.NewDataLink(App.Path & "\SQLS2K.udl") oConnection.Name = "SQL Server Northwind DB" oConnection.ID = 1 oConnection.ConnectionTimeout = 60 oConnection.UserID = frmPackage.txtSQLUser.Text oConnection.Password = frmPackage.txtSQLPassword.Text ' Need to set other connection properties for the UDL to SQL Server goPackage.Connections.Add oConnection Set oConnection = Nothing ' 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 = "Copy Data from Categories to Categories Step" oStep.Description = "Copy Data from Categories to Categories Task" oStep.ScriptLanguage = "VBScript" oStep.AddGlobalVariables = True oStep.CloseConnection = False oStep.ExecuteInMainThread = False oStep.IsPackageDSORowset = False oStep.JoinTransactionIfPresent = False oStep.DisableStep = False oStep.FailPackageOnError = False ' Need to set other step properties for DTS Step 2 goPackage.Steps.Add oStep Set oStep = Nothing ' Need to add and configure properties for other steps ' Create package tasks information '-----call Task_Sub1 for task Copy Data from Categories ' to Categories Task (Copy Data from Categories to Categories Task) Call Task_Sub1(goPackage) ' Need to call other tasks ' Save or execute package ' To save a package instead of executing it, comment out the ' executing package lines and uncomment the saving package line ' goPackage.SaveToSQLServer "(local)", "sa", "" goPackage.Execute TracePackageError goPackage goPackage.UnInitialize Set goPackage = Nothing End Sub
The following excerpt from the sample code defines one of the subtasks in a DTS package.
'------------- define Task_Sub1 for task Copy Data from Categories to Categories Task (Copy Data from Categories to Categories Task) Public Sub Task_Sub1(ByVal goPackage As Object) Dim oTask As DTS.Task Dim oCustomTask1 As DTS.DataPumpTask2 Set oTask = goPackage.Tasks.New("DTSDataPumpTask") oTask.Name = "Copy Data from Categories to Categories Task" Set oCustomTask1 = oTask.CustomTask oCustomTask1.Name = "Copy Data from Categories to Categories Task" oCustomTask1.Description = "Copy Data from Categories to Categories Task" oCustomTask1.SourceConnectionID = 1 oCustomTask1.SourceSQLStatement = _ "select [CategoryID],[CategoryName],[Description],NULL AS [Picture] _ from [Northwind].[dbo].[Categories]" oCustomTask1.DestinationConnectionID = 2 oCustomTask1.DestinationObjectName = "CATEGORIES" ' Need to configure other properties for this task Call oCustomTask1_Trans_Sub1(oCustomTask1) goPackage.Tasks.Add oTask Set oCustomTask1 = Nothing Set oTask = Nothing End Sub
The following sample code defines one of the transformation subtasks in a DTS package.
Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object) Dim oTransformation As DTS.Transformation2 Dim oTransProps As DTS.Properties Dim oColumn As DTS.Column Set oTransformation = _ CustomTask1.Transformations.New("DTSPump.DataPumpTransformCopy") oTransformation.Name = "DTSTransformation__1" oTransformation.TransformFlags = 63 oTransformation.ForceSourceBlobsBuffered = 0 oTransformation.ForceBlobsInMemory = False oTransformation.InMemoryBlobSize = 1048576 oTransformation.TransformPhases = 4 Set oColumn = oTransformation.SourceColumns.New("Picture", 1) oColumn.Name = "Picture" oColumn.Ordinal = 1 oColumn.Flags = 112 oColumn.Size = 0 oColumn.DataType = 3 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = True oTransformation.SourceColumns.Add oColumn Set oColumn = Nothing ' Need to add and configure properties for other source columns Set oColumn = oTransformation.DestinationColumns.New("PICTURE", 1) oColumn.Name = "PICTURE" oColumn.Ordinal = 1 oColumn.Flags = 116 oColumn.Size = 254 oColumn.DataType = 129 oColumn.Precision = 0 oColumn.NumericScale = 0 oColumn.Nullable = True oTransformation.DestinationColumns.Add oColumn Set oColumn = Nothing ' Need to add and configure properties for other destination columns Set oTransProps = oTransformation.TransformServerProperties Set oTransProps = Nothing oCustomTask1.Transformations.Add oTransformation Set oTransformation = Nothing