DTS Workflow - On Success? On Completion?

Question

DTS Workflow - On Success? On Completion?

Nicholas Wang

Default port

Points: 1463
More actions
September 3, 2003 at 5:22 pm

#106584

A while ago I asked for help in the Performance Tuning board, I have since completely my project, thank you for all who had helped.
http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=15216
Now, I have a question about using DTS. I want to check a table for its last updated time, if the table was last updated at the scheduled interval successfully, then I want to compare the attributes between that table with a view, and insert a row_id into a third table if discrepancies exist.
(The first table is our web products' data loaded from Oracle, we'll call it the web_table. The view is created from our CRM database, it is data of our web products that Oracle *should* contain. We call it the crm_table. The third table is used to hold the company_id and row_id of the companies/products where discrepancies between the two systems occur. We'll call it the error_table. Now, let's say the web_table is loaded from Oracle every day at 8:00am.)
So, I use an "Execute SQL Task" and- SELECT TOP 1 last_updated_time FROM web_table.
IF last_updated_time < CAST(CONVERT(char(10),getdate(),120) + ' 08:00:00.000' AS datetime)
THEN I want it to FAIL.
ELSE I want it to SUCCESS (and TRUNCATE error_table).
So that "On Success", it can start a "Transform Data Task" -
SELECT crm.company_id, crm.row_id
FROM web_table w
INNER JOIN crm_table crm
ON w.row_id = crm.row_id
WHERE w.attribute1 <> crm.attribute1
OR w.attribute2 <> crm.attribute2
OR {... etc}
The result will be inserted into the error_table.
Question is, HOW DO I MAKE IT FAIL? Or should I say, how do I make it stop before the "Transform Data Task"?
Thanks for any help!

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

Presuming that you do the "IF last_updated_time < ..." in an ActiveX Script step, then all you need to do is set the appropriate execution status.

Have a look at DTSTaskExecResult in Books Online.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

dave_is_here Old Hand Points: 312 More actions · Answer 2

Don't forget to output the "last_updated_time" from the "Execute SQL Task" to a package global variable, then use that variable in the "IF" statement in the "ActiveX Task".

I spent a while on something like this mucking about with ADO connections before realising that a global variable could be used to capture output from a SQL task.

The perils of self taught DTS programming!

Dave Leathem.
It's just what we asked for but not what we want! (The Customer's Creed)

Antares686 SSC Guru Points: 125444 More actions · Answer 3

DTSTaskExecResult is related to scripting.

For SQL code use RAISERROR with RETURN to force a failure message to the message pump.

Nicholas Wang Default port Points: 1463 More actions · Answer 4

quote:
DTSTaskExecResult is related to scripting.
For SQL code use RAISERROR with RETURN to force a failure message to the message pump.

I thought about using RAISERROR & RETURN... but I just wished there would be an easier way built into DTS . Oh well, thanks for the help.

philcart SSC-Forever Points: 47796 More actions · Answer 5

You could capture the output of the last_updated_time in a global variable, then have a short ActiveX Script on the workflow properties of the datapump task to determine if it executes or not.

That way you're not raising an error condition that causes a failure.

Take a look at the following,

http://www.sqldts.com/default.aspx?214

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

Edited by - phillcart on 09/04/2003 5:22:37 PM

--------------------
Colt 45 - the original point and click interface

Antares686 SSC Guru Points: 125444 More actions · Answer 6

That would be using

DTSTaskExecResult

which means you return retry failure or success. So you actually are returning a failure message thru the message pump. Just not thru the errors collections.

For you to determine if a process needs to end based on a value in a query you will have to return a failure message one way or another.

philcart SSC-Forever Points: 47796 More actions · Answer 7

The script is attached to the workflow properties. So it executes before the Datapump task. You use the DTSStepScriptResult_ExecuteTask or DTSStepScriptResult_DontExecuteTask constants to execute or skip the task.

Here's an MSDN article that gives a few examples of what can be done.

http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/dts_elemwkflow_0793.htm

It's not a stand-alone ActiveX Script task. So DTSTaskExecResult doesn't come into it.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Thanks phillcart, something new I had not used yet. I have a developer who has several places this will come in handy.