Solved

On step failure, DTS package not following workflow

Posted on 2007-04-09
8
2,351 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
 
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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Union 20 44
HasClusteredColumnStoreIndex: unknown property 2 56
Optimizing a query 3 33
SQL log file keeps growing despite getting successful log backups 4 23
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now