В последние месяцы в моих статьях речь шла в основном о ядре базы данных. Недавно я объединил многие свои инструменты для работы с ядром базы данных в набор бесплатных файлов для загрузки. Их можно найти по ссылке http://www.sqldownunder.com/Resources/SDUTools.

А в этой статье речь пойдет о пакете SQL Server Integration Services (SSIS). В нем используется та же архитектура, что и в других пакетах на том же сервере, но, несмотря на сообщение об успешном завершении, на самом деле результата работы нет. Рассмотрим, почему это происходит.

В компании был размещен отдельный сервер SSIS (с использованием SQL Server 2014). На нем выполнялись все имеющиеся пакеты SSIS; эти пакеты подключаются к нескольким источникам данных:

  • SQL Server 2012;
  • SQL Server 2014;
  • SQL Server 2016;
  • неструктурированные файлы;
  • IBM AS400.

Раньше многие пакеты успешно развертывались и выполняли свои функции. Однако при наличии отдельного сервера SSIS неизбежно возникала проблема двойного перехода, если только не была выполнена тщательная настройка протокола Kerberos на всех серверах. Компании, где произошел описываемый случай, в прошлом приходилось сталкиваться с разнообразными проблемами с Kerberos.

Правильно настроить Kerberos — по-прежнему трудная задача. Наиболее частые ошибки при настройке Kerberos, которые приводят к полной неработоспособности, следующие:

  • неверно настроены имена участников — служб SPN;
  • неверно настроено ограниченное делегирование для комбинации учетной записи службы и службы.

Если вы не знакомы с методами настройки Kerberos, рекомендую начать со статьи по адресу: https://blogs.msdn.microsoft.com/psssql/2010/06/23/my-kerberos-checklist/

Конкретно для SQL Server предусмотрен новый инструмент диагностики настроек Kerberos. Загружаемый файл находится по адресу: https://www.microsoft.com/en-us/download/details.aspx? id=39046.

Статью с описанием инструмента можно найти по адресу: https://blogs.msdn.microsoft.com/farukcelik/2013/05/21/new-tool-microsoft-kerberos-configuration-manager-for-sql-server-is-ready-to-resolve-your-kerberosconnectivity-issues/.

Как правило, это удобный инструмент, позволяющий подтвердить работоспособность настроек, но он не так хорош, если вы изначально столкнулись с ошибками. В таком случае имеет смысл проверить, в чем дело, с помощью инструмента, доступного по указанной выше ссылке.

Большее беспокойство вызывает ситуация с периодически возникающими ошибками Kerberos. Их основные причины следующие:

  • синхронизация времени между серверами (не допускается рассогласования времени между серверами);
  • фермы серверов и реплики (один сервер работает, а другой, предназначенный для отработки отказа, не работает, поэтому вы случайным образом попадаете на один из них).

Симптомы

Клиент сообщил, что они изменили существующий пакет SSIS и развернули его на сервере как отдельный пакет. После этого задание агента SQL Server, которое выполняло измененный пакет (назовем его пакетом WontWork), завершилось без ошибок. Однако работа не была выполнена. Предыдущая версия пакета (назовем его пакетом StillWorks) по-прежнему функционировала безупречно. Клиент не мог понять, каким образом задание, которое, казалось, выполнялось верно, перестало работать.

Структура пакета

Я выяснил, что делал этот пакет. Он находил несколько файлов из папки «Входящие», проверял их доступность и перемещал в промежуточную папку для обработки. Для каждого файла в промежуточной папке определяется тип файла и совершаются соответствующие ему действия. В большинстве случаев данные загружались в базу данных SQL Server, которая использовалась для промежуточного хранения данных. Каждый обрабатываемый файл копировался в папку Copy (перезаписывая любые преды­дущие версии того же файла). Затем, если все файлы были обработаны верно, они перемещались в папку Processed. В завершение вызывалась хранимая процедура на сервере базы данных для обработки промежуточных данных.

Проверка 1: безопасность

Первое, на что я обращаю внимание, когда имею дело с пакетами такого типа, — цикл For Each, который обходит файлы. Мне случалось наблюдать, как эти циклы просто возвращают пустой список файлов, когда учетная запись службы, от имени которой выполняется задание, не имеет разрешений в целевой папке.

Такие пакеты следует настраивать при подготовке задания агента SQL Server для выполнения с соответствующей учетной записью посредника. Типичная ошибка — пакеты, которые выполняются с учетной записью службы агента SQL Server. Этого следует избегать. В противном случае учетная запись службы агента должна иметь слишком много разрешений. Вместо этого нужно назначить соответствующие учетные данные SQL Server отдельной учетной записи службы, которая имеет разрешение для выполнения пакета. Если вы прежде не настраивали учетные данные, рекомендую познакомиться с ними поближе. Они показаны в обозревателе объектов. Этот вариант доступен со времени выпуска SQL Server 2005 и представляет собой учетные данные уровня сервера (экран 1).

 

Настройка учетных данных
Экран 1. Настройка учетных данных

Обратите внимание, что в SQL Server 2016 и базе данных Azure SQL некоторые параметры безопасности доступны на уровне базы данных. Об учетных данных для базы данных можно прочитать в статье по адресу: https://msdn.microsoft.com/en-us/library/mt270260.aspx.

Учетные данные просто связывают внешнее удостоверение (в данном случае имя пользователя Windows) с именем объекта SQL Server. Чтобы воспользоваться учетными данными для выполнения пакета SSIS, необходимо создать посредника. В агенте SQL Server посредник, в сущности, представляет собой учетную запись с разрешением настроить данную подсистему (SSIS, PowerShell и т. д.) для использования учетных данных. Пример настройки посредника показан на экране 2.

 

Создание учетной записи посредника
Экран 2. Создание учетной записи посредника

Посредник имеет имя, связанные с ним учетные данные и список подсистем, которые могут использовать эти учетные данные (экран 3).

 

Параметры посредника
Экран 3. Параметры посредника

После завершения настройки шаг задания такого типа может быть настроен для выполнения с этим посредником (экран 4).

 

Использование посредника для запуска задания
Экран 4. Использование посредника для запуска задания

Я проверил настройки в этой области и не нашел ошибок. Чтобы удостовериться в наличии необходимого доступа, я выполнил регистрацию с теми же учетными данными и смог работать с файлами в этой папке. Кроме того, я проверил журнал заданий агента и убедился, что шаг задания выполнен с нужной учетной записью. Таким образом, ошибка лежала глубже.

Проверка 2: ведение журнала и отчеты SSIS

Следующим шагом проверки был стандартный отчет SSMS, который можно подготовить для пакета. В нем показан каждый запуск на выполнение: можно посмотреть детализацию каждого процесса и увидеть, что произошло. Существует несколько уровней журнала. В большинстве случаев простого журнала по умолчанию вполне достаточно, чтобы разобраться в ситуации, но иногда требуется больше подробностей.

При настройке или изменении шага задания SSIS агента SQL Server можно задать уровень ведения журнала. Настройку также можно выполнить в средстве SQL Server Data Tools при создании пакета.

Я установил уровень ведения журнала Verbose («Подробно»), повторно выполнил пакет и внимательно просмотрел результаты.

На данном этапе я выяснил следующее:

  • похоже, пакет выполнялся успешно;
  • соединения открыты;
  • доступ к папкам прдоставляется;
  • первый цикл For Each обходил все файлы;
  • первый шаг каждого цикла состоял в поиске расширения файла;
  • второй шаг каждого цикла состоял в проверке возможности доступа к файлу; это действие, похоже, тоже было выполнено.

Однако третьим шагом цикла было перемещение файла, и оно так и не произошло. Похоже, программный код, используемый для определения доступности файла, всегда сообщал, что файл недоступен. Это означало, что остальные компоненты пакета игнорировали файлы.

Таким образом, мне хотя бы удалось определить источник проблемы. По крайней мере, я так думал. Я решил, что в программном коде имеется ошибка.

Проверка 3: сценарий задания IsFileAccessible

