SSIS - Missing Source Files

SSIS - Missing Source Files

Post by TlJfVF » Wed, 11 Jul 2007 10:20:01

I want to skip running the SSIS data flow task when the source file is
missing. We have a scheduler that copies the source file to the staging area.
This SSIS package runs as SQL server job. So when a SSIS package fails due
to missing file the remaining steps in the SQL scheduler won't execute. I
want to handle the missing source file condition grace fully. Please advise.

Thanks in advance.

SSIS - Missing Source Files

Post by VG9kZCB » Wed, 11 Jul 2007 11:40:02

You will need to put something on the Control Flow to test for the existence
of the file you want, then go from that task to your data flow with an "On
Success" constraint.
One possible solution: Have a File System Task set up to COPY or RENAME the
file. If that succeeds, then go to your dat flow. If not, then stop or do
something else.

Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


SSIS - Missing Source Files

Post by Allan Mitc » Wed, 11 Jul 2007 17:28:28

Hello Todd,

A simpler way would be to test for the existence of the file in a script
task. You can then set a package variable to a value that you then use in
Expressions on the workflow to decide which way to go

Here might be an example of looking for a file and then setting a variable

Public Class ScriptMain

Public Sub Main()

Dim vs As Variables

Dts.VariableDispenser.LockOneForWrite("IsFileAvailable", vs)

vs("IsFileAvailable").Value = System.IO.File.Exists("C:\MyFile.txt")


Dts.TaskResult = Dts.Results.Success
End Sub

Allan Mitchell | | |