Solved

On step failure, DTS package not following workflow

Posted on 2007-04-09
8
2,353 Views
Last Modified: 2013-11-30
(SQL Server 2000)

Experts,

I am fairly handy with writing SQL, and I have gotten my hands dirty with DTS before,
but I am hardly an Expert.

I have an ActiveX step using the following code:





Function Main()
Dim fso, f, f1, fc, strFileName, strFilePath,  pkg, oConn, oConn2, Counter, DateStr

      On Error Resume Next

      set pkg = DTSGlobalVariables.Parent
      'strFilePath = DTSGlobalVariables("strFilePath").Value & "\VolumeData\"
      strFilePath = DTSGlobalVariables("strFilePath").Value
      Set oConn = pkg.Connections("Community")
      Set oConn2 = pkg.Connections("Metro")

      DTSGlobalVariables("strFileCommunity").Value = ""
      DTSGlobalVariables("strFileMetro").Value = ""

      Set fso = CreateObject("Scripting.FileSystemObject")
      Set f = fso.GetFolder(strFilePath )
      Set fc = f.Files

      Counter = 0      'number of matching files found.  Needs to be 2, one Metro and
                  'one Community, each with same date

      For Each f1 In fc      
            strFileName = UCase(f1.Name)

            If Left(strFileName,14) = "FTE_COMMUNITY_" then
                  DTSGlobalVariables("strFileCommunity").Value = strfilename
                  DateStr = Mid(strFileName, 15, 8)
                             oConn.datasource = strFilePath &  strFileName
                  Counter = 1
                  Exit For
            end if            
      Next

      If Counter = 1 Then
            For Each f1 In fc      
                  strFileName = UCase(f1.Name)
                  If strFileName = "FTE_METRO_" & DateStr & ".XLS" Then
                        DTSGlobalVariables("strFileMetro").Value = strfilename
                                   oConn2.datasource = strFilePath &  strFileName
                        Counter = 2
                        Exit For
                  end if
            Next
      End If

      Set fc = Nothing
      Set f = Nothing
      Set fso = Nothing
      Set oConn = Nothing
      Set oConn2 = Nothing

      If Counter = 2 Then
              Main = DTSTaskExecResult_Success
      Else
            Main = DTSTaskExecResult_Failure
      End If

End Function





The DTS package then branches to another step on success, and a different, other step
on failure.

When the step succeeds, all is well with the world--the package moves on to the "on success"
step, skips the "on failure" step, and keeps going.

However, if I change a file name so that I do not get matches to the naming convention,
the step fails (error message: The task recorded failure on execution), and yet the package
does not move to the "on failure" step indicated in the workflow.

The breakdown is definitely occurring at the Main = DTSTaskExecResult_Failure
line; I confirmed this by sprinkling the code with MsgBox debuggers, since
removed.

I would really appreciate any insight the Experts could give me on this one; the desired
outcome is for the package to actually go to the "on failure" step if this step fails.

Regards,

Patrick
0
Comment
Question by:Patrick Matthews
  • 5
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18877400
>>and yet the package does not move to the "on failure" step indicated in the workflow.<<
One reason, is that you are sharing the "Failure" task with another (different) workflow.

But your best bet is to upload an image of the DTS Package so that we can get a better understanding of what is going on.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18877413
Also, lose this:
On Error Resume Next

It causes more harm than good.

And finally, are you sure that the following line is getting executed:
Main = DTSTaskExecResult_Failure
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 18877575
OK, here is a screen shot of the package design:

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/3129-dts.zip 


The code is for the very first step, "Get Source File Name".

I will try removing On Error Resume Next.  I am sure it is getting to the
Main = DTSTaskExecResult_Failure line because I placed a MsgBox
immediately before it in my testing; the message came up and then the
error followed right away.

Regards,

Patrick
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 92

Author Comment

by:Patrick Matthews
ID: 18877620
I removed the error handling and changed the last block of code to:

      If Counter = 2 Then
              Main = DTSTaskExecResult_Success
      Else
msgbox "before"
            Main = DTSTaskExecResult_Failure
msgbox "after"
      End If


I did get both message boxes appearing, but I still got the error and the
package did not move on failure to the indicated step.

Regards, and thanks for looking into this,

Patrick
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18880032
Yep, it looks like I was right in my first comment.  One look at your image and that was enough.  The problem is the only way the "Move to Failed ..." Task will execute if all 5 steps fail.  That will never happen without a lot of additional complicated code.  My strong recommendation is for you to create one Failure Task for each event.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 18880063
Think of it this way.  DTS (unlike SSIS) can only use AND logic and not OR Logic.  You want: If Task 1 or Task 2 or Task 3 or Task 4 or Task 5 fail than branch.  But unless you have some code you have not told us about, than what is happening is If Task 1 And Task 2 And Task 3 And Task 4 And Task 5 fail than it will execute the Failure task.

Take a look at this article:
Multiple Paths in Workflow
http://www.sqldts.com/218.aspx

And you should understand better what is happening and why it is best to NOT use that approach for failing tasks.
0
 
LVL 92

Author Comment

by:Patrick Matthews
ID: 18881775
OK, I get it now.  In retrospect it seems so simple :)

Thanks so much for your help,

Patrick
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 18883790
Any time.  You know how to reach me.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question