Sql-server – Prevent Subscription to report from running on a holiday

sql serverssisssrsvisual-studio-2013

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?

Best 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.

Related Solutions

Sql-server – can I convert an integer to base-2 without a function

Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for). It utilizes bitwise operators to pull out the bit masks for the days:

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case 
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case 
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case 
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case 
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case 
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case 
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case 
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable

Sql-server – Non-English characters are not imported using SSIS or Visual Studio Deploy

The data types of the columns you are inserting the text into should be set to nvarchar.

Best Answer

Related Solutions

Sql-server – can I convert an integer to base-2 without a function

Sql-server – Non-English characters are not imported using SSIS or Visual Studio Deploy

Related Question