Which task to use

[aswolff]

Hi.

I am trying to figure what DTS tasks to use to accomplish the below scenario.

1. Query table/column A.JOBID where A.STATUS = PUSHED.

2. For each A.JOBID from query 1 set A.STATUS = PULLING.

3. Query oracle table B and insert into sql table C each record where B.STATUS = PULLING.

4. When 3 is done set each A.JOBID = PULLED.


My problem is how do I take the results of query1 and store them (as variables? parameters?) to be used in queries 2,3,4?


Thanks.

 

[HarleyQuinn]

You can do 1 & 2 as a join, so you can miss a step out. It's been a while since I've used Oracle with SQLServer (and it was the other way round DTS to import Oracle data) so as it's bed time where I am I'll have to leave it as that for the minute.

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[AlexCuse]

If you're trying to do a join across the two databases, you'll need to look into setting up a linked server as well. Because of syntax differences, you might be better off setting up a "staging table" on one database or the other, and doing the manipulation all in one place.

Is A an oracle or SQL Server table?

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page

 

[aswolff]

A is oracle. And I need to do everything from within DTS.

What I figured out so far is to use a "Execute SQL Task" and place the results in a recordset global variable (Step 1).

Now I need to figure out how to loop thru the recordset and for each record perform:

Step 2
Step 3
Step 4

I am thinking that maybe step 1 needs to be a parent package
and then steps 2,3,4 are seperate packages that get called from 1?? Any ideas? Anybody?

Thanks.

 

[AlexCuse]

I think you're going down a bad road here. Bringing the necessary oracle data into your SQL database somehow is probably a better idea (IMO) , because it would let you do things in a set-based fashion.

You can do this by setting up a linked server, or actually creating a step in your DTS to bring the data across into a staging table as I suggested.

Can you share what you've done so far?

Hope this helps,

Alex

[small]----signature below----[/small]
I'm pushing an elephant up the stairs

My Crummy Web Page