Выполнение пакета SQL Server Integration Services с помощью действия хранимой процедуры

Статья
05/15/2024

ОБЛАСТЬ ПРИМЕНЕНИЯ: Фабрика данных Azure Azure Synapse Analytics

Совет

Попробуйте использовать фабрику данных в Microsoft Fabric, решение для аналитики с одним интерфейсом для предприятий. Microsoft Fabric охватывает все, от перемещения данных до обработки и анализа данных в режиме реального времени, бизнес-аналитики и отчетности. Узнайте, как бесплатно запустить новую пробную версию !

В этой статье описывается, как запустить пакет SQL Server Integration Services (SSIS) в конвейере Фабрики данных Azure или Azure Synapse с помощью действия хранимой процедуры.

Необходимые компоненты

База данных SQL Azure

В этих пошаговых инструкциях используется База данных SQL Azure, в которой размещен каталог SSIS. Вы также можете использовать Управляемый экземпляр SQL Azure.

Фабрика данных

Для реализации этого пошагового действия потребуется экземпляр Фабрики данных Azure. Если у вас еще нет подготовленного экземпляра, выполните действия, описанные в кратком руководстве по созданию фабрики данных с помощью портала Azure и Фабрики данных Azure Studio.

Azure-SSIS Integration Runtime

Создайте среду выполнения интеграции Azure SSIS, если у вас ее нет. Для этого выполните пошаговую инструкцию в статье Развертывание пакетов служб интеграции (SSIS) в Azure.

Создание конвейера с действием хранимой процедуры

На этом этапе вы создадите конвейер, используя пользовательский интерфейс фабрики данных. Если вы еще не перешли в Фабрику данных Azure Studio, откройте фабрику данных на портале Azure и нажмите кнопку Открыть Фабрику данных Azure Studio, чтобы открыть ее.

Снимок экрана: домашняя страница Фабрики данных Azure.

Вы добавите действие хранимой процедуры в конвейер и настроите его для запуска пакета SSIS с помощью хранимой процедуры sp_executesql.

На главной странице нажмите Оркестрация:
На панели Действия разверните элемент База данных SQL и перетащите действие Хранимая процедура в область конструктора конвейера.
Выберите действие хранимой процедуры, которое вы только что добавили в область конструктора, а затем перейдите на вкладку Параметры и нажмите кнопку + Новый рядом с связанной службой. Вы создадите подключение к Базе данных SQL Azure, в которой размещается каталог SSIS (база данных SSISDB).
В окне New Linked Service (Новая связанная служба) выполните следующие действия:
1. В поле Тип выберите База данных SQL Azure.
2. Выберите среду выполнения интеграции Azure по умолчанию AutoResolveIntegrationRuntime для подключения к базе данных SQL Azure, в которой размещена база данных SSISDB.
3. В поле Имя сервера выберите базу данных SQL Azure, в которой размещена база данных SSISDB.
4. В поле Имя базы данных выберите SSISDB.
5. В поле Имя пользователя введите имя пользователя, у которого есть доступ к базе данных.
6. В поле Пароль введите пароль для этого пользователя.
7. Проверьте подключение к базе данных, нажав кнопку Проверить соединение.
8. Сохраните связанную службу, нажав кнопку Сохранить.

Вернитесь в окно свойств на вкладке Параметры, выполните следующие действия:

Выберите Изменить.
В поле Имя хранимой процедуры введите sp_executesql.
Нажмите кнопку + Создать в разделе Параметры хранимой процедуры.
В поле для имени параметра введите stmt.
В поле для типа параметра введите String.

В поле для значения параметра введите следующий SQL-запрос.

В SQL-запросе укажите правильные значения для параметров folder_name, project_name и package_name.

DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'<FOLDER name in SSIS Catalog>', @project_name=N'<PROJECT name in SSIS Catalog>', @package_name=N'<PACKAGE name>.dtsx', @use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 BEGIN SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) END

Связанная служба

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

Запуск и мониторинг конвейера

В этом разделе вы активируете выполнение конвейера, а затем будете отслеживать его.

Чтобы активировать конвейер, щелкните Триггер на панели инструментов, а затем Trigger Now (Активировать сейчас).
На странице Запуск конвейера нажмите кнопку Готово.
Перейдите на вкладку Мониторинг слева. На ней отображается выполнение конвейера и его состояние вместе с другой информацией (например, время начала выполнения). Чтобы обновить это представление, щелкните Refresh (Обновить).
Щелкните ссылку View Activity Runs (Просмотр выполнений действий) в столбце Actions (Действия). Вы видите только одно выполнение действия, так как конвейер содержит только действие (действие хранимой процедуры).
Вы можете направить следующий запрос к базе данных SSISDB в Базе данных SQL, чтобы убедиться, что пакет выполнен.
```
select * from catalog.executions
```

Примечание.

Вы также можете создать запланированный триггер для запуска конвейера по расписанию (ежечасно, ежедневно и т. д.). Пример см. в разделе Запуск конвейера по расписанию.

Azure PowerShell

Примечание.

Мы рекомендуем использовать модуль Azure Az PowerShell для взаимодействия с Azure. Сведения о начале работы см. в статье "Установка Azure PowerShell". Дополнительные сведения см. в статье Перенос Azure PowerShell с AzureRM на Az.

В этом разделе с помощью Azure PowerShell вы создадите конвейер фабрики данных с действием хранимой процедуры, которое вызывает пакет SSIS.

Чтобы установить модули Azure PowerShell, выполните инструкции из статьи Установка и настройка Azure PowerShell.

Создание фабрики данных

Вы можете использовать ту же фабрику данных, в которой есть среда выполнения интеграции Azure SSIS, или создать отдельную. В следующей процедуре представлены шаги для создания фабрики данных. Вы создадите конвейер с действием хранимой процедуры в фабрике данных. Действие хранимой процедуры выполняет хранимую процедуру в базе данных SSISDB для запуска вашего пакета SSIS.

Определите переменную для имени группы ресурсов, которую в дальнейшем можно будет использовать в командах PowerShell. Скопируйте текст следующей команды в PowerShell, укажите имя группы ресурсов Azure в двойных кавычках, а затем выполните команду. Например: "adfrg".
```
$resourceGroupName = "ADFTutorialResourceGroup";
```
Если группа ресурсов уже существует, вы можете не перезаписывать ее. Назначьте переменной $ResourceGroupName другое значение и еще раз выполните команду.
Чтобы создать группу ресурсов Azure, выполните следующую команду:
```
$ResGrp = New-AzResourceGroup $resourceGroupName -location 'eastus'
```
Если группа ресурсов уже существует, вы можете не перезаписывать ее. Назначьте переменной $ResourceGroupName другое значение и еще раз выполните команду.
Определите переменную для имени фабрики данных.

Внимание

Измените имя фабрики данных, чтобы оно было глобально уникальным.
```
$DataFactoryName = "ADFTutorialFactory";
```
Чтобы создать фабрику данных, выполните следующий командлет Set-AzDataFactoryV2, используя свойства Location и ResourceGroupName из переменной $ResGrp.
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

Обратите внимание на следующие аспекты:

Имя фабрики данных Azure должно быть глобально уникальным. Если появляется следующая ошибка, измените имя и повторите попытку.
```
The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.
```
Чтобы создать экземпляры фабрики данных, нужно назначить учетной записи пользователя, используемой для входа в Azure, роль участника, владельца либо администратора подписки Azure.
Чтобы получить список регионов Azure, в которых в настоящее время доступна Фабрика данных, выберите интересующие вас регионы на следующей странице, а затем разверните раздел Аналитика, чтобы найти пункт Фабрика данных: Доступность продуктов по регионам. Хранилища данных (служба хранилища Azure, база данных SQL Azure и т. д.) и вычисления (HDInsight и т. д.), используемые фабрикой данных, могут располагаться в других регионах.

