I'm using VB6 and SS2000. I want to look through all of the DTS Data Pump
Tasks in a package. I'm trying to find a package that contains a certain
table. I pulled some code from MS site but it is creating a datapump object
and I want to find one. I having trouble instantiating the object. I don't
know how to do it and then I want to loop through the datapump objects in
the package and look at both the DestinationObjectName and the
SourceObjectName. How do I instantiate the data pump object for the package
that I'm connected to? This is the code I have so far. I was able to loop
through the tasks but then I discovered that what I'm looking for is in the
datapump.

'Declare variables
Dim x As Integer
Dim oPkg

' Assign parameters
sServer = "(local)"
sUID = ""
sPWD = ""
iSecurity = DTSSQLStgFlag_UseTrustedConnection
sPkgPWD = ""
sPkgName = "Fetch and Process the Stores File from the web"

' Load Package
Set pkg = New DTS.Package2

pkg.LoadFromSQLServer sServer, sUID, sPWD, iSecurity, sPkgPWD, "", "",
sPkgName

' this code doesn't work
Dim objDataPump As DTS.DataPumpTask2
objDataPump.DestinationObjectName


' this part works ok but what I'm looking for isn't here
For x = 1 To pkg.Tasks.Count
pkg.Tasks.Item (x)
Next x

'Clean up
pkg.UnInitialize
Set pkg = Nothing

Thanks,

Dan

OK this is using .Net (I don't have the classic version of VB)

Dim p As New DTS.Package

Dim cus As DTS.CustomTask

Dim dpTask As DTS.DataPumpTask



p.LoadFromSQLServer("MyBigAndBadServer", , ,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection, , , ,
"FindTheDataPumps")

For Each tsk As DTS.Task In p.Tasks

If tsk.CustomTaskID = "DTSDataPumpTask" Then

dpTask = tsk.CustomTask

MessageBox.Show(dpTask.DestinationObjectName)

End If

Next

p.UnInitialize()


You can loop through packages on the server using this article code

Enumerating DTS Packages using VB.Net
(http://www.sqldts.com/default.aspx?250)




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Dan" <ddonahue (AT) archermalmo (DOT) com> wrote

VB6 doesn't like the line "For each tsk AS DTS.Task in p.tasks". I can look
through the tasks with this:

For x = 1 To pkg.Tasks.Count
pkg.Tasks.Item (x)
Next x

But how do I get the task.customtaskid once I'm inside the loop?

Thanks,

Dan
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

You should be able to do something like

dim tsk as DTS.Task

for each tsk in pkg.Tasks
msgbox tsk.CustomTaskID
next



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Dan" <ddonahue (AT) archermalmo (DOT) com> wrote

Its almost there. I have this:

For Each tsk In pkg.Tasks
MsgBox tsk.CustomTaskID
If tsk.CustomTaskID = "DTSDataPumpTask" Then
dpTask = tsk.CustomTask
MessageBox.Show (dpTask.DestinationObjectName)
End If
Next

The line that starts dpTask gives this error: Object variable or with block
variable not set.

Any idea?

Thanks,

Dan

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Dim oTask AS DTS.Task
Dim oDataPump As DTS.DataPumpTask

For Each oTask In oPkg.Tasks
If oTask = "DTSDataPumpTask" Then
Set oDataPump = oTask
MsgBox(oDataPump.DestinationObjectName)
End If
Next

Set oDataPump = Nothing
Set oTask = Nothing


--
Darren Green
http://www.sqldts.com


"Dan" <ddonahue (AT) archermalmo (DOT) com> wrote

Darren,

As soon as the debugger tries to leave the line "If oTask =
"DTSDataPumpTask" Then" I get an "object doesn't support this property or
method. If I change the line to be more like what Allan used - "If
oTask.CustomTaskId = "DTSDataPumpTask" then I don't get the error on that
line but when the code goes into the "If" statement I get a "type mismatch"
error on the line - "Set oDataPump = oTask"

Oh. I got it! I changed the line to ""Set oDataPump = oTask.CustomTask" and
it worked. Great!

Thanks Darren and thanks Allan.

Dan

"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

In message <esM#4#dMEHA.3380 (AT) TK2MSFTNGP11 (DOT) phx.gbl>, Dan
<ddonahue (AT) archermalmo (DOT) com> writes
Bugger!! I meant to highlight the lack of Set as required object
assignment, and thought I'd include a full code sample, but
unfortunately I failed on both counts! Glad you made it there despite my
attempts

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

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