DTS to VB

[Lawrence 007]

Can someone help me please.

I am having issues with the following code. This is the first time I am doing this, so it is Brand new to me. Sorry if I look and sound like an idiot.

I am having issues with 'DTS.OleDBProperties' cannot be indexed because it has no default property at the following line :oConnection.ConnectionProperties

I used DTS wizard to create my vb code and I am following this page: http://msdn2.microsoft.com/en-us/library/aa176248(SQL.80).aspx

Can someone please guide me in the right direction.
Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "Test"
goPackage.Description = "Casier"
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


Dim oConnProperty As DTS.OleDBProperty

Dim oConnection as DTS.Connection2
Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("Initial Catalog") = "EndofY"
oConnection.ConnectionProperties("Data Source") = "(local)"
oConnection.ConnectionProperties("Application Name") = "DTS Import/Export Wizard"

oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(local)"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "EndofY"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False

'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing
Set oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data Source") = ""
oConnection.ConnectionProperties("Mode") = 3
oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = ","
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = False
oConnection.ConnectionProperties("Column Names") = "HQID,LastUpdated,Number,StoreID,ID,Name,Password, FloorLimit,ReturnLimit,CashDrawerNumber,SecurityLe vel,Privileges,EmailAddress,FailedLogonAttempts,DB TimeStamp,MaxOverShortAmount,MaxOverShortPercent,O verShortLimitType,AutoID,Telephone"
oConnection.ConnectionProperties("Number of Column") = 20
oConnection.ConnectionProperties("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "00100110000010000001"
oConnection.ConnectionProperties("Max characters per delimited column") = 8000
oConnection.ConnectionProperties("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "00000000000000000000"

oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = ""
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint
Set oStep = goPackage.Steps.New

oStep.Name = "Copy Data from Results to Step"
oStep.Description = "Copy Data from Results to Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copied data in table "
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
Call Task_Sub1( goPackage )
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
set goPackage = Nothing

set goPackageOld = Nothing

End Sub

Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer

For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next i

End Sub
Public Sub Task_Sub1(ByVal goPackage As Object)

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

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "Copied data in table "
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copied data in table "
oCustomTask1.Description = "Copied data in table "
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceSQLStatement = "Select * from Cashier"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1( oCustomTask1 )


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

End Sub

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 = oCustomTask1.Transformations.New("DTS.DataPumpTran sformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

Lawrence