I have a package utilizing 2 'connections' but 45+ transformations. I need
for the 'source' and 'destinations' to be flexible, dynamic. Changing the
connections alone does not do the trick, I must go re-do each and every
mapping. I'm not a vb guru and can't figure out exactly how to use a
generated vb bas file in an activex script. I suppose I could write an
activex script to set the SourceObjectName and DestinationObjectName for
each of the 45 tasks equal to a GV + .[dbo].[tablename]. Any ideas on the
best way to accomplish this ?

Jeff Block, Database Warlock
First Look
Dynamic Business Intelligence
jblock@firstlook<DOT>biz

I figured out easy enough I can do this:

Dim oPkg, oTask

Set oPkg = DTSGlobalVariables.Parent
Set oTask = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

oTask.Properties("SourceObjectName").Value =
"[XXEERR].[dbo].[ApplicationEvent]"

Set oTask = Nothing
Set oPkg = Nothing

Is there an easy, but I don't want to have to hard code each and every
table, etc,. I 'd like to loop through all of the Tasks, dynamically
stuffing in the proper database name


"Jeff Block" <jblock (AT) REMOVETHECAPSANDINVALIDfirstlook (DOT) biz.invalid> wrote in
message news:uCQ4smTuDHA.1512 (AT) TK2MSFTNGP10 (DOT) phx.gbl...

I figured it all out EXCEPT FOR, I need to know how to loop through the
'collection' of the DataPump Tasks dynamically so I can make the
replacements. Help please.

"Jeff Block" <jblock (AT) REMOVETHECAPSANDINVALIDfirstlook (DOT) biz.invalid> wrote in
message news:%23CVOlxTuDHA.2260 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

How about this

dim pkg, tsk

set pkg = DTSGlobalVariables.Parent

for each tsk in pkg.Tasks
if tsk.CustomTaskID = "DTSDataPumpTask" then
msgbox "You want me then: " & tsk.Name
end if
next

--
--

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

"Jeff Block" <jblock (AT) REMOVETHECAPSANDINVALIDfirstlook (DOT) biz.invalid> wrote in
message news:%23sBsSTUuDHA.2224 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

thanks, now I have one other little problem with this ..

TaskName = tsk.Name

Set oTask = oPkg.Tasks(TaskName).CustomTask

The last line gives me a type mismatch error ...



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

What about

dim pkg, tsk, cusTask

set pkg = DTSGlobalVariables.Parent

for each tsk in pkg.Tasks
if tsk.CustomTaskID = "DTSDataPumpTask" then
set cusTask = pkg.Tasks(tsk.Name).CustomTask
msgbox custask.SourceObjectName

end if
next

--
--

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

"Jeff Block" <jblock (AT) REMOVETHECAPSANDINVALIDfirstlook (DOT) biz.invalid> wrote in
message news:uiZYFfbuDHA.3536 (AT) tk2msftngp13 (DOT) phx.gbl...