How can I process each row in result set to access properties on another package o

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@.comcast.net> wrote in message
news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> (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
>Thanks, Dan -- but I can't seem to get my head around your suggestion. Basi
cally, what I
want is to be able to specify a Source Query that would return a bunch of ro
ws. 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@.nospam-earthlink.net> wrote in message
news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
> 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 specif
y
> a separate query for each property.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:O4WyV0zSEHA.3332@.tk2msftngp13.phx.gbl...
> want to invoke
> I want to set
> the best
> even though I
> "Source" record
> execute that
> User").
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.net> wrot
e:
>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 r
ows.
> 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...
>
Just do it in a VBScript task.
Open a recordset.
Loop thru it and do whatever you want during each loop.|||> 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...
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@.hotmail.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("ObjectName").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@.comcast.net> wrote in message
news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> 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@.nospam-earthlink.net> wrote in message
> news:u8ri%23s0SEHA.3608@.TK2MSFTNGP11.phx.gbl...
DTS[vbcol=seagreen]
specify[vbcol=seagreen]
that I[vbcol=seagreen]
row,[vbcol=seagreen]
would be[vbcol=seagreen]
Task",[vbcol=seagreen]
each[vbcol=seagreen]
>|||Thanks Dan (and bb_43)!
I had hoped there would have been a simpler solution in the context of exist
ing 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 pro
cess the rows.
The only problem is that both the "Transform Data Task" and "Data Driven Que
ry Task" seem
to *require* a "destination" object; that you can't simply have an ActiveX t
ransformation
script for each row without having the data ultimately going somewhere.
Thanks again!
John Peterson
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> 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@.hotmail.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("ObjectName").Value).DataS
ou
> 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@.comcast.net> wrote in message
> news:OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl...
> Basically, what I
> rows. Then, for
> a "Destination".
> DTS
> specify
> that I
> row,
> would be
> Task",
> each
>|||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@.Pygmalion.Com> wrote in message
news:%23MUhxx$SEHA.2128@.TK2MSFTNGP11.phx.gbl...
> John,
> if you do want to use the Transform Data Task without inserting rows you c
an
> change the DTSTransformStatus constant from DTSTransformStat_OK to
> DTSTransformStat_SkipInsert.
> HTH,
> Paul Ibison
>|||No problem. FYI I came across this info from this book which is the most
comprehensive DTS book I know of:
_2_1/202-5145180-8774263" target="_blank">http://www.amazon.co.uk/exec/obidos...5145180-8774263
Regards,
Paul Ibisonsql