Hi!

I'm currently designing a DTS package with parameters - I call the DTS
package from my C# application after I've defined the global variables (also
in C#).

In the DTS package, I've mapped the global variables directly to parameters
in my SQL query (by using question marks in the query). This works great,
however; problems arise when I want these parameters to be optional. The DTS
package is simply moving data from one table to another; and the parameters
go in the WHERE clause (i.e. "WHERE value = ?"). When the global variable
("value" in this case) is empty, I don't want the value field to be in the
where clause at all (so all rows will be selected, regardless of their
value).

Any ideas? If this is cumbersome, too slow, impractical or just plain
stupid, I'd really appreciate suggestions as to how I otherwise may solve
the problem. I wish to avoid having two separate packages since the logic
apart from the where clause will be identical, and I don't want to have to
maintain it two places (in addition, it simplifies my C# application).

Thanks a lot!


Sincerely,
Nils Magnus Englund

So you build the SourceSQLStatement by hand.

You check the value of the GV first and either include or exclude the WHERE
clause

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Nils Magnus Englund" <nme (AT) orkfin (DOT) no> wrote

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote


That helped a lot, thanks


Sincerely,
Nils Magnus Englund