Prevent Subscription to report from running on a holiday

Posted on

Question :

I have a SQL Server that I am using for building and distributing reports. I have built all the packages for importing data as needed as SSIS packages. These reports only need to be run after the data has been imported. I have built my SSIS packages to check and make sure that the day isn’t a holiday by comparing the date to a table that contains holidays. This way my data is only imported on business days. The problem is, my report subscriptions are set to run at a specific time Monday – Friday. How can I programmatically run the subscription at the end of my import, so that it only runs on the days that the import runs and not on the holidays?

Answer :

have built my SSIS packages to check and make sure that the day isn’t a holiday by comparing the date to a table that contains holidays.

You can have your 1st job step as

IF EXISTS (SELECT 1 FROM HolidayTable WHERE Date = GETDATE())
  begin
   RAISERROR ('There is nothing to run as today is holiday !', 16, 1)
  end
 else
   print 'The job can run .. as its a working day !'

The second step is to run the SSIS package only if the above condition is FALSE. If the above step is true meaning if it is a holiday as per your HOLIDAY Table, then it should silently fail.

Leave a Reply

Your email address will not be published. Required fields are marked *