my package.bas

This project is developed for school management system in vb and sql server 2000. All source code in

Set oCustomTask2 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)

        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DirectCopyXform"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
                oTransformation.TransformPhases = 4
                
                Set oColumn = oTransformation.SourceColumns.New("COURSE_ID", 1)
                        oColumn.Name = "COURSE_ID"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 122
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("COURSE_NAME", 2)
                        oColumn.Name = "COURSE_NAME"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 106
                        oColumn.Size = 50
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("DESCRIPTION", 3)
                        oColumn.Name = "DESCRIPTION"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 106
                        oColumn.Size = 50
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("SEMESTER", 4)
                        oColumn.Name = "SEMESTER"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 122
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("COURSE_ID", 1)
                        oColumn.Name = "COURSE_ID"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 122
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("COURSE_NAME", 2)
                        oColumn.Name = "COURSE_NAME"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 106
                        oColumn.Size = 50
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("DESCRIPTION", 3)
                        oColumn.Name = "DESCRIPTION"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 106
                        oColumn.Size = 50
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("SEMESTER", 4)
                        oColumn.Name = "SEMESTER"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 122
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

        Set oTransProps = oTransformation.TransformServerProperties

                
        Set oTransProps = Nothing

        oCustomTask2.Transformations.Add oTransformation
        Set oTransformation = Nothing

End Sub

'------------- define Task_Sub3 for task Create Table [College].[dbo].[EMPLOYEE] Task (Create Table [College].[dbo].[EMPLOYEE] Task)
Public Sub Task_Sub3(ByVal goPackage As Object)

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

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

        oCustomTask3.Name = "Create Table [College].[dbo].[EMPLOYEE] Task"
        oCustomTask3.Description = "Create Table [College].[dbo].[EMPLOYEE] Task"
        oCustomTask3.SQLStatement = "CREATE TABLE [College].[dbo].[EMPLOYEE] (" & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[EmployeeID] int NOT NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[LastName] nvarchar (20) NOT NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[FirstName] nvarchar (10) NOT NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[Title] nvarchar (30) NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[TitleOfCourtesy] nvarchar (25) NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[BirthDate] smalldatetime NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[HireDate] smalldatetime NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[Address] nvarchar (60) NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[City] nvarchar (15) NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[HomePhone] nvarchar (24) NULL, " & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & "[Notes] ntext NULL" & vbCrLf
        oCustomTask3.SQLStatement = oCustomTask3.SQLStatement & ")"
        oCustomTask3.ConnectionID = 4
        oCustomTask3.CommandTimeout = 0
        oCustomTask3.OutputAsRecordset = False
        
goPackage.Tasks.Add oTask
Set oCustomTask3 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub4 for task Copy Data from EMPLOYEE to [College].[dbo].[EMPLOYEE] Task (Copy Data from EMPLOYEE to [College].[dbo].[EMPLOYEE] Task)
Public Sub Task_Sub4(ByVal goPackage As Object)

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

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

        oCustomTask4.Name = "Copy Data from EMPLOYEE to [College].[dbo].[EMPLOYEE] Task"
        oCustomTask4.Description = "Copy Data from EMPLOYEE to [College].[dbo].[EMPLOYEE] Task"
        oCustomTask4.SourceConnectionID = 3
        oCustomTask4.SourceSQLStatement = "select `EmployeeID`,`LastName`,`FirstName`,`Title`,`TitleOfCourtesy`,`BirthDate`,`HireDate`,`Address`,`City`,`HomePhone`,`Notes` from `EMPLOYEE`"
        oCustomTask4.DestinationConnectionID = 4
        oCustomTask4.DestinationObjectName = "[College].[dbo].[EMPLOYEE]"
        oCustomTask4.ProgressRowCount = 1000
        oCustomTask4.MaximumErrorCount = 0
        oCustomTask4.FetchBufferSize = 1
        oCustomTask4.UseFastLoad = True
        oCustomTask4.InsertCommitSize = 0
        oCustomTask4.ExceptionFileColumnDelimiter = "|"
        oCustomTask4.ExceptionFileRowDelimiter = vbCrLf
        oCustomTask4.AllowIdentityInserts = False
        oCustomTask4.FirstRow = 0
        oCustomTask4.LastRow = 0
        oCustomTask4.FastLoadOptions = 2
        oCustomTask4.ExceptionFileOptions = 1
        oCustomTask4.DataPumpOptions = 0
        
Call oCustomTask4_Trans_Sub1(oCustomTask4)
                
                
goPackage.Tasks.Add oTask
Set oCustomTask4 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask4_Trans_Sub1(ByVal oCustomTask4 As Object)

        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask4.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DirectCopyXform"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
                oTransformation.TransformPhases = 4
                
                Set oColumn = oTransformation.SourceColumns.New("EmployeeID", 1)
                        oColumn.Name = "EmployeeID"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 90
                        oColumn.Size = 0
                        oColumn.DataType = 3
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("LastName", 2)
                        oColumn.Name = "LastName"
                        oColumn.Ordinal = 2
                        oColumn.Flags = 74
                        oColumn.Size = 20
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("FirstName", 3)
                        oColumn.Name = "FirstName"
                        oColumn.Ordinal = 3
                        oColumn.Flags = 74
                        oColumn.Size = 10
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = False
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("Title", 4)
                        oColumn.Name = "Title"
                        oColumn.Ordinal = 4
                        oColumn.Flags = 106
                        oColumn.Size = 30
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("TitleOfCourtesy", 5)
                        oColumn.Name = "TitleOfCourtesy"
                        oColumn.Ordinal = 5
                        oColumn.Flags = 106
                        oColumn.Size = 25
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("BirthDate", 6)
                        oColumn.Name = "BirthDate"
                        oColumn.Ordinal = 6
                        oColumn.Flags = 122
                        oColumn.Size = 0
                        oColumn.DataType = 7
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.SourceColumns.New("HireDate", 7)
                        oColumn.Name = "HireDate"
                        oColumn.Ordinal = 7
                        oColumn.Flags = 122
                        oColumn.Size = 0
                        oColumn.DataType = 7
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True