Hello,
I created a DTS package VB6 script with DTS from Sql
Server 2000. In a vb.net project I add a reference to
Microsoft DTSpackage object library and copy the code from
the DTS script to a module in the vb.net project. The
following lines of code are a few of the lines with syntax
issues and the description of the issue (below that is the
sub (DTSrun) that these lines came from). My request is
if someone could explain how to fix the syntax (I'm just
trying to save a few hours of hacking from picking
whatever from the dropdown lists until something works).
Plus, some of the messages are suggesting an Interface
problem - do I need to implement some kind of interface?
(I appologize in advance for my ignorance)
Option Strict On
Option Explicit On
Option Compare Binary
Imports DTS
Imports System.Data.SqlClient
Module ModDTS
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Sub DTSrun()
goPackage = goPackageOld <----- prob here
-->Option Strict On disallows implicit conversions from
DTS.Package to DTS.Package2
....
goPackage.TransactionIsolationLevel = 4096 <---- prob
-->Option Strict On disallows implicit conversions from
Integer to DTS.DTSIsolationLevel
....
oConnection.ConnectionProperties("Mode") = 1 <--- prob
-->Interface 'DTS.OleDBProperties' cannot be indexed
because it has no default properties
-->what Interface do I need to implement for this?
....
*********************************************
Public Sub tracePackageError(ByVal 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 ...
-->Name 'DTSStepExecResult_Failure' is not declared
-->where do I get the constants for this?
************************************************** ****
************************************************** ****
-->here is all of sub DTSrun - minus the create columns
part
Option Strict On
Option Explicit On
Option Compare Binary
Imports DTS
Imports System.Data.SqlClient
Module ModDTS
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Public Sub DTSrun()
goPackage = goPackageOld
goPackage.Name = "samplePkg1b"
goPackage.Description = "DTS package description"
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
' create package connection information
Dim oConnection As DTS.Connection2
oConnection = goPackage.Connections.New("DTSFlatFile")
oConnection.ConnectionProperties("Data Source")
= "C:\sample1a.txt"
oConnection.ConnectionProperties("Mode") = 1
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("Max characters per
delimited column") = 8000
oConnection.Name = "Connection 1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "F:\Adonet\sample1a.txt"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False
goPackage.Connections.Add(oConnection)
oConnection = Nothing
oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Integrated Security")
= "SSPI"
oConnection.ConnectionProperties("Persist Security Info")
= True
oConnection.ConnectionProperties("Initial Catalog")
= "LATOS"
oConnection.ConnectionProperties("Data Source") = "mySvr"
oConnection.ConnectionProperties("Application Name")
= "DTS Import/Export Wizard"
oConnection.Name = "Connection 2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "mySrv"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "LATOS"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
goPackage.Connections.Add(oConnection)
oConnection = Nothing
' create package steps information
Dim oStep As DTS.Step2
Dim oPrecConstraint As DTS.PrecedenceConstraint
'------------- a new step defined below
oStep = goPackage.Steps.New
oStep.Name = "Copy Data from sample1a to [myDB].[dbo].
[sample1a] Step"
oStep.Description = "Copy Data from sample1a to [myDB].
[dbo].[sample1a] Step"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from sample1a to [myDB].[dbo].
[sample1a] Task"
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)
oStep = Nothing
' create package tasks information
'------------- call Task_Sub1 for task Copy Data from
sample1a to [myDB].[dbo].[sample1a] Task (Copy Data from
sample1a to [myDB].[dbo].[sample1a] Task)
Call Task_Sub1(goPackage)
' Save or execute package
'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute()
tracePackageError(goPackage)
goPackage.UnInitialize()
goPackage = Nothing
goPackageOld = Nothing
End Sub
*************************************
' error reporting using step.GetExecutionErrorInfo after
execution
Public Sub tracePackageError(ByVal 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
....
End Module
Thanks,
Rich
