How to skip a step in DTS

[klewis10367]

I have a DTS package that looks to a file location to import into sql. The problem I am having is that the file does not always exist or has a size of 0. I have four other steps within the DTS package that do the same thing but for different file locations. The problem I have is that if there is no file in one of the steps the whole task fails, so I have set all of the workflow options to completion, then at least the package will complete. Is there any to create an Active X script to check for either file size or if exists, that will (if fails )continue on with the package reporting success?

 

[AlexCuse]

You need to use file system object to check if the file exists, then if it does not:

1. Disable the next step
2. Enable an 'exit' step (another script task is how I've usually done it).

I will try to mock up an example for you in the next few minutes.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Ok, that was easier than I thought. Here is the function for the 'fork' in your workflow (you'll need to mess with the global variables a bit)

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

Function Main()
	
Dim objFSO
Dim objPKG
Dim stpContinuePkg
Dim stpExitBadDirectory

set objPKG = DTSGlobalVariables.Parent

set stpContinuePkg = objPKG.Steps("DTSStep_DTSActiveScriptTask_3")
set stpExitPkg = objPKG.Steps("DTSStep_DTSActiveScriptTask_2")

'This check will be used to disable/enable subsequent steps
DTSGlobalVariables("@CheckErrors").Value = ""

set objFSO = createobject("Scripting.FileSystemObject")

'This is the actual check for valid directory
if objFSO.FolderExists (DTSGlobalVariables("@FileLoc").Value) <> "True" then
	DTSGlobalVariables("@CheckErrors").Value = cstr(DTSGlobalVariables("@CheckErrors").Value) & _
		 VbCrLf & "Source File Directory Not Found" 
end if

'Decide what to do next based on value of @CheckErrors
If len(DTSGlobalVariables("@CheckErrors").Value) > 2 Then
	stpContinuePkg.DisableStep = True
	stpExitPkg.DisableStep = False
else
	stpContinuePkg.DisableStep = False
	stpExitPkg.DisableStep = True
end if



Main = DTSTaskExecResult_Success
End Function

and here is the 'exit' function:

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

Function Main()

	MsgBox ("Some defined directories were invalid." & vbCrLf & "Please check Global Variables" & vbCrLf & DTSGlobalVariables("@CheckErrors").Value)
	Main = DTSTaskExecResult_Success
End Function

Sorry about the formatting, but I think it will paste alright.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[klewis10367]

Thanks for you reply...I have attached what my code looks like for just evaluating if the file exists, but what I want to include in the code is also to check to see if the file exists and the size > 0...any help would be appreciated...

Function Main()
Dim oFSO, sFileName

' Get the name of the file from the global variable "ImportFileName"
sFilename = DTSGlobalVariables.Parent.Connections("GDM").DataSource
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Check for file and return appropriate result
If oFSO.FileExists(sFilename) Then
Main = DTSStepScriptResult_ExecuteTask
Else
Main = DTSStepScriptResult_DontExecuteTask
End If

Set oFSO = Nothing
End Function

 

[AlexCuse]

Try something like this. ALso, try using the [ignore]

[/ignore] tags around your posts, it makes them easier to read. This is all 'air code' and untested

Function Main()
        Dim oFSO, sFileName

        ' Get the name of the file from the global variable "ImportFileName"
       sFilename = DTSGlobalVariables.Parent.Connections("GDM").DataSource
        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Set oFile = oFSO.GetFile(sFilename)
                iFileSize = oFile.Size
                
                'Check file size
                If iFileSize > 0 Then
                   Main = DTSTaskExecResult_Success
                Else 
  [b]'not sure if Failure is the right keyword to use here[/b]
                   Main = DTSTaskExecResult_Failure
                End 

        Else
                Main = DTSStepScriptResult_Failure
        End If

        Set oFSO = Nothing
End Function

I also offer you this, in case you want to actually go to a different step in the sequence in case of an error.

Function Main()
        Dim oFSO, sFileName, oFile, iFileSize
        Dim stpContinuePkg, stpExitPkg, objPKG

set objPKG = DTSGlobalVariables.Parent

set stpContinuePkg = objPKG.Steps("DTSStep_DTSActiveScriptTask_3")
set stpExitPkg = objPKG.Steps("DTSStep_DTSActiveScriptTask_2")

        ' Get the name of the file from the global variable "ImportFileName"
       sFilename = objPKG.Connections("GDM").DataSource
        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Set oFile = oFSO.GetFile(sFilename)
                iFileSize = oFile.Size

                If iFileSize > 0 Then
                   stpContinuePkg.DisableStep = False 
                   stpExitPkg.DisableStep = True
                Else 
                    stpContinuePkg.DisableStep = True
                    stpExitPkg.DisableStep = False
                End If
        Else
                stpContinuePkg.DisableStep = True
                stpExitPkg.DisableStep = False
        End If

        Set oFSO = Nothing
        Set stpCOntinuePkg = Nothing
        Set stpExitPkg = Nothing
        Set oFile = Nothing
        Set objPKG = Nothing

Main = DTSTaskExecResult_Success
        
End Function

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom