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?
1 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.
-
Perhaps I didn't word this correctly. I have already ensured that the SSIS package only runs when I want it to. They don't run on Holidays. Now I need the Subscription to the report to not run on Holidays either. Dec 4, 2014 at 21:26
-
2Actually I think I see what you are saying. I can add a step to the subscription job that SSRS put in. That can be step on to check and fail if needed. That is genius, thank you, I'll try it. Dec 4, 2014 at 21:31
-
Protip: If your question is actually about SSRS, put that in the title. You didn't mention what your reporting system was. Jan 26, 2015 at 15:34