Автоматический запуск пакетов SSIS по нестандартному расписанию.

Что мы имеем в наличии: хранилище данных с большим количеством таблиц (>100), которое обновляется путем импорта данных из внешнего источника данных (ERP). Для импорта таблиц создано несколько пакетов. Для небольших таблиц существуют только пакеты полной загрузки (т.е. удаление данных в таблице хранилища и загрузка всех данных) для больших таблиц (>50 млн записей) существуют как пакеты полной загрузки так и пакеты инкрементальной загрузки (удаляются данные за определенный период и заново загружаются из источника). Для удобства создано 3 пакета, в которых объединены пакеты по таким признакам:
1 пакет - все пакеты, которые осуществляют только полную загрузку таблиц
2 пакет - все пакеты, которые осуществляют инкрементальную загрузку больших таблиц
3 пакет - все пакеты, которые осуществляют полную загрузку больших таблиц
Каждому пакету полной загрузки из группы 2 соответствует пакет инкрементальной загрузки из группы 3.
Задача: настроить ежедневную автоматическую загрузку хранилища. В определенные дни нужно осуществлять полную загрузку, а в остальные инкрементальную. Должна быть возможность запускать полную загрузку в любой день при необходимости.
Решения: классическое решение предполагает создание нескольких хитроумных джобов в SQL Server Agent. Проблема только в том, что их может быть много (или самих джобов или расписаний) и они должны будут реализовывать сложную логику.
Я нашел более гибкое решение, по крайней мере, для наших условий.

Допустим, стоит задача запускать полную загрузку в первую субботу месяца и второе воскресенье. Для этого я шифрую нужные дни ввиде числа, например: первая суббота месяца - 61, второе воскресенье - 72, где первая цифра - порядковый номер дня недели, а вторая - порядковый номер недели. В базе данных создается табличка следующей структуры для хранения каких-то служебных переменных:

CREATE TABLE params(param_name varchar(100) NULL,param_value)

В таблицу добавляем две записи, описанные в примере:

INSERT INTO params('full run day','61')
INSERT INTO params('full run day','72')

Далее в SSIS создаем общий пакет, в котором будем запускать все наши объединенные пакеты:

На первом шаге всегда выполняется загрузка пакета 1. На втором шаге мы определяем нужно ли сегодня запускать полную загрузку. Для этого используем SQL Execute Task, в котором определяем следующий запрос:

SELECT  CONVERT( int,
  CASE

     WHEN
      CONVERT(varchar(1), DATEPART(dw, GETDATE()))+

      CONVERT(varchar(1),

      CASE
        WHEN (DATEPART(DAY, GETDATE()) % 7) = 0

        THEN DATEPART(DAY, GETDATE())/7

        ELSE DATEPART(DAY, GETDATE())/7 + 1

      END)

    IN

    (
      SELECT param_value 

      FROM params

      WHERE [parameter_name] = 'full run day'

     )
    THEN 1

    ELSE 0

  END) AS isFULL

Запрос возвращает 1 или 0. Если 1 - запускаем полную загрузку, если 0 - инкрементальную. Полученое в запросе значение сохраняем в заранее определенную переменную:

Затем используем переменную в условии перехода к следующему компоненту Control Flow Task:

Это условие перехода к инкрементальному пакету, соответственно для перехода к полному, в условии будет : @[User::IsFULL]==1

Теперь можно создать в SQL Server Agent всего один джоб с одним расписанием и в нем запускать наш пакет.

Ну и на закуску: как сделать так, чтоб полная загрузка запускалась еще и в какой-нибудь день по нашему желанию? Для этого в таблицу параметров добавляем еще одну запись с новым параметром, в котором будет храниться дата запуска:

INSERT INTO params('full run date','20121221')


Дополняем наш запрос:

SELECT   CONVERT( int,
  CASE
     WHEN      CONVERT(varchar(1), DATEPART(dw, GETDATE()))+
      CONVERT(varchar(1),
      CASE
         WHEN (DATEPART(DAY, GETDATE()) % 7) = 0 THEN DATEPART(DAY, GETDATE())/7
        ELSE DATEPART(DAY, GETDATE())/7 + 1
      END)
      IN
      (
        SELECT param_value 
        FROM params
        WHERE [parameter_name] = 'full run day'
       )

    THEN 1  

   -- specific date for FULL
    WHEN
      CONVERT(datetime, CONVERT(date, GETDATE())) =
     (SELECT CASE
         WHEN ISDATE(value) = 1 THEN CONVERT(datetime, value)
         ELSE CONVERT(datetime, NULL)
         END AS RunDate
      FROM params
      WHERE [parameter_name] = 'full run date')
    THEN 1
    ELSE 0
  END) AS isFULL