how do I stop a ssis package if there are no files?

Question

I have a ssis package, to which the first stage is an Execute Process step. Inside the Execute Process Stage, I am asking a 7z command line to explode a zip file from a given folder.

If there are no zip files, the SSIS package fails. There will be days without zip files. how do I finish successfully, and end gracefully in this case?

Thanks

You could wrap it inside a for each file loop. If there are no files the loop doesn't run. — Nick.McDermaid
Nick, your solution worked. can you please put it in an answer? Thanks. — arcee123

Tarzan Tarzan · Accepted Answer · 2016-10-11T13:46:06

This is how you can stop an SSIS package if there are no files.

Programmatically check to see if the file exists.
Use a Precedence Constraint, and only attempt to unzip the file if it exists.

This is an example of a script you can use in a Script Task to check for the existence of a file.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Partial Public Class ScriptMain
        Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

        Public Sub Main()

            Dim directory As DirectoryInfo = New DirectoryInfo("c:\")
            Dim file As FileInfo() = directory.GetFiles("*.zip")

            If file.Length > 0 Then
                Dts.Variables("User::FileExists").Value = True
            Else
                Dts.Variables("User::FileExists").Value = False
            End If

            Dts.TaskResult = ScriptResults.Success
         End Sub
    End Class
End Class

how do I stop a ssis package if there are no files?

1 Answers