Executar um pacote SSIS com a atividade de procedimento armazenado

APLICA-SE A: Azure Data Factory Azure Synapse Analytics

Este artigo descreve como executar um pacote SSIS em um pipeline do Azure Data Factory usando uma atividade de procedimento armazenado.

Pré-requisitos

Banco de Dados SQL do Azure

O passo a passo neste artigo usa um Banco de Dados SQL do Azure que hospedar o catálogo SSIS. Também é possível usar a Instância Gerenciada de SQL do Azure.

Data Factory

Você precisará de uma instância do Azure Data Factory para implementar esse passo a passo. Se você ainda não tiver um provisionado, siga as etapas no Início Rápido: Criar um data factory usando o portal do Azure e o Azure Data Factory Studio.

runtime de integração do Azure-SSIS

Por fim, você também precisará de um Integration Runtime do Azure-SSIS, caso você não tenha um, seguindo as instruções passo a passo no Tutorial: Implantar pacotes do SSIS.

Criar um pipeline com atividade de procedimento armazenado

Nesta etapa, você usa a interface do usuário do Data Factory para criar um pipeline. Se você ainda não navegou até o Azure Data Factory Studio, abra o data factory no portal do Azure e clique no botão Abrir Azure Data Factory Studio para abri-lo.

Em seguida, você adicionará uma atividade de procedimento armazenado a um novo pipeline e a configurará para executar o pacote SSIS usando o procedimento armazenado sp_executesql.

1. Na home page, clique em Orquestrar:

   

2. Na caixa de ferramentas Atividades, pesquise Procedimento armazenado e arraste e solte a atividade Procedimento armazenado na superfície do designer de pipeline.

   

3. Selecione a atividade Procedimento armazenado que você acabou de adicionar à superfície do designer e, em seguida, a guia Configurações e clique em + Novo ao lado do Serviço vinculado. Você cria uma conexão com o banco de dados no Banco de dados SQL do Azure que hospeda o Catálogo SSIS (banco de dados SSISDB).

   

4. Na janela Novo Serviço Vinculado, execute estas etapas:

   1. Selecione Banco de Dados SQL do Azure para Tipo.

   2. Selecione o AutoResolveIntegrationRuntime Padrão para se conectar ao Banco de Dados SQL do Azure que hospeda o banco de dados SSISDB.

   3. Selecione o Banco de Dados SQL do Azure que hospeda o banco de dados SSISDB para o campo Nome do servidor.

   4. Selecione SSISDB para Nome do Banco de Dados.

   5. Para Nome de usuário, insira o nome do usuário que tem acesso ao banco de dados.

   6. Para Senha, insira a senha do usuário.

   7. Teste a conexão com o banco de dados, clicando no botão Testar conexão.

   8. Salve o serviço vinculado. clicando no botão Salvar.

      

5. De volta à janela de propriedades na guia Configurações, conclua as seguintes etapas:

   1. Selecione Editar.

   2. No campo Nome do procedimento armazenado, Insira sp_executesql.

   3. Clique em + Novo na seção Parâmetros do procedimento armazenado.

   4. Para o nome do parâmetro, insira stmt.

   5. Para o tipo do parâmetro, insira Cadeia de caracteres.

   6. Para o valor do parâmetro, insira a consulta SQL a seguir:

      Na consulta SQL, especifique os valores certos para os parâmetros nom_da_pasta, nome_do_projeto e nome_do_pacote.

      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
      

      

6. Para validar a configuração do pipeline, clique em Validar na barra de ferramentas. Para fechar o Relatório de validação do pipeline clique em >> .

   

7. Publique o pipeline para Data Factory, clicando no botão Publicar Tudo.

   

Executar e monitorar o pipeline

Nesta seção, você dispara uma execução do pipeline e, em seguida, faz o monitoramento.

1. Para disparar uma execução de pipeline, clique em Disparar na barra de ferramentas e clique em Disparar agora.

   

2. Na janela Execução de Pipeline, selecione Concluir.

3. Alterne para a guia Monitorar à esquerda. Você verá o pipeline de execução e seu status junto com outras informações (como a Hora de início da execução). Para atualizar o modo de exibição, clique em Atualizar.

   

4. Clique no link Exibir Execuções da atividade na coluna Ações. Você verá apenas uma execução da atividade, pois o pipeline possui apenas uma atividade (atividade de procedimento armazenado).

   

5. É possível executar a seguinte consulta no banco de dados SSISDB no Banco de Dados SQL para verificar se o pacote foi executado.

   select * from catalog.executions
   

   

Azure PowerShell

Nesta seção, você usa o Azure PowerShell para criar um pipeline do Data Factory com uma atividade de procedimento armazenado que invoca um pacote do SSIS.

Instale os módulos mais recentes do Azure PowerShell seguindo as instruções em Como instalar e configurar o Azure PowerShell.

Criar uma data factory

Você pode usar a mesma fábrica de dados que contém o IR do Azure-SSIS ou criar uma fábrica de dados separada. O procedimento a seguir fornece as etapas para criar uma fábrica de dados. Você cria um pipeline com uma atividade de procedimento armazenado nesta data factory. A atividade de procedimento armazenado executa um procedimento armazenado no banco de dados SSISDB para executar o seu pacote do SSIS.

