SQL Server 2000 DTS package, filter rows using stored procedure returned date

Question

I know very little about DTS packages in the first place (I'm a programmer not a dba) and have been stuck with the task of filtering rows based on the results of a stored procedure call that returns a date, where the desired result would be something like this:
" ( WHERE SpecialDate >= SPROC_Returned_Date )".

The stored procedure lives on a local DB server and the rows are all coming from elsewhere using a DB/C FS 3 driver connection. I'm having a very hard time figuring out how to connect the SQL query on the Transform Data Task that does the row retrieval to the date returning stored procedure.

Where should I even start?

Update: I've been able to create a view that returns one row with the date I'm interested in from the stored procedure. However the DB/C FS 4 connection doesn't seem capable of interpreting anything involving the use of parameters. I'm at a loss here as it seems I'm just about 90% here but can find any useful information about this DB/C FS4 ODBC driver.

Answer 1

In SQL Server 2000, you cannot use the results of a stored proc directly in another query. However, you can insert them into a #temp table and then join to that. But you must create the temp table structure first, you can't use select into.

Answer 2

Due to inability to get this working in SQL2000 I've since installed a 2008R2 instance and started working on an SSIS based package. It appears there is a way to dynamically generate the SQL statement I need using expressions and user variables.

In general I'm guessing the major problem is the limitations of the DB/C FS4 driver although I've still been unable to find anything useful about it.

So in short I think the best answer here is to move to a new SQL Server version if possible.