DSXchange

Can anyone show me how I can use an ODBC connection to select a 1x1 value into a variable/parameter so I can then use that in a WHERE statement on a different ODBC connection in the same job?

I'd be surprised if that could be done. The problem is that all the stages pretty much get started at the same time (assuming a parallel job) so even if your small ODBC stage were to get the variable and write it to a file which is used as the SQL command for the other stage you get concurrency issues.
Although, now that I've written it down, it might be possible to declare that file as a named pipe (are you on UNIX?) which could mean that the two stages communicate with each other via the pipe.
... I checked and the read-from-file option is only for the before/after statements, not for the query.
If both ODBC queries are in the same database then you could do it directly in the write stage.

Perhaps a job sequence, calling up a job to read the "where" clause and setting a user status, which is read by the job sequence and passed as a parameter to your 2nd job, which then processes that parameter as part of the "where" clause.

ArndW wrote:I'd be surprised if that could be done.

It can't. As Arnd noted, you'll need to re-architect your approach.

You could use the job's user status area as that variable. But you would then need to retrieve it using a routine that invokes DSGetJobInfo() function. Messy, but doable in a server job.

I found a solution, adapting from mhester's post at viewtopic.php?t=91564 to write a routine that would return a 1x1 value. I can run my routine from just about anywhere; passing as a job parameter, user variable stage, etc...

Where there's a will, there's a way!