I'm very new with ActiveX Script and I would appreciate if
you could review this script. The script works but I will
need to add all the tranformations I have in my dts. Is
there any way around that. If not, can my script be
shorter than this? I feel I'm declaring too many variables.

Function Main()
dim myTask1
dim myTask2
set myTask1 = DTSGlobalVariables.Parent.Tasks
("DTSTask_DTSDataPumpTask_1")
set myTask2= DTSGlobalVariables.Parent.Tasks
("DTSTask_DTSDataPumpTask_2")
dim sTable
dim sTable2
dim sDB
dim sDB2
dim iPos
dim iPos2
sTable= myTask1.CustomTask.DestinationObjectName
sTable2=myTask2.CustomTask.DestinationObjectName
sDB = DTSGlobalVariables("DestinationDB").Value
sDB2=DTSGlobalVariables("DestinationDB").Value
iPos =instr(1,sTable,".")
iPos=instr(1,sTable2,".")
if iPos > 0 then
myTask1.CustomTask.DestinationObjectName = sDB & mid
(sTable,iPos)
myTask2.CustomTask.DestinationObjectName = sDB2 & mid
(sTable2,iPos)
end if

Main = DTSTaskExecResult_Success
End Function

In message <6f8401c3e6be$2f1ed3b0$a401280a (AT) phx (DOT) gbl>, Olivia
<anonymous (AT) discussions (DOT) microsoft.com> writes
Firstly, you could just remove the DB from the object name and rely on
the connection to set the DB.

Your code was OK, but you did appear to do everything twice. Why not
write a function that is passed the task, and the new DB name. You can
also enumerate all tasks in a package, looking for DataPump tasks.

Below is some old code I have used, which finds all DataPump and DDQ
tasks, and takes the DB from the connection, and updates the object
names to match. To use this first set the connection DBs to their new
values, and then call the UpdateTasks method.


Function UpdateTasks()
*Dim oPkg, oTasks, oTask, oCustomTask, oConn
*Dim sSource, sDest
*
*' Get Package and Task Collection Reference
*Set oPkg = DTSGlobalVariables.Parent
*Set oTasks = oPkg.Tasks
*
*For Each oTask in oTasks
**' Check for DataPump or DDQ Task
**If oTask.CustomTaskID = "DTSDataPumpTask" Or _
*** oTask.CustomTaskID = "DTSDataDrivenQueryTask" Then
*
***' Get CustomTask Reference
***Set oCustomTask = oTask.CustomTask
***
***' Amend* Source Object Name
***Set oConn = GetConnectionByID(oPkg.Connections,
oCustomTask.SourceConnectionID)
MsgBox oConn.Name
***sSource = oCustomTask.SourceObjectName
***sSource = ReplaceDB(sSource, oConn.Catalog)
***oCustomTask.SourceObjectName = sSource
*
***' Amend Destination Object Name
***Set oConn = GetConnectionByID(oPkg.Connections,
oCustomTask.DestinationConnectionID)
MsgBox oConn.Name
***sDest = oCustomTask.DestinationObjectName
***sDest = ReplaceDB(sDest, oConn.Catalog)
***oCustomTask.DestinationObjectName = sDest
***
**End If
*Next
*
*
End Function
*
Function GetConnectionByID(oConnections, iID)
*Dim oConn
*For Each oConn In oConnections
**If iID = oConn.ID Then
***Set GetConnectionByID = oConn
***Exit For
**End If
*Next
End Function
*
Function ReplaceDB(sString, sDB)
*'* Assumes standard format
*' [dbanme].[owner].[object]
*Dim iEnd
*If sDB = "" Or sString = "" Then
**ReplaceDB = sString
*Else
**iEnd = InStr(1, sString, "]")
**ReplaceDB = "[" & sDB & Mid(sString, iEnd, Len(sString) - (iEnd- 1))
*End If
End Function
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

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

When I run my package (just a simple pkg with 2
connections and a transform data task) I keep getting this
error "invalid task result value". I cannot figure it out
why? Can you please help? Thank you. Olivia