CODEWITHSUNDEEP
sql-serverssis
Kelly Peterson
Kelly Peterson

Reputation: 45

SSIS, avoid failure if source file isn't available

I have an SSIS job that is scheduled to run every 5 minutes via SQL Agent. The job imports the contents of an excel file into a SQL table. That all works great, but the files get placed there sporadically and often times when the job runs there is no file there at all. The issue is this is causing the job to fail and send a notification email that the job failed, but I only want to be notified if the job failed while processing a file, not because there was no file there in the first place. From what I have gathered I could fix this with a script task to check if the file is there before the job continues, but I haven't been able to get that to work. Can someone break down how the script task works and what sort of script I need to check if a file exists? Or if there is some better way to accomplish what I am trying to do I am open to that as well!

The errors I get when I tried the Foreach Loop approach are

Excel is failing validation

Upvotes: 1

Views: 2187

Answers (2)

Chris Albert
Chris Albert

Reputation: 2507

This can be done easily with a Foreach Loop Container in SSIS.

Put simply, the container will check the directory you point it at and perform the tasks within the container for each file found. If no files are found the contents of the container are never executed. Your job will not fail if no files are found. It will complete reporting success.

Check out this great intro blog post for more info.

In the image attached the question, the specific errors are related to the Excel Source failing validation. When SSIS opens a package for editing or running, the first thing it does is validate all of the artifacts needed for a successful run are available and conform to the expected shape/API. Since the expected file may not be present, right click on the Excel Connection Manager and in the Properties menu, find a setting for DelayValidation and change it to True. This will ensure the connection manager only validates the resource is available if the package is actually going to use it i.e. it passes into the Foreach Loop Container. You will also need to set the same DelayValidation to True on your Data Flow Task.

Upvotes: 4

user3662215
user3662215

Reputation: 526

You did not mention what scripting approach you're applying to search for your file. While using C# or VB.NET are typical scripting languages used in a Scripting control task of this nature, you can also use TSQL that will simply return a boolean value saved to a user variable (Sometimes systems limit the use C# and VB.NET). Then you apply that user variable in the control flow to determine whether to import (boolean = 1) or not (boolean = 0).

Take a look at the following link that shows in detail how to set up the TSQL script that checks for whether or not a file exist. Check for file exists or not in sql server?

Take a look at the following link that shows how to apply a conditional check based on a boolean user variable. This example also shows how to apply VB.NET in a script task to determine if the file exists (as an alternative to the before mentioned TSQL approach). http://sql-articles.com/articles/bi/file-exists-check-in-ssis/

Hope this helps.

Upvotes: 0

Related Questions