Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DTS question

Posted on 2006-05-17
8
Medium Priority
?
336 Views
Last Modified: 2013-11-30
i wrote an activex script , and i am getting errors, what am i doing wrong:

'**********************************************************************
'  Visual Basic ActiveX Script
'**********************************************************************

Function Main()
        Dim oFSO, oFolder
     
      
      set oFSO = createobject("scripting.filesystemobject")
      sPath = "C:\scripts\import"
      set oFolder = oFSO.GetFolder(sPath)

      msgbox oFolder.Path & " has " _
        & oFolder.Files.Count & " files and " _
           & oFolder.SubFolders.Count & " folders" _
           ,vbInformation+vbSystemModal _
           ,"Folder content count"

  If (oFolder.Files.Count > 0) Then
                 
            Main = DTSStepScriptResult_ExecuteTask
        Else
             
      Main = DTSStepScriptResult_DontExecuteTask
        End If

        Set oFSO = Nothing


End Function



I want to go to the next task when file.count is >0...if i do   ' Main = DTSTaskExecResult_Failure it will just fail totally

0
Comment
Question by:lytung818
  • 4
  • 2
  • 2
8 Comments
 
LVL 5

Expert Comment

by:bwdowhan
ID: 16704836
Try this:

Function Main()
        Dim oFSO, oFolder
     
     set oFSO = createobject("scripting.filesystemobject")
     sPath = "C:\scripts\import"
     set oFolder = oFSO.GetFolder(sPath)

     msgbox oFolder.Path & " has " _
       & oFolder.Files.Count & " files and " _
          & oFolder.SubFolders.Count & " folders" _
          ,vbInformation+vbSystemModal _
          ,"Folder content count"

 If (oFolder.Files.Count > 0) Then
     Main = DTSTaskExecResult_Success
 Else
     Main = DTSTaskExecResult_Failure
 End If

 Set oFSO = Nothing

End Function


From the Active-X script, you can use workflow properties to define which step to go to on success and what to do on failure (maybe nothing)...
0
 

Author Comment

by:lytung818
ID: 16704869
when it goes on failure it means that i get an error message. is there a way to supress thesse messsages? if i do a scheduled dts package, will these messages pop up?

0
 

Author Comment

by:lytung818
ID: 16704882
also i have this error handler

On error go to ErrHandler

code here---


ErrHandler:

Main = DTSTaskExecResult_Failure

Resume Exit Sub



i get an expected statement error. what am i doing wrong?

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16705394
>>is there a way to supress thesse messsages?<<
You need to remove all MsgBox and any other interactive function (such as InputBox) from Scheduled DTS Packages.

>>i get an expected statement error. what am i doing wrong?<<
You are using VB syntax in VB Script.  There is no "On error go to ErrHandler" (as an aside that syntax is not even correct in VB) and there is no "Resume".  The best you can do in VBScript is:

On Error Resume Next
0
 

Author Comment

by:lytung818
ID: 16709826
1. thanks how do i remove the msgbox from scheduled DTS package?

2. okay instead of doingthis:

 If (oFolder.Files.Count > 0) Then
     Main = DTSTaskExecResult_Success
 Else
     Main = DTSTaskExecResult_Failure
 End If


is there a way to prevent goingto the next step when the count is < 0?
0
 

Author Comment

by:lytung818
ID: 16710488
okay i figured out #2. I disabled the next step. Ignore that question

now aboutthe error handling. I dont want to do On Error Resume Next.  On any error i want to alert me by email but i can't seem to be able to do:

On error
Main = DTSTaskExecResult_Failure
0
 
LVL 5

Accepted Solution

by:
bwdowhan earned 300 total points
ID: 16713327
Hi lytung818,

I will start by saying that error handling in DTS is not an easy task. There is no magic object, that I know of, that you can just call when any error occurs in your DTS package. Error handling needs to be controlled through workflow. Even if you create a custom task, you still need to use workflow to control when the error handler is called. This means that if you want to send an e-mail when any step fails and you have 5 steps, you will need to have 5, on failure workflows that point to an e-mail task (not 5 failures pointing to 1 e-mail task because that would mean that all 5 pieces need to fail). If you have Outlook installed on the server and a profile configured, you can use the DTS Mail Task to send messages. From each DTS Task, you would have a failure precendence pointing to an e-mail task and a success precendence pointing to the next task to execute.

Brian
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 16714594
>>now aboutthe error handling. I dont want to do On Error Resume Next.<<
Than to put it mildly you are SOL or if you will up the creek without a paddle.  In case you did not know, On Error Resume Next is the only error handling available in VBScript.  If you can upgrade to SQL Server 2005's SSIS than you can use .NET's far more efficient Try Catch error handler.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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.
Suggested Courses

864 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