Пакет использовал сценарий задания, чтобы определить, доступен ли файл. Это делалось для того, чтобы избежать попыток переместить файл, все еще записываемый исходной системой. Программный код показан в листинге.

Трудно увидеть в этом коде какие-либо ошибки, способные вызвать подобную проблему. Была предпринята попытка открыть файл (на основе переменной MoveFromLocation) в режиме записи без общего доступа. Если это можно сделать, то файл очевидно доступен. Если файл открывался, его немедленно закрывали вновь. В любом случае устанавливалась переменная IsFileAccessible.

Я выяснил, что управление очередностью проверяло значение переменной, чтобы определить, следует ли перемещать файл.

Проверка 4: задача становится интересной

Я добавил программный код для вывода значений MoveFromLocation и IsFileAccessible в таблицу и повторного запуска пакета. Независимо от имени файла, возвращаемое значение переменной IsFileAccessible оказывалось false. Это было удивительно. Я не понимал, каким образом мог произойти такой отказ в сценарии задания.

Наконец, я изменил сценарий задания так, чтобы каждый раз просто присваивать значение true. После выполнения значение переменной неизменно было false.

Я убедился, что переменная была настроена для чтения и записи в сценарии задания. Мне приходилось встречать сценарии, в которых переменные по ошибке назначались только для чтения. Когда такое случается, переменную можно изменить в сценарии, но значение никогда не копируется в пакет. Но в данном случае это было не так. Наконец, я задумался, выполняется ли сценарий задания вообще. Я ввел ссылку на System.Threading и добавил ожидание через Thread.Sleep (10000)

Я хотел, чтобы задание предусматривало задержку в течение 10 секунд. Однако после запуска пакета задание было выполнено успешно, а задержки не последовало. И я понял, что сценарий задания в действительности не выполнялся.

Разгадка

Учитывая необычную ситуацию, когда пакет выполняется успешно, а сценарий задания не выполняется, я задумался о версиях. NET на сервере. Затем я осознал, что другие пакеты на том же сервере работают и используют тот же сценарий задания. Очевидно, существовала какая-то несовместимость.

Наконец я понял, что использовалась более новая версия SQL Server Data Tools (SSDT). Эти версии SSDT могут охватывать несколько выходных версий сервера.

Я взглянул на свойства пакета и обнаружил причину проблемы (экран 5).

 

Свойства пакета
Экран 5. Свойства пакета

Когда пакет был открыт, он был настроен для SQL Server 2016, а целевой сервер SSIS был версии SQL Server 2014. Когда вы создаете новый пустой пакет (в данном случае я использовал имя по умолчанию), в обозревателе решений Solution Explorer пакет выглядит таким образом, как показано на экране 6.

 

Создание пустого пакета
Экран 6. Создание пустого пакета

Но если изменить свойство версии целевого сервера, то получаем вид как на экране 7.

 

Пакет с измененной версией целевого сервера
Экран 7. Пакет с измененной версией целевого сервера

Когда это изменение было внесено и пакет развернут заново, все работало как полагается.

Должен признаться, что описанная проблема меня удивила. Я ожидал, что попытка развернуть пакет SQL Server 2016 на сервере SQL Server 2014 приведет к явному отказу. Однако для данного пакета все работало как ожидалось, кроме сценария заданий, которые сообщали, что выполняются, но на самом деле ничего не делали. Надеюсь, этот пример кому-то из читателей будет полезен.

Листинг. Сценарий проверки доступа к файлу IsFileAccessible
public void Main()
{
    Dts.Variables["IsFileAccessible"].Value
        = IsFileAccessible(Dts.Variables["MoveFromLocation"].Value.ToString());
    Dts.TaskResult = (int)ScriptResults.Success;
}

bool IsFileAccessible(string FilePath)
{
    bool isAccessible = false;

    try
    {
        FileStream fs = File.Open(FilePath, FileMode.Open, FileAccess.Write, FileShare.None);
        fs.Close();
        isAccessible = true;
    }
    catch
    {
        isAccessible = false;
    }

    return isAccessible;
}