1. Defina uma variável para o nome do grupo de recursos que você usa nos comandos do PowerShell posteriormente. Copie o seguinte texto de comando para o PowerShell, especifique um nome para o grupo de recursos do Azure entre aspas duplas e, em seguida, execute o comando. Por exemplo: "adfrg".

   $resourceGroupName = "ADFTutorialResourceGroup";
   

   Se o grupo de recursos já existir, não convém substituí-lo. Atribua um valor diferente para a variável $ResourceGroupName e execute o comando novamente

2. Para criar o grupo de recursos do Azure, execute o seguinte comando:

   $ResGrp = New-AzResourceGroup $resourceGroupName -location 'eastus'
   

   Se o grupo de recursos já existir, não convém substituí-lo. Atribua um valor diferente para a variável $ResourceGroupName e execute o comando novamente.

3. Defina uma variável para o nome do data factory.

   Importante

   Atualize o Nome do data factory para ser globalmente exclusivo.

   $DataFactoryName = "ADFTutorialFactory";
   

4. Para criar o data factory, execute o cmdlet Set-AzDataFactoryV2 a seguir usando a propriedade Location e ResourceGroupName da variável $ResGrp:

   $DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
   

Observe os seguintes pontos:

• O nome da data factory do Azure deve ser globalmente exclusivo. Se você receber o erro a seguir, altere o nome e tente novamente.

  The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.
  

• Para criar instâncias de Data Factory, a conta de usuário usada para fazer logon no Azure deve ser um membro das funções colaborador ou proprietário, ou um administrador da assinatura do Azure.

• Para obter uma lista de regiões do Azure no qual o Data Factory está disponível no momento, selecione as regiões que relevantes para você na página a seguir e, em seguida, expanda Análise para localizar Data Factory: Produtos disponíveis por região. Os armazenamentos de dados (Armazenamento do Azure, Banco de Dados SQL do Azure, etc.) e serviços de computação (HDInsight, etc.) usados pelo data factory podem estar em outras regiões.

Criar um serviço vinculado do Banco de Dados SQL do Azure

Crie um serviço vinculado para vincular o banco de dados que hospeda o catálogo SSIS ao data factory. O Data Factory usa informações nesse serviço vinculado para se conectar ao banco de dados SSISDB, e executa um procedimento armazenado para executar um pacote do SSIS.

1. Crie um arquivo JSON denominado AzureSqlDatabaseLinkedService.json na pasta C:\ADF\RunSSISPackage com o seguinte conteúdo:

   Importante

   Substitua o <servername>, o <username>, e a <senha> pelos valores do seu banco de dados SQL do Azure antes de salvar o arquivo.

   {
       "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"
           }
       }
   }
   

2. No Azure PowerShell, mude para a pasta C:\ADF\RunSSISPackage.

3. Execute o cmdlet Set-AzDataFactoryV2LinkedService para criar o serviço vinculado: AzureSqlDatabaseLinkedService.

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

Criar um pipeline com atividade de procedimento armazenado

Nesta etapa, você cria um pipeline com uma atividade de procedimento armazenado. A atividade chama o procedimento armazenado sp_executesql para executar o seu pacote do SSIS.

1. Crie um arquivo JSON denominado RunSSISPackagePipeline.json na pasta C:\ADF\RunSSISPackage com o seguinte conteúdo:

   Importante

   Substitua o <NOME DA PASTA>, o <NOME DO PROJETO>, e o <NOME DO PACOTE> com os nomes de pasta, projeto e pacote no catálogo do SSIS antes de salvar o arquivo.

   {
       "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"
                           }
                       }
                   }
               }
           ]
       }
   }
   

2. Para criar o pipeline: RunSSISPackagePipeline, execute o cmdlet Set-AzDataFactoryV2Pipeline.

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

   Veja o exemplo de saída:

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

Criar uma execução de pipeline

Use o cmdlet Invoke-AzDataFactoryV2Pipeline Invoke para executar o pipeline. O cmdlet retorna a ID da execução de pipeline para monitoramento futuro.

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

Monitorar a execução de pipeline

Execute o script do PowerShell a seguir para verificar continuamente o status da execução de pipeline até que ela termine de copiar os dados. Copie/cole o script a seguir na janela do PowerShell e pressione ENTER.

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
}   

Escolha um gatilho

Na etapa anterior, você chamou a pipeline sob demanda. Você também pode criar um gatilho de agendamento para a agendar a execução do pipeline (por hora, diariamente, etc.).

1. Crie um arquivo JSON denominado MyTrigger.json na pasta C:\ADF\RunSSISPackage com o seguinte conteúdo:

   {
       "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": {}
               }
           ]
       }
   }    
   

2. No Azure PowerShell, mude para a pasta C:\ADF\RunSSISPackage.

3. Execute o cmdlet Set-AzDataFactoryV2Trigger, que cria o gatilho.

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

4. Por padrão, o gatilho está no estado interrompido. Inicie o gatilho usando o cmdlet Start-AzDataFactoryV2Trigger.

   Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" 
   

5. Verifique se o gatilho foi iniciado executando o cmdlet Get-AzDataFactoryV2Trigger.

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

6. Execute o seguinte comando após a próxima hora. Por exemplo, se a hora atual for 15:25 UTC, execute o comando às 16:00 UTC.

   Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"
   

   É possível executar a seguinte consulta no banco de dados SSISDB no Banco de Dados SQL para verificar se o pacote foi executado.

   select * from catalog.executions
   

Conteúdo relacionado

Você também pode monitorar o pipeline usando o Portal do Azure. Para obter instruções passo a passo, consulte Monitorar o pipeline.