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.OleDBPrope rties' cannot be indexed because it has no default property at the following line :oConnection.Co nnectionPropert ies
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.Descr iption = "Casier"
goPackage.Write CompletionStatu sToNTEventLog = False
goPackage.FailO nError = False
goPackage.Packa gePriorityClass = 2
goPackage.MaxCo ncurrentSteps = 4
goPackage.Linea geOptions = 0
goPackage.UseTr ansaction = True
goPackage.Trans actionIsolation Level = 4096
goPackage.AutoC ommitTransactio n = True
goPackage.Repos itoryMetadataOp tions = 0
goPackage.UseOL EDBServiceCompo nents = True
goPackage.LogTo SQLServer = False
goPackage.LogSe rverFlags = 0
goPackage.FailP ackageOnLogFail ure = False
goPackage.Expli citGlobalVariab les = False
goPackage.Packa geType = 0
Dim oConnProperty As DTS.OleDBProper ty
Dim oConnection as DTS.Connection2
Set oConnection = goPackage.Conne ctions.New("SQL OLEDB")
oConnection.Con nectionProperti es("Integrated Security") = "SSPI"
oConnection.Con nectionProperti es("Persist Security Info") = True
oConnection.Con nectionProperti es("Initial Catalog") = "EndofY"
oConnection.Con nectionProperti es("Data Source") = "(local)"
oConnection.Con nectionProperti es("Applicatio n Name") = "DTS Import/Export Wizard"
oConnection.Nam e = "Connection 1"
oConnection.ID = 1
oConnection.Reu sable = True
oConnection.Con nectImmediate = False
oConnection.Dat aSource = "(local)"
oConnection.Con nectionTimeout = 60
oConnection.Cat alog = "EndofY"
oConnection.Use TrustedConnecti on = True
oConnection.Use DSL = False
'oConnection.Pa ssword = "<put the password here>"
goPackage.Conne ctions.Add oConnection
Set oConnection = Nothing
Set oConnection = goPackage.Conne ctions.New("DTS FlatFile")
oConnection.Con nectionProperti es("Data Source") = ""
oConnection.Con nectionProperti es("Mode") = 3
oConnection.Con nectionProperti es("Row Delimiter") = vbCrLf
oConnection.Con nectionProperti es("File Format") = 1
oConnection.Con nectionProperti es("Column Delimiter") = ","
oConnection.Con nectionProperti es("File Type") = 1
oConnection.Con nectionProperti es("Skip Rows") = 0
oConnection.Con nectionProperti es("Text Qualifier") = """"
oConnection.Con nectionProperti es("First Row Column Name") = False
oConnection.Con nectionProperti es("Column Names") = "HQID,LastUpdat ed,Number,Store ID,ID,Name,Pass word,FloorLimit ,ReturnLimit,Ca shDrawerNumber, SecurityLevel,P rivileges,Email Address,FailedL ogonAttempts,DB TimeStamp,MaxOv erShortAmount,M axOverShortPerc ent,OverShortLi mitType,AutoID, Telephone"
oConnection.Con nectionProperti es("Number of Column") = 20
oConnection.Con nectionProperti es("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "00100110000010 000001"
oConnection.Con nectionProperti es("Max characters per delimited column") = 8000
oConnection.Con nectionProperti es("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "00000000000000 000000"
oConnection.Nam e = "Connection 2"
oConnection.ID = 2
oConnection.Reu sable = True
oConnection.Con nectImmediate = False
oConnection.Dat aSource = ""
oConnection.Con nectionTimeout = 60
oConnection.Use TrustedConnecti on = False
oConnection.Use DSL = False
'oConnection.Pa ssword = "<put the password here>"
goPackage.Conne ctions.Add oConnection
Set oConnection = Nothing
Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceC onstraint
Set oStep = goPackage.Steps .New
oStep.Name = "Copy Data from Results to Step"
oStep.Descripti on = "Copy Data from Results to Step"
oStep.Execution Status = 1
oStep.TaskName = "Copied data in table "
oStep.CommitSuc cess = False
oStep.RollbackF ailure = False
oStep.ScriptLan guage = "VBScript"
oStep.AddGlobal Variables = True
oStep.RelativeP riority = 3
oStep.CloseConn ection = False
oStep.ExecuteIn MainThread = False
oStep.IsPackage DSORowset = False
oStep.JoinTrans actionIfPresent = False
oStep.DisableSt ep = False
oStep.FailPacka geOnError = False
goPackage.Steps .Add oStep
Set oStep = Nothing
Call Task_Sub1( goPackage )
goPackage.Execu te
tracePackageErr or goPackage
goPackage.Unini tialize
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
Public Sub tracePackageErr or(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescriptio n As String
Dim ErrorHelpFile As String
Dim ErrorHelpContex t As Long
Dim ErrorIDofInterf aceWithError As String
Dim i As Integer
For i = 1 To oPackage.Steps. Count
If oPackage.Steps( i).ExecutionRes ult = DTSStepExecResu lt_Failure Then
oPackage.Steps( i).GetExecution ErrorInfo ErrorCode, ErrorSource, ErrorDescriptio n, _
ErrorHelpFile, ErrorHelpContex t, ErrorIDofInterf aceWithError
MsgBox oPackage.Steps( i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescriptio n
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.DataPumpTas k2
Set oTask = goPackage.Tasks .New("DTSDataPu mpTask")
oTask.Name = "Copied data in table "
Set oCustomTask1 = oTask.CustomTas k
oCustomTask1.Na me = "Copied data in table "
oCustomTask1.De scription = "Copied data in table "
oCustomTask1.So urceConnectionI D = 1
oCustomTask1.So urceSQLStatemen t = "Select * from Cashier"
oCustomTask1.De stinationConnec tionID = 2
oCustomTask1.De stinationObject Name = "
oCustomTask1.Pr ogressRowCount = 1000
oCustomTask1.Ma ximumErrorCount = 0
oCustomTask1.Fe tchBufferSize = 1
oCustomTask1.Us eFastLoad = True
oCustomTask1.In sertCommitSize = 0
oCustomTask1.Ex ceptionFileColu mnDelimiter = "|"
oCustomTask1.Ex ceptionFileRowD elimiter = vbCrLf
oCustomTask1.Al lowIdentityInse rts = False
oCustomTask1.Fi rstRow = 0
oCustomTask1.La stRow = 0
oCustomTask1.Fa stLoadOptions = 2
oCustomTask1.Ex ceptionFileOpti ons = 1
oCustomTask1.Da taPumpOptions = 0
Call oCustomTask1_Tr ans_Sub1( oCustomTask1 )
goPackage.Tasks .Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask1_Tr ans_Sub1(ByVal oCustomTask1 As Object)
Dim oTransformation As DTS.Transformat ion2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Tr ansformations.N ew("DTS.DataPum pTransformCopy" )
oTransformation .Name = "DirectCopyXfor m"
oTransformation .TransformFlags = 63
oTransformation .ForceSourceBlo bsBuffered = 0
oTransformation .ForceBlobsInMe mory = False
oTransformation .InMemoryBlobSi ze = 1048576
oTransformation .TransformPhase s = 4
oTransformation .DestinationCol umns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation .TransformServe rProperties
Set oTransProps = Nothing
oCustomTask1.Tr ansformations.A dd oTransformation
Set oTransformation = Nothing
End Sub
Lawrence
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.OleDBPrope rties' cannot be indexed because it has no default property at the following line :oConnection.Co nnectionPropert ies
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.Descr iption = "Casier"
goPackage.Write CompletionStatu sToNTEventLog = False
goPackage.FailO nError = False
goPackage.Packa gePriorityClass = 2
goPackage.MaxCo ncurrentSteps = 4
goPackage.Linea geOptions = 0
goPackage.UseTr ansaction = True
goPackage.Trans actionIsolation Level = 4096
goPackage.AutoC ommitTransactio n = True
goPackage.Repos itoryMetadataOp tions = 0
goPackage.UseOL EDBServiceCompo nents = True
goPackage.LogTo SQLServer = False
goPackage.LogSe rverFlags = 0
goPackage.FailP ackageOnLogFail ure = False
goPackage.Expli citGlobalVariab les = False
goPackage.Packa geType = 0
Dim oConnProperty As DTS.OleDBProper ty
Dim oConnection as DTS.Connection2
Set oConnection = goPackage.Conne ctions.New("SQL OLEDB")
oConnection.Con nectionProperti es("Integrated Security") = "SSPI"
oConnection.Con nectionProperti es("Persist Security Info") = True
oConnection.Con nectionProperti es("Initial Catalog") = "EndofY"
oConnection.Con nectionProperti es("Data Source") = "(local)"
oConnection.Con nectionProperti es("Applicatio n Name") = "DTS Import/Export Wizard"
oConnection.Nam e = "Connection 1"
oConnection.ID = 1
oConnection.Reu sable = True
oConnection.Con nectImmediate = False
oConnection.Dat aSource = "(local)"
oConnection.Con nectionTimeout = 60
oConnection.Cat alog = "EndofY"
oConnection.Use TrustedConnecti on = True
oConnection.Use DSL = False
'oConnection.Pa ssword = "<put the password here>"
goPackage.Conne ctions.Add oConnection
Set oConnection = Nothing
Set oConnection = goPackage.Conne ctions.New("DTS FlatFile")
oConnection.Con nectionProperti es("Data Source") = ""
oConnection.Con nectionProperti es("Mode") = 3
oConnection.Con nectionProperti es("Row Delimiter") = vbCrLf
oConnection.Con nectionProperti es("File Format") = 1
oConnection.Con nectionProperti es("Column Delimiter") = ","
oConnection.Con nectionProperti es("File Type") = 1
oConnection.Con nectionProperti es("Skip Rows") = 0
oConnection.Con nectionProperti es("Text Qualifier") = """"
oConnection.Con nectionProperti es("First Row Column Name") = False
oConnection.Con nectionProperti es("Column Names") = "HQID,LastUpdat ed,Number,Store ID,ID,Name,Pass word,FloorLimit ,ReturnLimit,Ca shDrawerNumber, SecurityLevel,P rivileges,Email Address,FailedL ogonAttempts,DB TimeStamp,MaxOv erShortAmount,M axOverShortPerc ent,OverShortLi mitType,AutoID, Telephone"
oConnection.Con nectionProperti es("Number of Column") = 20
oConnection.Con nectionProperti es("Text Qualifier Col Mask: 0=no, 1=yes, e.g. 0101") = "00100110000010 000001"
oConnection.Con nectionProperti es("Max characters per delimited column") = 8000
oConnection.Con nectionProperti es("Blob Col Mask: 0=no, 1=yes, e.g. 0101") = "00000000000000 000000"
oConnection.Nam e = "Connection 2"
oConnection.ID = 2
oConnection.Reu sable = True
oConnection.Con nectImmediate = False
oConnection.Dat aSource = ""
oConnection.Con nectionTimeout = 60
oConnection.Use TrustedConnecti on = False
oConnection.Use DSL = False
'oConnection.Pa ssword = "<put the password here>"
goPackage.Conne ctions.Add oConnection
Set oConnection = Nothing
Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceC onstraint
Set oStep = goPackage.Steps .New
oStep.Name = "Copy Data from Results to Step"
oStep.Descripti on = "Copy Data from Results to Step"
oStep.Execution Status = 1
oStep.TaskName = "Copied data in table "
oStep.CommitSuc cess = False
oStep.RollbackF ailure = False
oStep.ScriptLan guage = "VBScript"
oStep.AddGlobal Variables = True
oStep.RelativeP riority = 3
oStep.CloseConn ection = False
oStep.ExecuteIn MainThread = False
oStep.IsPackage DSORowset = False
oStep.JoinTrans actionIfPresent = False
oStep.DisableSt ep = False
oStep.FailPacka geOnError = False
goPackage.Steps .Add oStep
Set oStep = Nothing
Call Task_Sub1( goPackage )
goPackage.Execu te
tracePackageErr or goPackage
goPackage.Unini tialize
set goPackage = Nothing
set goPackageOld = Nothing
End Sub
Public Sub tracePackageErr or(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescriptio n As String
Dim ErrorHelpFile As String
Dim ErrorHelpContex t As Long
Dim ErrorIDofInterf aceWithError As String
Dim i As Integer
For i = 1 To oPackage.Steps. Count
If oPackage.Steps( i).ExecutionRes ult = DTSStepExecResu lt_Failure Then
oPackage.Steps( i).GetExecution ErrorInfo ErrorCode, ErrorSource, ErrorDescriptio n, _
ErrorHelpFile, ErrorHelpContex t, ErrorIDofInterf aceWithError
MsgBox oPackage.Steps( i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescriptio n
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.DataPumpTas k2
Set oTask = goPackage.Tasks .New("DTSDataPu mpTask")
oTask.Name = "Copied data in table "
Set oCustomTask1 = oTask.CustomTas k
oCustomTask1.Na me = "Copied data in table "
oCustomTask1.De scription = "Copied data in table "
oCustomTask1.So urceConnectionI D = 1
oCustomTask1.So urceSQLStatemen t = "Select * from Cashier"
oCustomTask1.De stinationConnec tionID = 2
oCustomTask1.De stinationObject Name = "
oCustomTask1.Pr ogressRowCount = 1000
oCustomTask1.Ma ximumErrorCount = 0
oCustomTask1.Fe tchBufferSize = 1
oCustomTask1.Us eFastLoad = True
oCustomTask1.In sertCommitSize = 0
oCustomTask1.Ex ceptionFileColu mnDelimiter = "|"
oCustomTask1.Ex ceptionFileRowD elimiter = vbCrLf
oCustomTask1.Al lowIdentityInse rts = False
oCustomTask1.Fi rstRow = 0
oCustomTask1.La stRow = 0
oCustomTask1.Fa stLoadOptions = 2
oCustomTask1.Ex ceptionFileOpti ons = 1
oCustomTask1.Da taPumpOptions = 0
Call oCustomTask1_Tr ans_Sub1( oCustomTask1 )
goPackage.Tasks .Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
Public Sub oCustomTask1_Tr ans_Sub1(ByVal oCustomTask1 As Object)
Dim oTransformation As DTS.Transformat ion2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Tr ansformations.N ew("DTS.DataPum pTransformCopy" )
oTransformation .Name = "DirectCopyXfor m"
oTransformation .TransformFlags = 63
oTransformation .ForceSourceBlo bsBuffered = 0
oTransformation .ForceBlobsInMe mory = False
oTransformation .InMemoryBlobSi ze = 1048576
oTransformation .TransformPhase s = 4
oTransformation .DestinationCol umns.Add oColumn
Set oColumn = Nothing
Set oTransProps = oTransformation .TransformServe rProperties
Set oTransProps = Nothing
oCustomTask1.Tr ansformations.A dd oTransformation
Set oTransformation = Nothing
End Sub
Lawrence