I've been tasked with figuring out how to alter the version_id of an ExecutePackageTask programatically from a separate DTS package.
The ultimate goal is to have a "utility" DTS pack that
iterates through all the child packages within a given parent package, checks the systems tables to get the latest version_id, then assigns the latest versionid to the ExecutePackageTask. We feel this beats the heck out of opening the parent, opening each ExecutePackageTask, running through the randomly ordered list of Package Names and clicking on the updated package when a child package changes or a program is migrated to a new server.
My approach was to open the parent pack from an activex script in the utility DTS package, then iterate through the task collection looking only at ExecutePackage tasks, then check the versionid against the appropriate row in the sysdtspackages table and update the child package version_id if necessary.
I've only gotten as far as looking at the ExecutePackageTask properties and I'm stumped . . . I'm told that the object doesn't support the property or method, yet you can do this manually through a disconnected edit! Any thoughts?
Here's the code so far:
Function Main()
'create the target parent package object
Set objPackage = CreateObject("DTS.Package2")
'load the target parent package
objPackage.LoadFromSQLServer "PAMB","dts" ,"dts", DTSSQLStgFlag_UseTrustedConnection, , , ,"TESTME2"
'Create the task object
Set objTask = objPackage.Tasks
'Iterate through the tasks
For each objTask in objPackage.Tasks
if left(objTask.Name,29) = "DTSTask_DTSExecutePackageTask" then
msgbox objTask.Description
msgbox objTask.Versionid
end if
Next
Main = DTSTaskExecResult_Success
End Function
Thanks in advance,
PamClearly there are too many projects in my head right now . . .
Friday, March 9, 2012
Get the InputGlobalVariables from a different package
Labels:
alter,
database,
dts,
executepackagetask,
figuring,
inputglobalvariables,
microsoft,
mysql,
oracle,
package,
programatically,
separate,
server,
sql,
tasked,
version_id
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment