Eseguire un pacchetto SSIS con l'attività Stored procedure

SI APPLICA A: Azure Data Factory Azure Synapse Analytics

Questo articolo descrive come eseguire un pacchetto SSIS in una pipeline di Azure Data Factory pr Synapse Pipelines usando un'attività stored procedure.

Prerequisiti

Database SQL di Azure

La procedura dettagliata descritta in questo articolo usa database SQL di Azure per ospitare il catalogo SSIS. È anche possibile usare Istanza gestita di SQL di Azure.

Data Factory

Per implementare questa procedura dettagliata, è necessaria un'istanza di Azure Data Factory. Se non è già stato effettuato il provisioning di una data factory, è possibile seguire la procedura descritta in Avvio rapido: Creare una data factory usando il portale di Azure e Azure Data Factory Studio.

Runtime di integrazione Azure-SSIS

Infine, sarà necessario anche un runtime di integrazione Azure-SSIS se non ne è disponibile uno seguendo le istruzioni dettagliate in Esercitazione: Distribuire pacchetti SSIS.

Creare una pipeline con un'attività stored procedure

In questo passaggio viene usata l'interfaccia utente di Data Factory per creare una pipeline. Se non si è già passati ad Azure Data Factory Studio, aprire la data factory nel portale di Azure e fare clic sul pulsante Apri Azure Data Factory Studio per aprirlo.

Successivamente, si aggiungerà un'attività stored procedure a una nuova pipeline e la si configurerà per eseguire il pacchetto SSIS usando la stored procedure sp_executesql.

1. Nella home page fare clic su Orchestrate :In the home page, click Orchestrate:

   

2. Nella casella degli strumenti Attività cercare Stored procedure e trascinare un'attività Stored procedure nell'area di progettazione della pipeline.

   

3. Selezionare l'attività Stored procedure appena aggiunta all'area di progettazione, quindi la scheda Impostazioni e fare clic su + Nuovo accanto al servizio collegato. Creare una connessione al database in database SQL di Azure che ospita il catalogo SSIS (database SSIDB).

   

4. Nella finestra New Linked Service (Nuovo servizio collegato) seguire questa procedura:

   1. Selezionare Database SQL di Azure per Tipo.

   2. Selezionare l'opzione AutoResolveIntegrationRuntime predefinita per connettersi al database SQL di Azure che ospita il SSISDB database.

   3. Selezionare il database SQL di Azure che ospita il database SSISDB per il campoNome server.

   4. Selezionare SSISDB per Nome database.

   5. Per Nome utente immettere il nome dell'utente che ha accesso al database.

   6. Per Password immettere la password dell'utente.

   7. Testare la connessione al database facendo clic sul pulsante Test connessione.

   8. Salvare il servizio collegato facendo clic sul pulsante Salva.

      

5. Nella finestra delle proprietà della scheda Impostazioni completare i passaggi seguenti:

   1. Seleziona Modifica

   2. Per il campo Nome stored procedure immettere sp_executesql.

   3. Fare clic su + Nuovo nella sezione Parametri stored procedure.

   4. Per il nome del parametro, immettere stmt.

   5. Per il tipo del parametro, immettere Stringa.

   6. Per il valore del parametro, immettere la query SQL seguente.

      Nella query SQL specificare i valori corretti per i parametri folder_name, project_name e 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
      

      

6. Per convalidare la configurazione della pipeline, fare clic su Convalida sulla barra degli strumenti. Per chiudere il Pipeline Validation Report (Report di convalida della pipeline) fare clic su >>.

   

7. Pubblicare la pipeline in Data Factory facendo clic sul pulsante Publish All (Pubblica tutto).

   

Eseguire e monitorare la pipeline

In questa sezione si attiva un'esecuzione della pipeline e quindi la si monitora.

1. Per attivare un'esecuzione della pipeline, fare clic su Trigger sulla barra degli strumenti e quindi su Trigger Now (Attiva adesso).

   

2. Nella finestra Pipeline Run (Esecuzione di pipeline) selezionare Fine.

3. Passare alla scheda Monitoraggio a sinistra. Verrà visualizzata l'esecuzione della pipeline e il relativo stato insieme ad altre informazioni (ad esempio l'Ora di inizio dell'esecuzione). Per aggiornare la visualizzazione, fare clic su Aggiorna.

   

4. Fare clic sul collegamento View Activity Runs (Visualizza le esecuzioni di attività) nella colonna Azioni. Verrà visualizzata una sola esecuzione attività dal momento che la pipeline ha una sola attività (attività stored procedure).

   

5. È possibile eseguire la query seguente sul database SSISDB in database SQL per verificare che il pacchetto sia stato eseguito.

   select * from catalog.executions
   

   

Azure PowerShell

In questa sezione verrà usato Azure PowerShell per creare una pipeline di Data Factory con un'attività stored procedure che richiama un pacchetto SSIS.

Installare i moduli di Azure PowerShell più recenti seguendo le istruzioni descritte in Come installare e configurare Azure PowerShell.

Creare una data factory

È possibile usare la stessa data factory che ha il runtime di integrazione SSIS di Azure oppure crearne una distinta. La procedura seguente descrive le operazioni necessarie per creare una data factory. Viene creata una pipeline con un'attività stored procedure in questa data factory. L'attività stored procedure esegue una stored procedure nel database SSISDB per l'esecuzione del pacchetto SSIS.

1. Definire una variabile per il nome del gruppo di risorse usato in seguito nei comandi di PowerShell. Copiare il testo del comando seguente in PowerShell, specificare un nome per il gruppo di risorse di Azure tra virgolette doppie e quindi eseguire il comando. Ad esempio: "adfrg".

   $resourceGroupName = "ADFTutorialResourceGroup";
   

   Se il gruppo di risorse esiste già, potrebbe essere preferibile non sovrascriverlo. Assegnare un valore diverso alla variabile $ResourceGroupName ed eseguire di nuovo il comando.

2. Per creare il gruppo di risorse di Azure, eseguire questo comando:

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

   Se il gruppo di risorse esiste già, potrebbe essere preferibile non sovrascriverlo. Assegnare un valore diverso alla variabile $ResourceGroupName ed eseguire di nuovo il comando.

3. Definire una variabile per il nome della data factory.

   Importante

   Aggiornare il nome della data factory in modo che sia univoco a livello globale.

   $DataFactoryName = "ADFTutorialFactory";
   

4. Per creare la data factory, eseguire il cmdlet Set-AzDataFactoryV2 usando le proprietà Location e ResourceGroupName della variabile $ResGrp:

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

Notare i punti seguenti:

• È necessario specificare un nome univoco globale per l'istanza di Azure Data Factory. Se viene visualizzato l'errore seguente, modificare il nome e riprovare.

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

• Per creare istanze di Data Factory, l'account utente usato per accedere ad Azure deve essere un membro dei ruoli collaboratore o proprietario oppure un amministratore della sottoscrizione di Azure.

• Per un elenco di aree di Azure in cui Data Factory è attualmente disponibile, selezionare le aree di interesse nella pagina seguente, quindi espandere Analytics per individuare Data Factory: Prodotti disponibili in base all'area. Gli archivi dati (Archiviazione di Azure, database SQL di Azure e così via) e le risorse di calcolo (HDInsight e così via) usati dalla data factory possono trovarsi in altre aree.

Creare un servizio collegato Database SQL di Azure

Creare un servizio collegato per collegare il database che ospita il catalogo SSIS alla data factory. Data Factory usa le informazioni in questo servizio collegato per connettersi al database SSISDB ed esegue una stored procedure per l'esecuzione di un pacchetto SSIS.

1. Creare un file JSON denominato AzureSqlDatabaseLinkedService.json nella cartella C:\ADF\RunSSISPackage con il contenuto seguente:

   Importante

   Sostituire <servername>, <username> e <password> con i valori del database SQL di Azure prima di salvare il file.

   {
       "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. In Azure PowerShell passare alla cartella C:\ADF\RunSSISPackage.

3. Eseguire il cmdlet Set-AzDataFactoryV2LinkedService per creare il servizio collegato AzureSqlDatabaseLinkedService.

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

Creare una pipeline con un'attività stored procedure

In questo passaggio viene creata una pipeline con un'attività stored procedure. L'attività chiama la stored procedure sp_executesql per l'esecuzione del pacchetto SSIS.

1. Creare un file JSON denominato RunSSISPackagePipeline.json nella cartella C:\ADF\RunSSISPackage con il contenuto seguente:

   Importante

   Sostituire <FOLDER NAME>, <PROJECT NAME> e <PACKAGE NAME> con i nomi di cartella, progetto e pacchetto nel catalogo SSIS prima di salvare il file.

   {
       "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. Per creare la pipeline: RunSSISPackagePipeline, eseguire il cmdlet Set-AzDataFactoryV2Pipeline .

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

   Di seguito è riportato l'output di esempio:

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

Creare un'esecuzione della pipeline

Usare il cmdlet Invoke-AzDataFactoryV2Pipeline per eseguire la pipeline. Il cmdlet restituisce l'ID di esecuzione della pipeline per il monitoraggio futuro.

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

Monitorare l'esecuzione della pipeline

Eseguire lo script di PowerShell seguente per verificare continuamente lo stato di esecuzione della pipeline fino al termine della copia dei dati. Copiare/Incollare lo script seguente nella finestra di PowerShell e premere INVIO.

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
}   

Creare un trigger

Nel passaggio precedente è stata chiamata la pipeline su richiesta. È anche possibile creare un trigger di pianificazione per eseguire la pipeline in base a una pianificazione (oraria, giornaliera e così via).

1. Creare un file JSON denominato MyTrigger.json nella cartella C:\ADF\RunSSISPackage con il contenuto seguente:

   {
       "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. In Azure PowerShell passare alla cartella C:\ADF\RunSSISPackage.

3. Eseguire il cmdlet Set-AzDataFactoryV2Trigger , che crea il trigger.

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

4. Per impostazione predefinita, lo stato del trigger è arrestato. Avviare il trigger eseguendo il cmdlet Start-AzDataFactoryV2Trigger .

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

5. Verificare che il trigger venga avviato eseguendo il cmdlet Get-AzDataFactoryV2Trigger .

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

6. Eseguire il comando seguente dopo l'ora successiva. Ad esempio, se l'ora corrente è 15:25 UTC, eseguire il comando alle 16 UTC.

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

   È possibile eseguire la query seguente sul database SSISDB in database SQL per verificare che il pacchetto sia stato eseguito.

   select * from catalog.executions
   

Contenuto correlato

È anche possibile monitorare la pipeline tramite il portale di Azure. Per istruzioni dettagliate, vedere Monitorare la pipeline.