(SQL Server 2000, SP3a)

Hello all!

I have a DTS package that I'm working with, in which I have a query that I want to invoke
on a target SQL Server that will return a handful of rows. For each row, I want to set
some package properties (on another object in the package). What would be the best
approach to this? I thought that I might use the "Transform Data Task", even though I
don't really have a "Destination", per se (that is, I want to process each "Source" record
via an ActiveX script).

However, when I try and do this, I seem to be getting an error when I execute that
"Transform Data Task" step (something akin to "Execution Cancelled by User").

Is there some other way that I should approach this?

Regards,

John Peterson

Take a look at the DynamicProperties task. This will allow you to set DTS
properties based query that returns a scalar value. You'll need to specify
a separate query for each property.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote

Thanks, Dan -- but I can't seem to get my head around your suggestion. Basically, what I
want is to be able to specify a Source Query that would return a bunch of rows. Then, for
each row, I want to invoke some ActiveX snippet withOUT doing anything to a "Destination".
I don't see that it's too easy with DTS...


"Dan Guzman" <danguzman (AT) nospam-earthlink (DOT) net> wrote

In article <OEs7OZ4SEHA.3852 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, "John Peterson" <j0hnp (AT) comcast (DOT) net> wrote:
Open a recordset.
Loop thru it and do whatever you want during each loop.

Sorry, but I don't understand what you mean by <withOUT doing anything to a
"Destination">. Please elaborate.

If you want to assign many properties from a single query, below is an
example of the ActiveX script technique suggested by b_43 (AT) hotmail (DOT) com.


CREATE TABLE DTSPackageProperties
(
PackageName varchar(255) NOT NULL,
ObjectName varchar(255) NOT NULL,
PropertyName varchar(255) NOT NULL,
PropertyValue varchar(255) NOT NULL,
)

ALTER TABLE DTSPackageProperties
ADD CONSTRAINT PK_DTSPackageProperties
PRIMARY KEY(PackageName, ObjectName, PropertyName)

INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MySource', 'DataSource',
'C:\InputFiles\MyInputFile.txt')
INSERT INTO DTSPackageProperties
VALUES('MyPackage', 'MyDestination', 'DataSource',
'C:\OutputFiles\MyOutputFile.txt')

Function Main()
Dim conn, rs, sqlQuery
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;" & _
"Data Source=MyServer;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=MyDatabase"
sqlQuery = "SELECT ObjectName, PropertyValue"
sqlQuery = sqlQuery + " FROM DTSPackageProperties"
sqlQuery = sqlQuery + " WHERE PackageName = '"
sqlQuery = sqlQuery + DTSGlobalVariables.Parent.Name
sqlQuery = sqlQuery + "' AND PropertyName = 'DataSource'"
Set rs = conn.Execute(sqlQuery)
Do While rs.EOF = False

DTSGlobalVariables.Parent.Connections(rs.Fields("O bjectName").Value).DataSou
rce = _
rs.Fields("PropertyValue").Value
rs.MoveNext
Loop
rs.Close
conn.Close
Set rs = Nothing
Set comm = Nothing
Main = DTSTaskExecResult_Success
End Function

The alternative DynamicProperties task method would use the following
queries to assign the properties.

SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MySource' AND
PropertyName = 'DataSource'

SELECT PropertyValue
FROM DTSPackageProperties
WHERE
PackageName = 'MyPackage' AND
ObjectName = 'MyDestination' AND
PropertyName = 'DataSource'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Peterson" <j0hnp (AT) comcast (DOT) net> wrote

Thanks Dan (and bb_43)!

I had hoped there would have been a simpler solution in the context of existing DTS
objects, rather than having to write a lot of code. Alas, it seems like it's not quite
the case, even though DTS seems uniquely qualified to do this type of thing (almost).
Since it can use a Connection to issue a query on that remote server and process the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Query Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX transformation
script for each row without having the data ultimately going somewhere.

Thanks again!

John Peterson



"Dan Guzman" <danguzman (AT) nospam-earthlink (DOT) net> wrote

John,
if you do want to use the Transform Data Task without inserting rows you can
change the DTSTransformStatus constant from DTSTransformStat_OK to
DTSTransformStat_SkipInsert.
HTH,
Paul Ibison

<blush> I did not know such a return value existed! Thanks so much, Paul -- I'm sure
that'll do the trick! (And I think you pegged my issue *exactly*!)


"Paul Ibison" <Paul.Ibison (AT) Pygmalion (DOT) Com> wrote

No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
http://www.amazon.co.uk/exec/obidos/...145180-8774263
Regards,
Paul Ibison