Создание связанной службы Базы данных SQL Azure

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

В папке C:\ADF\RunSSISPackage создайте файл JSON с именем AzureSqlDatabaseLinkedService.json и следующим содержимым:

Внимание

Перед сохранением файла замените <servername>, <username> и <password> значениями своей базы данных SQL Azure.

{
    "name": "AzureSqlDatabaseLinkedService",
    "properties": {
        "type": "AzureSqlDatabase",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=SSISDB;User ID=<username>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
        }
    }
}

В Azure PowerShell перейдите в папку C:\ADF\RunSSISPackage.

Выполните командлет Set-AzDataFactoryV2LinkedService, чтобы создать связанную службу: AzureSqlDatabaseLinkedService.

Set-AzDataFactoryV2LinkedService -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -Name "AzureSqlDatabaseLinkedService" -File ".\AzureSqlDatabaseLinkedService.json"

Создание конвейера с действием хранимой процедуры

На этом этапе создается конвейер с действием хранимой процедуры. Это действие вызывает хранимую процедуру sp_executesql для запуска пакета SSIS.

Создайте файл JSON с именем RunSSISPackagePipeline.json в папке C:\ADF\RunSSISPackage со следующим содержимым:

Внимание

Прежде чем сохранять файл, замените заполнители <folder name>, <project name>, <package name> именами папки, проекта и пакета в каталоге SSIS соответственно.

{
    "name": "RunSSISPackagePipeline",
    "properties": {
        "activities": [
            {
                "name": "My SProc Activity",
                "description":"Runs an SSIS package",
                "type": "SqlServerStoredProcedure",
                "linkedServiceName": {
                    "referenceName": "AzureSqlDatabaseLinkedService",
                    "type": "LinkedServiceReference"
                },
                "typeProperties": {
                    "storedProcedureName": "sp_executesql",
                    "storedProcedureParameters": {
                        "stmt": {
                            "value": "DECLARE @return_value INT, @exe_id BIGINT, @err_msg NVARCHAR(150)    EXEC @return_value=[SSISDB].[catalog].[create_execution] @folder_name=N'<FOLDER NAME>', @project_name=N'<PROJECT NAME>', @package_name=N'<PACKAGE NAME>', @use32bitruntime=0, @runinscaleout=1, @useanyworker=1, @execution_id=@exe_id OUTPUT    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @exe_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1    EXEC [SSISDB].[catalog].[start_execution] @execution_id=@exe_id, @retry_count=0    IF(SELECT [status] FROM [SSISDB].[catalog].[executions] WHERE execution_id=@exe_id)<>7 BEGIN SET @err_msg=N'Your package execution did not succeed for execution ID: ' + CAST(@exe_id AS NVARCHAR(20)) RAISERROR(@err_msg,15,1) END"
                        }
                    }
                }
            }
        ]
    }
}

Чтобы создать конвейер RunSSISPackagePipeline, выполните командлет Set-AzDataFactoryV2Pipeline.

$DFPipeLine = Set-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -Name "RunSSISPackagePipeline" -DefinitionFile ".\RunSSISPackagePipeline.json"

Пример выходных данных:

PipelineName      : Adfv2QuickStartPipeline
ResourceGroupName : <resourceGroupName>
DataFactoryName   : <dataFactoryName>
Activities        : {CopyFromBlobToBlob}
Parameters        : {[inputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification], [outputPath, Microsoft.Azure.Management.DataFactory.Models.ParameterSpecification]}

Создание конвейера

Используйте для запуска конвейера командлет Invoke-AzDataFactoryV2Pipeline. Командлет позволяет получить идентификатор выполнения конвейера для дальнейшего мониторинга.

