Solved

On step failure, DTS package not following workflow

Posted on 2007-04-09
8
2,350 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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Any time.  You know how to reach me.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

762 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

12 Experts available now in Live!

Get 1:1 Help Now