I thought I knew a lot about DTS, but I realize what a novice I am
after reading this news group.

I am not new to DTS, but new to Global Variables. I am trying to loop
through a global variable rowset and update data in a table.

Here is what I am trying to do...

I need to mark a Primary item if there are multiple itemsin the table.
I need to select the first Item and mark it as 'Prime' (a bit field).
Here is what my original table looks like...

tblItem...
RowID ItemID ItemOrder Prime
----- ------ --------- -----
1 1 23 0
2 1 25 0
3 1 26 0
4 2 1 0
5 2 2 0
6 2 3 0
7 3 23 1
8 4 1 1

I have set up a global veriable holding the ItemID. I tried using this
statement in the SQLTask...

UPDATE tblItems SET Prime = 1 WHERE RowID in
(SELECT TOP 1 RowID FROM tblItems WHERE Itemid = ? ORDER BY
ItemOrder)

I recieved the error that I could not use a global veriable in a
sub-select query.

I then tried...
Declare @item Int
Set @item = ?
GO

UPDATE tblItems SET Prime = 1 WHERE RowID in
(SELECT TOP 1 RowID FROM tblItems WHERE Itemid = @item ORDER BY
ItemOrder)

That did not work either.

I am guessing that my next option would be to build the SQL string,
with the sub-select, and execute that in an ActiveX Task??

Any direction would be helpful.

Steffany

Ok, left to my own will and determination, I came up with this, and it
works...

Step 1. I set prime to [zero] for all rows in my table.
Step 2. For each item that was only listed once, I set Prime to [one].
Step 3. I created a Global Veriable RecordSet that held the ItemID for
all the items that were listed more than once.
Step 4. I ran this ActiveX code...
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
dim uSQL
dim gv

'set the global variable
set gv = DTSGlobalVariables("gvMultiISBNOptyID").Value

'connect to the database
set myCon = CreateObject("ADODB.Connection")
myCon.Open = "DRIVER={SQL
Server};SERVER={server};UID={uid};PWD={pws};DATABA SE={database}"

'loop thru variable and execute my SQL
Do While NOT gv.EOF
'set my update string
uSQL = "UPDATE tblItem SET Prime = 1 WHERE RowID IN(SELECT TOP 1
RowID FROM tblItem WHERE ItemID = '" & gv.Fields(0).Value & "' ORDER BY
ItemOrder)"
set uRS = myCon.Execute(uSQL)

'clean up
set uRS = Nothing

'move next
gv.MoveNext
Loop

Main = DTSTaskExecResult_Success
End Function
'************************************************* ******

It worked!! I have to thank everyone in the group and the great info
on www.sqldts.com.

It might not be the prettiest, but it works and it's mine.

Steffany