Bandwidth Monitor
SQLDTS.comSQLIS.comSQLIS Wiki
How to Use Lookups in DTS
By Allan Mitchell
Version 7.0/2000
Level Intermediate
Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7 | Page 8 | Page 9

If you wanted to assign the results of a statement to a globalvariable then in the Output Parameters tab is where you'd do it.

Figure 1.11 Output Parameters.

Output Params

There's a few things to explain on this tab

1. None means there are no output Parameters (The default)

2. Row Value means we can assign to a Global Variable a single value from the statement

3. Rowset means we can a multi value recordset from the statement to a Global Variable.

This last one is the most interesting. We can take the Global Variable and use it in an ActiveX Script as a recordset and manipulate it using ADO. Before we conclude we just want to show you another place where you can use parameters/Lookups. Although this is on a Data Pump we left it until now because you assign the variables like you would with the ExecuteSQL task. You can use them in the Source statement for your data pump. Remember this is SQL Server 2000 only.

Figure 1.12 Show Source Statement Parameters.

AltDataPump

Conclusion

As you can see Lookups can be very powerful things and you'll find yourself using them to get out of some sticky situations. A word of caution though in using them in a Data Pump task. In order to use them SQL Server must do processing row by row and for each row it has to do a lookup to the lookup server. Be aware of this when using them.

Page 1 | Page 2 | Page 3 | Page 4 | Page 5 | Page 6 | Page 7 | Page 8 | Page 9