Error handling(how can I read SQL Task name in Activex Script?) 1

[dobrios]

Hello,
I'm trying to read previous SQL task description in Activex script and having problem figuring out correct syntax. I can read package name, but can't get to the task level.

Here's what I'm trying to accomplish by doing that:
My package has 10 SQL tasks and each individual task does data transfer from one server to another for one table. Task's description is also a name of table being trasferred.

Whenever any task fails, I need to insert a message into log table with a message and failed table name. So, I created Activex script task and linked all SQL tasks to Activex on "Fail" flow.

Now I need to read damn task's description that failed.
Any ideas, guys?
Thanks much.

Steve

 

[dobrios]

I found solution. Apparently to do that, one has to loop through steps collection and filter step that failed.
Here's the code in case somebody will be looking for something simular:
Function Main()
Dim oPkg, oTask, oStep

Set oPkg = DTSGlobalVariables.Parent

For Each oStep in oPkg.Steps
If oStep.ExecutionStatus = DTSStepExecStat_Completed Then
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
' Set global Variable for table name
DTSGlobalVariables("gvTableName").Value = oStep.Description
' msgbox oStep.Description & " Table Transfer Failed"
End If
End if
Next

Main = DTSTaskExecResult_Success
End Function


Only problem I encountered is that this script works only with one SQL task. If I link more than one SQL task on "failed" status, then it stops working.

 

[MDXer]

Copy the error handleing active x task for each step in your workflow and set up an on fail workflow. THe reason it stops working when you set a number of steps to the task using on fail is that every step that has an on fail work flow must fail for the activex task to fire.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[dobrios]

Thanks MDXer
I was trying to have as few steps as I could, but seems that I'll have to add Activex script to each SQL task.


My tasks just multiply from there. 10 SQL tasks + 10 Activex task + 10 SQL tasks to do log table update.

Do you have any links/info on workflow conditions?
It seems to me there should be a way to say: "If ANY of these tasks fail, use this single script to do some action

 

[MDXer]

Well the upside is that the 10 activex tasks don't get run unless of a failure. and 30 tasks in a dts package really isn't that much.

As for conditional work flows if you can find info on it or get it working let me know. I have some package with 30+ error handeling tasks.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck