Get the InputGlobalVariables from a different package

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 . . .

Get Primary Key from another Table

Hello everyone,
I've just tried realizing following project:

Currently i have separate Tables with separate Primary Keys (ascending separate Number regions).

Now I want to do the following:
Table - NewID
Field - ID - Identity
Field - IDproc - Sub-Data Number

Table - Data1 (IDProc=1)
Field - ID - Primary Key, generated NEW from NEWiD

Now great would be if I could add a new Recordset in Data1 and I could generate a New ID in 'NewID' trough a Trigger and Set it on the inserted Data, but that doesn't seem possible.

I was able to do it with the following trigger:
CREATE TRIGGER [getNewID] ON [dbo].[Data1]
FOR INSERT
AS
DECLARE @.newID int

INSERT INTO NewID (IDProc) VALUES(1)
SELECT @.newID=(SELECT TOP 1 id FROM NewID WHERE IDProc=1 ORDER BY ID DESC)
UPDATE Data1 SET ID=@.newID WHERE ID IS NULL

But as I can imagine this would get some problems if two users insert a Recordset at the same Time - right?
:mad:
Is there somehow the possiblity to 'SET INSERTED.ID = @.NewID' ??, because it's the only real ID I have in this table (beneath a TimeStamp wich I also couldn't use).

Thanks for any Help!!!First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.

Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.

blindman|||Originally posted by blindman
First off, if you are using the NewID() function, you will be generating Globally Unique Identifiers, not sequential numbers, so your ORDER BY ID DESC logic won't work.

Are you trying to get the most recent ID inserted? Try creating your ID as a variable within the trigger and then inserting it into both your tables. The NewID() function works well for this.

blindman

Hi Blindman
NewID was just a variable for my thoughts, i don't use the function NewID - this is just the tablename and var-name of the trigger...

The idea is to have a main table with all the ID's and sub-data information so I can have a table Data1 Data2 and Data3 wich dont get themselves into the way with their ID's.|||If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.

blindman|||Originally posted by blindman
If you are trying to keep IDs from being duplicated within or between data tables, then you should consider using the uniqueidentifier datatype and the NewID function. Its easy to use and will always give you a unique ID. I think it might solve your problem.

blindman

Wow, i've just took a look at this NewID function, that's a pretty big thing :-).
I would be pretty happy if i could handle the ID with numbers, not that a customer has to spell me those 20 chars :), do you know any way I could do this trough triggers?
Or is there a possibility to convert the NewID-var to some kind of number?|||NewID produces a standard UniqueIdentifier data type which can be implicitily converted to a char(36) or nchar(36) data type. It's good practice to make your IDs invisible to Users anyway and keep them for internal data processing.

blindman