Hello,

I saved a DTS packaged down to a VB6 module. If I compile
this module in VB6 it runs fine. But I would like to
incorporate the code into a VB.net app. I am having a lot
of problems with this. I have turned Option Strict Off
(for now) and even with that I still get 3 types of error
messages that are beyond me at this time. Does anyone
know how to deal with these types of errors or know where
I could go to find out about these kind of errors?

--Error #1
--------------------------------------------------------
Dim oConnection As DTS.Connection2

oConnection = goPackage.Connections.New("DTSFlatFile")

oConnection.ConnectionProperties("Data Source")
= "C:\test.txt" <--- error on this oConnection

--Interface 'DTS.OleDBProperties' cannot be indexed
because it has no default property--
--------------------------------------------------------

Error #2
--------------------------------------------------------
Public Sub Task_Sub1(ByVal goPackage As Object)

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

Dim oCustomTask1 As DTS.DataPumpTask2
oTask = goPackage.Tasks.New("DTSDataPumpTask") <-- error
on gopackage.Tasks....

--Constructor call is valid only as the first statement in
an instance constructor--
--------------------------------------------------------

Error #3
------------------------------------------------------
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 <--- error here

--Name 'DTSStepExecResult_Failure' is not declared--
-------------------------------------------------------

Any suggestions appreciated.
Thanks,
Rich

In article <2b6df01c468f9$f84a97c0$a601280a (AT) phx (DOT) gbl>, Rich wrote:

For your first problem does either of these work for you

Dim cn As DTS.Connection

cn = pkg.Connections.[New]("DTSFlatFile")

cn.DataSource = "c:\Text File.txt"

cn.ConnectionProperties.Item("Data Source").Value = "c:\Text File.txt"


And for your second problem I cannot see what you are erroring on as it
is unclear through my word wrapping

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

Dim oCustomTask1 As DTS.DataPumpTask2
oTask = dtsImport.Tasks.New("DTSDataPumpTask")
oCustomTask1 = oTask.CustomTask




For your third problem what about

If pkg.Steps.Item(i).ExecutionResult =
DTS.DTSStepExecResult.DTSStepExecResult_Failure Then

End If



Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Hi,

Thanks for your reply.

On the first part, your code lines appear to work in my
vb.net code module (have a reference to Microsoft DTS
object library - and using Imports System.DTS - everything
else commented out for now). Even with Option Strict On
these codes lines seem to work:

Dim cn As DTS.Connection
cn = pkg.Connections.[New]("DTSFlatFile")
....
compiler not complaining so far.

As for the second error

oTask = goPackage.Tasks.New("DTSDataPumpTask")

on this line (Error #2) I get this complaint from the
compiler:

"Constructor call is valid only as the first statement in
an instance constructor"

this is happening in

Public Sub Task_Sub1(ByVal goPackage As Object)
....

Should I copy the whole VB6 module for you to look at?

Note: with Option strict on I also get alot of other minor
errors, just syntax stuff like need to say CType(...,
Integer), somthing.ToString, etc - compiler says "No Late
Binding with Option Strict On". But I guess I will wait
till I resolve the big errors first.

Rich

Try-

oTask = CType(goPackage, DTS.Package).Tasks.New("DTSDataPumpTask")


Some more info on this topic-

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks. I'm starting to get the hang of this. I was able
to fix another constructor based error with your example.
This gave me the confidence to try Allan's suggestion of
adding .Item and .Value

oConnection.ConnectionProperties("Data Source")
to
oConnection.ConnectionProperties.Item("Data Source").Value

Thanks guys. I should be able to get this going in .Net
now. All I have left is a few more CTypes (so I can use
Option Strict On). Now I have the confidence.

Thanks for the refernce, too.
Rich

Well, this is the last sub that I wasn't able to get, even
with the info at (http://www.sqldts.com/default.aspx?
264). They didn't have instructions for DTS.Steps. Here
is the sub I couldn't get:

-->oPackage.Steps(i).ExecutionResult =...

I tried CType(oPackage, DTS.Package).Steps... no go

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 = <--problem here
DTSStepExecResult.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

I was able to get
DTSStepExecResult.DTSStepExecResult_Failure

by adding DTSStepExecResult. to DTSStepExecResult_Failure

I respectfully request one more hint (if I may) on the
DTS.Steps thing.

Thanks for all the help,
Rich

Nevermind. I figured it out.

oPackage.Steps(i).ExecutionResult =...

to

oPackage.Steps.Item(i).ExecutionResult =...

Many Thanks again for all the help.

Rich