"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
>
>

"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...
>

> 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...

"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
> > >
> > >
> >
> >
>
>

"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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

"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
>
>