DTS: How can I process each row in result set to access properties on another package obje

(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 PetersonTake 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. 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...
> 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
> >
> >
>|||In article <OEs7OZ4SEHA.3852@.TK2MSFTNGP10.phx.gbl>, "John Peterson" <j0hnp@.comcast.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...
>
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...
> > 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 (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@.nospam-earthlink.net> wrote in message
news:Oj%23iSG9SEHA.3476@.tk2msftngp13.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...
> 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...
> > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||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 can
> 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:
http://www.amazon.co.uk/exec/obidos/ASIN/0672320118/qid=1086594526/sr=1-1/ref=sr_1_2_1/202-5145180-8774263
Regards,
Paul Ibison