1

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

billinkc
  • 59,250
  • 9
  • 102
  • 159
Kelly Peterson
  • 45
  • 5
  • Why not use a script task to check the existence of the file, which assigns the value to a variable, and a conditional flow? YOu could also use a Foreach Loop Container to do the same thing (what I often do). – Thom A Jan 08 '20 at 17:14
  • I think your errors are more tied to delaying validation. – KeithL Jan 08 '20 at 19:05

2 Answers2

4

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
Chris Albert
  • 2,462
  • 8
  • 27
  • 31
  • 1
    Literally this approach is what I'd suggest. Put a foreach loop container onto your control flow and then put all your existing logic into it. Configure the FELC to check for your file and if it matches, boom all your tested code runs. No file, no problem – billinkc Jan 08 '20 at 18:22
  • I must be doing something wrong in the FELC because even though the file isn't there I am still getting an error when executing. – Kelly Peterson Jan 08 '20 at 18:35
  • 1
    @KellyPeterson Set the Excel Connection Manager's DelayValidation property to True. You *might* need to also set it on the Data Flow but I can't remember for certain how Excel plays, besides poorly – billinkc Jan 08 '20 at 18:52
  • That is what the issue was, setting DelayValidation True on the connection manager and the data flow did it. Thanks! – Kelly Peterson Jan 08 '20 at 19:41
0

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.

user3662215
  • 526
  • 4
  • 12