$RunId = Invoke-AzDataFactoryV2Pipeline -DataFactoryName $DataFactory.DataFactoryName -ResourceGroupName $ResGrp.ResourceGroupName -PipelineName $DFPipeLine.Name

Мониторинг конвейера

Запустите приведенный ниже скрипт PowerShell, чтобы проверять состояние выполнения, пока не закончится копирование данных. Скопируйте приведенный ниже скрипт в окно PowerShell и нажмите клавишу ВВОД.

while ($True) {
    $Run = Get-AzDataFactoryV2PipelineRun -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -PipelineRunId $RunId

    if ($Run) {
        if ($run.Status -ne 'InProgress') {
            Write-Output ("Pipeline run finished. The status is: " +  $Run.Status)
            $Run
            break
        }
        Write-Output  "Pipeline is running...status: InProgress"
    }

    Start-Sleep -Seconds 10
}

Создать триггер

На предыдущем этапе вы выполняли вызов конвейера по запросу. Вы также можете создать триггер расписания для запуска конвейера по расписанию (ежечасно, ежедневно и т. д.).

Создайте файл JSON с именем MyTrigger.json в папке C:\ADF\RunSSISPackage со следующим содержимым:

{
    "properties": {
        "name": "MyTrigger",
        "type": "ScheduleTrigger",
        "typeProperties": {
            "recurrence": {
                "frequency": "Hour",
                "interval": 1,
                "startTime": "2017-12-07T00:00:00-08:00",
                "endTime": "2017-12-08T00:00:00-08:00"
            }
        },
        "pipelines": [{
                "pipelineReference": {
                    "type": "PipelineReference",
                    "referenceName": "RunSSISPackagePipeline"
                },
                "parameters": {}
            }
        ]
    }
}

В Azure PowerShell перейдите в папку C:\ADF\RunSSISPackage.

Выполните командлет создания триггера Set-AzDataFactoryV2Trigger.

Set-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" -DefinitionFile ".\MyTrigger.json"

По умолчанию триггер находится в остановленном состоянии. Запустите триггер с помощью командлета Start-AzDataFactoryV2Trigger.
```
Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" 
```

Проверьте активацию триггера с помощью командлета Get-AzDataFactoryV2Trigger.

Get-AzDataFactoryV2Trigger -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -Name "MyTrigger"

Через час выполните следующую команду. Например, если текущее время 15:25 (UTC), запустите команду в 16:00 (UTC).
```
Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"
```
Вы можете направить следующий запрос к базе данных SSISDB в Базе данных SQL, чтобы убедиться, что пакет выполнен.
```
select * from catalog.executions
```

Вы также можете отслеживать конвейер с помощью портала Azure. Пошаговые инструкции см. в разделе Мониторинг конвейера.

Поделиться через

Выполнение пакета SQL Server Integration Services с помощью действия хранимой процедуры

Необходимые компоненты

База данных SQL Azure

Фабрика данных

Azure-SSIS Integration Runtime

Создание конвейера с действием хранимой процедуры

Запуск и мониторинг конвейера

Azure PowerShell

Создание фабрики данных

Создание связанной службы Базы данных SQL Azure

Создание конвейера с действием хранимой процедуры

Создание конвейера

Мониторинг конвейера

Создать триггер

Обратная связь

Обратная связь

Дополнительные ресурсы

Поделиться через

Выполнение пакета SQL Server Integration Services с помощью действия хранимой процедуры

Необходимые компоненты

База данных SQL Azure

Фабрика данных

Azure-SSIS Integration Runtime

Создание конвейера с действием хранимой процедуры

Запуск и мониторинг конвейера

Azure PowerShell

Создание фабрики данных

Создание связанной службы Базы данных SQL Azure

Создание конвейера с действием хранимой процедуры

Создание конвейера

Мониторинг конвейера

Создать триггер

Связанный контент

Обратная связь

Обратная связь

Дополнительные ресурсы