SQL Server Solutions - Store The Results Of A SQL Query In A Global DTS Variable (SQL Server 2000)
Did you know that you can funnel all the rows of a query resultset into a global DTS variable? This can be beneficial in that subsequent tasks in your DTS package can process the data in many different ways, working on the variable in memory. The flip-side of this, of course, is that you want to be careful about storing *huge* resultsets in such variables. But given that warning, this technique can still be quite valuable. Here's how to do it.
Create an Execute SQL Task in the DTS designer. Select a strong>SQL connection and enter a T-SQL query of some sort--for example, a connection to the Northwind database and a SELECT * FROM Employees will do nicely. Then, click the Parameters button at the bottom of the Execute SQL Task Properties dialog box. Click Create Global Variables. In the Global Variables dialog box, enter a name for the variable, such as Employees, and leave the type as <other>. Then, click OK. Back in the Parameter Mapping dialog box, now select the &Output Parameters tab. Under Output Parameter Type, select Rowset. Then, choose the Employees global variable in the dropdown list. Click OK twice and you're all set. You can now work with the results of your query, using the global variable, in any downstream DTS tasks.