Sample Applications Using DTS

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

End Sub

Previous Next