DTS pkg VB6 to VB.Net problems - Sql Srv2000

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

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
 
The DTS script works fine in VB6. So I suppose an
alternative would be to compile this to an exe and call it
from my vb.net project. Should I use shell or what is the
best way to call the exe from the .net proj?

Thanks,
Rich
 
Back
Top