Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

On step failure, DTS package not following workflow

Posted on 2007-04-09
8
Medium Priority
?
2,370 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 93

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 93

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 2000 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 93

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

604 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