I access RMS files on a mainframe using an ODBC driver. It works well. In the transformation part of DTS, I give it a select statement to limit the records I get back ( on date last updated ). My problem is that I need to do someting like this

SELECT *
FROM Address_rec
WHERE address_date_last_update > (SELECT MAX(date)
FROM DateLastUpdate)

The problem is that each is in a different database. Address_rec is accessed through the RMS ODBC connection and DateLastUpdate resides in SQLServer. I must create a connection for both.

How am I to solve this? I thought of a global variable. Can I access this through a select statement in the dts package?

Please help.