DTS help

I have not used SQL in a little while and am a little Rusty, and i have been stuck on this all day. Any advise would help. Here is the scenario

Two databases on two seperate servers: each database uses a different
user login

I am trying to pull out data in one field from one table in one database
and insert it into a row in another table.

Statements:

SELECT Count(*) as total_defects FROM server1.database1.dbo.defects
WHERE (idProduct = 31) AND (status <4)

(This statement works fine)

That statement returns the value to me correctly if I run it off of server1.

I then want to replace data in another table with that Count if it has a

certain product_id. Here is the logic for the statement I want to run.

Update local.sqatest.dbo.product SET ipro_defecttotal = total_defects
WHERE cpro_id = 00000001

Now i can't run it like the statement above, because I have two seperate

servers to log in to. Can I use some DTS method to do this?

Yes you can use DTS or SSIS. This forum is for SSIS-

Use a Data Flow with an OLE-DB Source, using your SELECT. That will generate one row which will flow down the pipeline. You can use a OLE-DB Command to perform the update.

Since it is only one row, you could also use an Execute SQL Task to run the select, and set the ResultSet to SingleRow. The result page can then be used to store the value into a variable. A second Execute SQL Task could then be used to run the UPDATE statement. Just use a parameter for the value, and map that to the variable.

The tasks mentioned and things like parameters are all documented in Books Online, so have a quick read of the relevant topics if you are not already familiar with concepts such as parameters in Execute SQL Tasks for example.

For DTS you can do the same thing, but for the Data Flow, use the Data Driven Query task as that allows parameterise statements such as UPDATEs. The Execute SQL Task method should work along much the same lines.