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

DTS to move database from one SQL server to another.

Hi,
Using SQL7 on both machines:
I used DTS to move a database from one server to another over the network.
The target server had a database established with the same names and table
structures as the source database. I just used the EM tools to commit the
DTS process and it seemed to work fine with no error. But I soon discovered
that there was some minor corruption on the target database.
So my questions are:
1. Is DTS a viable option for copying a database from one physical server to
another?
2. Should I (or do I) have to place the source server in single user mode or
some other state to prevent data corruption?
3. Is there a way to use DTS over the Internet to target the target machine?
I have not found a way to enter a destination IP in EM?
4. Do you guys/gals have any suggestions as to how to best do this?
Thanks,
Stefan
http://support.microsoft.com/default...b;en-us;314546
"Stef" <ss@.ss.com> wrote in message
news:9XRKd.11925$Yg6.1877490@.news20.bellglobal.com ...
> Hi,
> Using SQL7 on both machines:
> I used DTS to move a database from one server to another over the network.
> The target server had a database established with the same names and table
> structures as the source database. I just used the EM tools to commit the
> DTS process and it seemed to work fine with no error. But I soon
discovered
> that there was some minor corruption on the target database.
> So my questions are:
> 1. Is DTS a viable option for copying a database from one physical server
to
> another?
> 2. Should I (or do I) have to place the source server in single user mode
or
> some other state to prevent data corruption?
> 3. Is there a way to use DTS over the Internet to target the target
machine?
> I have not found a way to enter a destination IP in EM?
> 4. Do you guys/gals have any suggestions as to how to best do this?
>
> Thanks,
> Stefan
>
>

DTS to move database from one SQL server to another.

Hi,
Using SQL7 on both machines:
I used DTS to move a database from one server to another over the network.
The target server had a database established with the same names and table
structures as the source database. I just used the EM tools to commit the
DTS process and it seemed to work fine with no error. But I soon discovered
that there was some minor corruption on the target database.
So my questions are:
1. Is DTS a viable option for copying a database from one physical server to
another?
2. Should I (or do I) have to place the source server in single user mode or
some other state to prevent data corruption?
3. Is there a way to use DTS over the Internet to target the target machine?
I have not found a way to enter a destination IP in EM?
4. Do you guys/gals have any suggestions as to how to best do this?
Thanks,
Stefanhttp://support.microsoft.com/default.aspx?scid=kb;en-us;314546
"Stef" <ss@.ss.com> wrote in message
news:9XRKd.11925$Yg6.1877490@.news20.bellglobal.com...
> Hi,
> Using SQL7 on both machines:
> I used DTS to move a database from one server to another over the network.
> The target server had a database established with the same names and table
> structures as the source database. I just used the EM tools to commit the
> DTS process and it seemed to work fine with no error. But I soon
discovered
> that there was some minor corruption on the target database.
> So my questions are:
> 1. Is DTS a viable option for copying a database from one physical server
to
> another?
> 2. Should I (or do I) have to place the source server in single user mode
or
> some other state to prevent data corruption?
> 3. Is there a way to use DTS over the Internet to target the target
machine?
> I have not found a way to enter a destination IP in EM?
> 4. Do you guys/gals have any suggestions as to how to best do this?
>
> Thanks,
> Stefan
>
>

DTS to move database from one SQL server to another.

Hi,
Using SQL7 on both machines:
I used DTS to move a database from one server to another over the network.
The target server had a database established with the same names and table
structures as the source database. I just used the EM tools to commit the
DTS process and it seemed to work fine with no error. But I soon discovered
that there was some minor corruption on the target database.
So my questions are:
1. Is DTS a viable option for copying a database from one physical server to
another?
2. Should I (or do I) have to place the source server in single user mode or
some other state to prevent data corruption?
3. Is there a way to use DTS over the Internet to target the target machine?
I have not found a way to enter a destination IP in EM?
4. Do you guys/gals have any suggestions as to how to best do this?
Thanks,
Stefanhttp://support.microsoft.com/defaul...kb;en-us;314546
"Stef" <ss@.ss.com> wrote in message
news:9XRKd.11925$Yg6.1877490@.news20.bellglobal.com...
> Hi,
> Using SQL7 on both machines:
> I used DTS to move a database from one server to another over the network.
> The target server had a database established with the same names and table
> structures as the source database. I just used the EM tools to commit the
> DTS process and it seemed to work fine with no error. But I soon
discovered
> that there was some minor corruption on the target database.
> So my questions are:
> 1. Is DTS a viable option for copying a database from one physical server
to
> another?
> 2. Should I (or do I) have to place the source server in single user mode
or
> some other state to prevent data corruption?
> 3. Is there a way to use DTS over the Internet to target the target
machine?
> I have not found a way to enter a destination IP in EM?
> 4. Do you guys/gals have any suggestions as to how to best do this?
>
> Thanks,
> Stefan
>
>

dts that makes updates

hello, its posible that a dts looks the data and compara if the rows
exists in target database make a update of the row and else make a
insert... thanks...
Since DTS has a completely open programming interface, if you can write the
code for it, DTS can do it.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1136565790.166829.213060@.g14g2000cwa.googlegr oups.com...
> hello, its posible that a dts looks the data and compara if the rows
> exists in target database make a update of the row and else make a
> insert... thanks...
>
|||If you have a set of triggers writing to an audit table which DTS uses as a
basis to figure out what has changed, yes.
Otherwise I would look at transactional replication for something like this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1136565790.166829.213060@.g14g2000cwa.googlegr oups.com...
> hello, its posible that a dts looks the data and compara if the rows
> exists in target database make a update of the row and else make a
> insert... thanks...
>
|||Yes, DTS is highly programmable, especially if you script it out to VB. You
can do anything.
But I usually follow a practice of DTSing into a table reserved specifically
for the DTS data, matching the input format, and then I do the moving to
working tables from there. I feel this gives me some advantages:
1) DTS can be programmed, but it is a pain in the butt comparatively.
2) It gives me an exact record of what I received, so I am not wondering
whether an error was because of wrong/incomplete data or a wrong process.
3) It keeps the DTS as simple as possible so you have fewer failures.
4) The update process is clearly separated from the transfer process so it
is easier to debug.
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1136565790.166829.213060@.g14g2000cwa.googlegr oups.com...
> hello, its posible that a dts looks the data and compara if the rows
> exists in target database make a update of the row and else make a
> insert... thanks...
>
|||Thanks a lot, i am worried because the tables are very big and i don't
wan't translate all the content if i don't need it.
|||If it was me, and it was feasible to do so, I would probably prefer to spend
the money on disk space and do it the way I usually do. I have programmed
DTS a few times and never found it especially enjoyable. It always seemed to
cost more in terms of my time than the extra disk space would be.
But that's just me and my situations. Your mileage may vary.
How much data are we talking about here?
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1136574548.819640.129250@.g49g2000cwa.googlegr oups.com...
> Thanks a lot, i am worried because the tables are very big and i don't
> wan't translate all the content if i don't need it.
>