changing DTS(data transformation service) options in sql server 2000 through SQL Query Analyzer

Question

How do i change DTS options in sql server 2000 through SQL Query Analyzer? Such options are source, destination, transformation columns, etc..

Answer 1

You can't edit a DTS package through Query Analyzer; completely different set of tools. You do have a couple of options, however:

The closest you could come to this (and my DTS skills are very rusty) is to design your DTS package to look at a Parameters table in a SQL database, and edit those parameters via Query Analyzer.

Your second option (assuming you have xp_cmdshell enabled on your server) would be to use external variables in DTS, and use DTSRUN via xp_cmdshell to pass in those external variables. This is a bit of a kludge, but if you're trying to call a DTS package from a stored procedure with different parameters, it may be more robust than the first method (i.e., allows multiple instances of the proc to run without interference).

HTH, Stu