Hi !

I want to export data from a very big table on my SQL-server, to a
texfile, using DTS. The job is running every night.
The table contains 1.000.000 records, and i only want to export the
records that have been added to the table, since the last export.
Therefore i have a field (field3) that tells me, if the record has
been exported.


Actually, i want to execute 2 statements:

SELECT field1, field2 FROM table1 WHERE field3 IS null; /*give me the
records to export*/

UPDATE table1 SET field3 = 1 WHERE field3 IS null;; /*update the
record, so it is not exported again*/

I have created a "Transform Data Task", and specified the 2 statements
as SQL-query. But only the select statement is executed. Not the
update-statement.

Can't i use the "Transform Data Task" to UPDATE data ???

If not, how can i prevent records from beeing exported more then once
???

Thank's for your help !

Ole,

I would seperate these out into 2 seperate tasks, the SELECT being in a
transform data task and the UPDATE being in an Execute Sql Task. When your
SELECT/Transform data task completes successfully and outputs your data
whereever it needs to go, either On Success or On Completion call the next
task, the UPDATE/Execute Sql Task, to update the data.

HTH,

Raymond Lewallen

"Ole Hansen" <ohahaahr (AT) hotmail (DOT) com> wrote

Hi,

Is there a feature or method to start a another task from my transform
data task ? Where do i fill in the name of the second task ? Do i have
to make an activeX script to do so ? (If so, that would actually be my
first activeX script ever...)

(Of course i can schedule the second task to start 5 minutes after the
first task, but that is NOT a god solution.)

Regards,

Ole


"Raymond Lewallen" <Raymond.CTR.Lewallen (AT) nospam (DOT) faa.gov> wrote

Ole,

Use workflow to start a process after one has completed (On Success, On
Completion, On Failure). When the SELECT process completes, the On Success
workflow properties should specify that the next task be the UPDATE process.
I would take a look at the DTS samples in the BOL to give you a better
understanding of what you should be doing.

Raymond Lewallen

"Ole Hansen" <ohahaahr (AT) hotmail (DOT) com> wrote

There are several ways of accomplishing this task, depending upon your
choice, you might want to look at:

a) Writing a Stored Procedure - in this particular scenario, I would sugges
that a stored procedure would be your best bet. Through it, you can
shcedule to take the output from the table at a predefined frequency and
also update the table with the flag. Also, its more reliable and efficient
to do so, and you dont have to learn any other language.

b) You can use the workflow to start a process once the earlier task is
complete and as someone in this thread mentioned, it could be on success or
failure etc.

Sanchan [MSFT]

sanchans (AT) online (DOT) microsoft.com

This posting is provided "AS IS" with no warranties, and confers no rights.

Great. It works !!!!

Thank you very much for your help.

Regards,

Ole

"Raymond Lewallen" <Raymond.CTR.Lewallen (AT) nospam (DOT) faa.gov> wrote