Hello everyone,

I'm using the following DTS package to extract table
contents into a .txt based list file.

select o.name from Object o,viewhierarchy v where
parentobjid = 3829 and o.objid = v.objid

My intent is to script this operation using DTSRUN and
then to take advantage of the /A option to supply dynamic
values and replace the 3829 value in my above example.
Could anyone provide me with a little assistance in how I
would modify this package to reflect the values assigned
with DTSRUN? I've got just enough knowledge to be
dangerous here, so any help would be greatly appreciated!

Thanks,

Mark

In article <024c01c37c84$f4e2fdf0$a401280a (AT) phx (DOT) gbl>, Mark Todd
<mtodd (AT) veritas (DOT) com> writes
First create a global variable in your package to hold the parentobjid
value. String type will be fine. Enter any value for now.

In the task, replace the 3829 with ? and click Parameters, and then map
the first (only) parameter to your global variable. You can now change
the global variable and this will be reflected in the query.

Using /A to change the value gives you the next link for automating the
process as you know.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

In article <0a8301c37d41$11bfe200$a001280a (AT) phx (DOT) gbl>, Mark Todd
<mtodd (AT) veritas (DOT) com> writes
The parameter support in DTS is basically the OLE-DB parameter
functions, which are very similar to T-SQL, so think about this

DECLARE @InString varchar(20)
SET @InString = '3829,26080,23919'
Select o.name from Object o,viewhierarchy v where
parentobjid in (@InString) and o.objid = v.objid


This will fail in T-SQL and similarly fails in DTS, so no you cannot do
it this way. What you want is parameter support as string substitution,
which is not what DTS gives you. The good news is that by using an
ActiveX Script Task to change the SQLStatement at run-time you can do
this-

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

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks, Darren. I've got one more question, though. As
stated in the query above, I am returning values from a
single column value and it works. However, I have a
variant of the above query that returns values for several
column values which is included here:

select o.name from Object o,viewhierarchy v where
parentobjid in
(11996,11740,13677,3828,11737,11738,11739,11236,11 741,11742
,11239) and o.objid = v.objid

Trying to replicate this with the DTSRUN command, I tried
to use the following:

dtsrun /s svlniq02 /e /n test2 /a ParentFolder=26080,21232

When I try to run this, I get this error:

Error: -2147217904 (80040E10); Provider Error: 0 (0)
Error string: No value given for one or more required
parameters.
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0

Any idea how I would pass multiple values here? Before
you tell me to use multiple Global Variables, keep in mind
that the number of entries would be different, and
whenever I try to use multiple global variables with empty
values, I get errors. Is there a way to do avoid this?

Thanks,

Mark

In article <051b01c37d55$da351570$a301280a (AT) phx (DOT) gbl>, Mark Todd
<mtodd (AT) veritas (DOT) com> writes
Did you miss my last post, or did it get lost? Here it is again-

The parameter support in DTS is basically the OLE-DB parameter
functions, which are very similar to T-SQL, so think about this

DECLARE @InString varchar(20)
SET @InString = '3829,26080,23919'
Select o.name from Object o,viewhierarchy v where
parentobjid in (@InString) and o.objid = v.objid


This will fail in T-SQL and similarly fails in DTS, so no you cannot do
it this way. What you want is parameter support as string substitution,
which is not what DTS gives you. The good news is that by using an
ActiveX Script Task to change the SQLStatement at run-time you can do
this-

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

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org