Solved

On step failure, DTS package not following workflow

Posted on 2007-04-09
8
2,359 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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