I have a Table with the following schema:

StudentID Name Address Score Passed
============================

I'd like to create a DTS package which iterates through all the records in
this table and set the flag "Passed" to 1 if :

1) Score is greater than 15
and
2) Address is not null


Thanks

You don't need to use DTS to do this, it's an update statement you can run
from Query Analyzer.
UPDATE
TABLENAME
SET
Passed = 1
WHERE
Score > 15
AND Address IS NOT NULL

But if what you were getting at is that you want this to happen
automatically via an agent job, then just put the update statement in an
"Execute SQL Task" and schedule it to run when you want it to.

But easier still, if you're just looking to run a scheduled job, just put
the update statement within a new job step of type "Transact-SQL Script
(TSQL)" and schedule it to run.


"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Simon ,

Thanks for yuor reply,
Here is the whole story,
I have a table with the same schema ,I posted in previous message.I need to
check that considition and do the update and then trnasform the data of the
Table to another table(s).That's the reason I decided to use DTS package.

I'd like to do this(I'm not sure if its possible). I'd like to use "Execute
SQL Task" to return some rows from the Table (In a global variable as a
rowset) ,then I'd like to validate each fields in each record and then
update some flags in the same Table.(I don't know how to do that in DTS).I
have transformation anyways,but I'm not quite sure wether I can do the
validation in DTS package (probably using VBScript) or not.

Thanks again for your help.

Ray
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

You can do validation in DTS data pump tasks using either lookups (next tab
over from transformations), or by using multiphase data pumps (this isn't
turned on by default I don't think, so right click the "data transformation
services" node in enterprise manager, and click properties. Then make sure
there is a check mark in multiphase data pumps.). Using multiphase data
pumps allows you to manipulate each row as it's being processed or after
it's processed.

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Simon,

Thanks for your nice help.

Validation is not the metter only ,I need to set some flags on the record if
the validation is not met,for example if one of the fields is null I should
set the flag "IsNull" in the current record and write the error
in another field .Can multiphase data pumps do that? If not I can propably
handle it using Acivex Script and ADO object right?


My other concern is that besides Validation I have another kind of checking
which is:

I have to check each row's primary key to see if it exists in another table
or not ,if yes one flag in the current row must be set and if not the
current row must be inserted into that Table.
What's your suggession for this?

Thanks
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

You can do that within an ActiveX script for the transformation of the
column(s). Just write your code in there to make the updates to your data
as the records are passed through the data pump.

"RayAll" <RayAll (AT) microsft (DOT) com> wrote

Simon,

And also:

Even for checking the foriegn keys I can use the Activex?


Thanks
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

yes you can, you can run queries within activeX to check constraints. You
would probably be better off doing such logic within a lookup as part of the
transformation however, here's some good info on using lookups with data
pump tasks.

http://www.sqldts.com/default.aspx?277,1

www.sqldts.com has lots of links to common (and some not so common) tasks
performed in DTS - you probably find it valueable to bookmark that site.

Let me know if you have any issues.

Simon

"ALI-R" <Ray (AT) Alirezaei (DOT) com> wrote

Simon,

I so appreciate your valuable help.I'll drop you a message if I have any
problems ,I'm going to impelment it by the end of this week and let you know
then

again,thanks

Ali-R
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote