Ejecución de un paquete SSIS con la actividad de procedimiento almacenado

Artículo
07/20/2023

SE APLICA A: Azure Data Factory Azure Synapse Analytics

Sugerencia

Pruebe Data Factory en Microsoft Fabric, una solución de análisis todo en uno para empresas. Microsoft Fabric abarca todo, desde el movimiento de datos hasta la ciencia de datos, el análisis en tiempo real, la inteligencia empresarial y los informes. Obtenga información sobre cómo iniciar una nueva evaluación gratuita.

En este artículo se describe cómo ejecutar un paquete de SSIS desde una canalización de Azure Data Factory o las canalizaciones de Synapse mediante una actividad de procedimiento almacenado.

Requisitos previos

Azure SQL Database

En el tutorial de este artículo se usa Azure SQL Database para hospedar el catálogo de SSIS. También puede usar Instancia administrada de Azure SQL.

Data Factory

Necesitará una instancia de Azure Data Factory para implementar este tutorial. Si aún no tiene uno aprovisionado, siga los pasos descritos en Inicio rápido: Creación de una factoría de datos mediante la Azure Portal y Azure Data Factory Studio.

Integration Runtime de SSIS de Azure

Por último, también necesitará una instancia de Integration Runtime de SSIS de Azure si no tiene ninguna. Para ello, siga las instrucciones paso a paso del tutorial Implementación paquetes de SSIS en Azure.

Crear una canalización con una actividad de procedimiento almacenado

En este paso, usa la interfaz de Data Factory para crear una canalización. Si aún no ha navegado al Azure Data Factory Studio, abra la factoría de datos en Azure Portal y haga clic en el botón Abrir Azure Data Factory Studio para abrirlo.

Screenshot of the Azure Data Factory home page.

A continuación, agregará una actividad de procedimiento almacenado a una nueva canalización y lo configura para ejecutar el paquete SSIS mediante el uso del procedimiento almacenado sp_executesql.

En la página principal, haga clic en Orquestar:
En el cuadro de herramientas Actividades, busque Procedimiento almacenado y coloque una actividad procedimiento almacenado en la superficie del diseñador de canalizaciones.
Seleccione la actividad Procedimiento almacenado que acaba de agregar a la superficie del diseñador y, a continuación, la pestaña Configuración. Después, haga clic en + Nuevo junto al servicio vinculado. Crea una conexión a la base de datos de Azure SQL Database que hospeda el catálogo de SSIS (base de datos SSIDB).
En la ventana New Linked Service (Nuevo servicio vinculado), realice los pasos siguientes:
1. Seleccione Azure SQL Database para Type (Tipo).
2. Seleccione el valor predeterminado de AutoResolveIntegrationRuntime para conectarse a la instancia de Azure SQL Database que hospeda la base de datos SSISDB.
3. Seleccione la base de datos de Azure SQL que hospeda la base de datos SSISDB para el campo Server name (Nombre del servidor).
4. Seleccione SSISDB para el campo Database name (Nombre de la base de datos).
5. En User name (Nombre de usuario), escriba el nombre del usuario que tiene acceso a la base de datos.
6. En Password (Contraseña), escriba la contraseña del usuario.
7. Para probar la conexión con la base de datos, haga clic en el botón Test connection (Prueba de conexión).
8. Guarde el servicio vinculado con un clic en el botón Save (Guardar).

De nuevo en la ventana de propiedades de la pestaña Configuración, complete los pasos siguientes:

Seleccione Editar.
En el campo Stored procedure name (Nombre del procedimiento almacenado), escriba sp_executesql.
Haga clic en + New (+ Nuevo) en la sección Stored procedure parameters (Parámetros del procedimiento almacenado).
En el nombre del parámetro, escriba stmt.
En el tipo de parámetro, escriba Cadena.

En el valor del parámetro, escriba la consulta SQL siguiente:

En la consulta SQL, especifique los valores correctos para los parámetros folder_name, project_name y 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

Azure SQL Database linked service

Para validar la configuración de la canalización, haga clic en Validate (Validar) en la barra de herramientas. Para cerrar Pipeline Validation Report (Informe de comprobación de la canalización), haga clic en >>.
Publique la canalización en Data Factory con un clic en el botón Publish All (Publicar todo).

Ejecución y supervisión de la canalización

En esta sección, desencadena una ejecución de canalización y luego la supervisa.

Para desencadenar una ejecución de canalización, haga clic en Trigger (Desencadenar) en la barra de herramientas y en Trigger now (Desencadenar ahora).
En la ventana Pipeline Run (Ejecución de canalización), seleccione Finish (Finalizar).
Cambie a la pestaña Monitor (Supervisar) de la izquierda. Verá la ejecución de canalización y su estado junto con otro tipo de información (como la hora de inicio de la ejecución). Para actualizar la vista, haga clic en Refresh (Actualizar).
Haga clic en el vínculo View Activity Runs (Ver ejecuciones de actividad) de la columna Actions (Acciones). Solo verá una ejecución de actividad porque la canalización solo tiene una actividad (actividad de procedimiento almacenado).
Puede ejecutar la consulta siguiente en la base de datos de SSISDB en SQL Database para comprobar la ejecución del paquete.
```
select * from catalog.executions
```

Nota:

También puede crear un desencadenador programado para la canalización de manera que esta se ejecute según una programación (por hora, cada día, etc.). Para ver un ejemplo, consulte Create a data factory - Data Factory UI (Creación de una factoría de datos: interfaz de usuario de Data Factory).

Azure PowerShell

Nota:

Se recomienda usar el módulo Azure Az de PowerShell para interactuar con Azure. Consulte Instalación de Azure PowerShell para empezar. Para más información sobre cómo migrar al módulo Az de PowerShell, consulte Migración de Azure PowerShell de AzureRM a Az.

En esta sección, usará Azure PowerShell para crear una canalización de Data Factory con una actividad de procedimiento almacenado que invoca un paquete SSIS.

Instale los módulos de Azure PowerShell siguiendo las instrucciones de Cómo instalar y configurar Azure PowerShell.

Crear una factoría de datos

Puede usar la misma factoría de datos que tiene el IR de SSIS de Azure o crear una factoría de datos independiente. El siguiente procedimiento detalla los pasos para crear una factoría de datos. Debe crear una canalización con una actividad de procedimiento almacenado en esta factoría de datos. La actividad de procedimiento almacenado ejecuta un procedimiento almacenado en la base de datos SSISDB para ejecutar el paquete de SSIS.

Defina una variable para el nombre del grupo de recursos que usa en los comandos de PowerShell más adelante. Copie el texto del comando siguiente en PowerShell, especifique el nombre del grupo de recursos de Azure entre comillas dobles y ejecute el comando. Por ejemplo: "adfrg".
```
$resourceGroupName = "ADFTutorialResourceGroup";
```
Si el grupo de recursos ya existe, puede que no desee sobrescribirlo. Asigne otro valor a la variable $ResourceGroupName y vuelva a ejecutar el comando
Para crear el grupo de recursos de Azure, ejecute el comando siguiente:
```
$ResGrp = New-AzResourceGroup $resourceGroupName -location 'eastus'
```
Si el grupo de recursos ya existe, puede que no desee sobrescribirlo. Asigne otro valor a la variable $ResourceGroupName y ejecute el comando de nuevo.
Defina una variable para el nombre de la factoría de datos.

Importante

Actualice el nombre de la factoría de datos para que sea globalmente único.
```
$DataFactoryName = "ADFTutorialFactory";
```
Para crear la factoría de datos, ejecute el siguiente cmdlet Set-AzDataFactoryV2 con las propiedades ResourceGroupName y Location de la variable $ResGrp:
```
$DataFactory = Set-AzDataFactoryV2 -ResourceGroupName $ResGrp.ResourceGroupName -Location $ResGrp.Location -Name $dataFactoryName 
```

Tenga en cuenta los siguientes puntos:

El nombre de la instancia de Azure Data Factory debe ser único de forma global. Si recibe el siguiente error, cambie el nombre y vuelva a intentarlo.
```
The specified Data Factory name 'ADFv2QuickStartDataFactory' is already in use. Data Factory names must be globally unique.
```
Para crear instancias de Data Factory, la cuenta de usuario que use para iniciar sesión en Azure debe ser un miembro de los roles colaborador o propietario, o de administrador de la suscripción de Azure.
Para una lista de las regiones de Azure en las que Data Factory está disponible actualmente, seleccione las regiones que le interesen en la página siguiente y expanda Análisis para poder encontrar Data Factory: Productos disponibles por región. Los almacenes de datos (Azure Storage, Azure SQL Database, etc.) y los procesos (HDInsight, etc.) que usa la factoría de datos pueden encontrarse en otras regiones.

Creación de un servicio vinculado de Azure SQL Database

Cree un servicio vinculado para vincular su base de datos que hospeda el catálogo de SSIS con la factoría de datos. Data Factory usa la información de este servicio vinculado para conectarse a la base de datos SSISDB y ejecuta un procedimiento almacenado para ejecutar un paquete de SSIS.

Cree un archivo JSON denominado AzureSQLDatabaseLinkedService.json en la carpeta C:\ADF\RunSSISPackage con el siguiente contenido:

Importante

Reemplace <servername>, <username> y <password> por los nombres de su base de datos de Azure SQL antes de guardar el archivo.

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

En Azure PowerShell, cambie a la carpeta C:\ADF\RunSSISPackage.

Ejecute el cmdlet Set-AzDataFactoryV2LinkedService para crear el servicio vinculado: AzureSqlDatabaseLinkedService.

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

Crear una canalización con una actividad de procedimiento almacenado

En este paso, debe crear una canalización con una actividad de procedimiento almacenado. La actividad invoca el procedimiento almacenado sp_executesql para ejecutar su paquete de SSIS.

Cree un archivo JSON con el nombre RunSSISPackagePipeline.json en la carpeta C:\ADF\RunSSISPackage con el siguiente contenido:

Importante

Reemplace <FOLDER NAME>, <PROJECT NAME> y <PACKAGE NAME> por los nombres de carpeta, proyecto y paquete del catálogo de SSIS antes de guardar el archivo.

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

Para crear la canalización: RunSSISPackagePipeline, ejecute el cmdlet Set-AzDataFactoryV2Pipeline.

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

Este es la salida de ejemplo:

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

Creación de una ejecución de canalización

Use el cmdlet Invoke-AzDataFactoryV2Pipeline para ejecutar la canalización. El cmdlet devuelve el identificador de ejecución de la canalización para realizar una supervisión en un futuro.

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

Supervisión de la ejecución de la canalización

Ejecute el script de PowerShell siguiente para comprobar continuamente el estado de ejecución de la canalización hasta que termine de copiar los datos. Copie y pegue el siguiente script en la ventana de PowerShell y presione ENTRAR.

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
}

Crear un desencadenador

En el paso anterior, invocó la canalización a petición. También puede crear un desencadenador de programación para ejecutar la canalización en una programación (cada hora, día, etc.).

Cree un archivo JSON con el nombre MyTrigger.json en la carpeta C:\ADF\RunSSISPackage con el siguiente contenido:

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

En Azure PowerShell, cambie a la carpeta C:\ADF\RunSSISPackage.

Ejecute el cmdlet Set-AzDataFactoryV2Trigger, que crea el desencadenador.

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

De manera predeterminada, el desencadenador está en estado detenido. Inicie el desencadenador al ejecutar el cmdlet Start-AzDataFactoryV2Trigger.
```
Start-AzDataFactoryV2Trigger -ResourceGroupName $ResGrp.ResourceGroupName -DataFactoryName $DataFactory.DataFactoryName -Name "MyTrigger" 
```

Confirme que el desencadenador se ha iniciado al ejecutar el cmdlet Get-AzDataFactoryV2Trigger.

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

Ejecute el comando siguiente al comenzar la hora siguiente. Por ejemplo, si la hora actual es 15:25 UTC, ejecute el comando a las 16:00 UTC.
```
Get-AzDataFactoryV2TriggerRun -ResourceGroupName $ResourceGroupName -DataFactoryName $DataFactoryName -TriggerName "MyTrigger" -TriggerRunStartedAfter "2017-12-06" -TriggerRunStartedBefore "2017-12-09"
```
Puede ejecutar la consulta siguiente en la base de datos de SSISDB en SQL Database para comprobar la ejecución del paquete.
```
select * from catalog.executions
```

También puede supervisar la canalización mediante Azure Portal. Para ver instrucciones paso a paso, consulte Supervisar la canalización.

Ejecución de un paquete SSIS con la actividad de procedimiento almacenado

Requisitos previos

Azure SQL Database

Data Factory

Integration Runtime de SSIS de Azure

Crear una canalización con una actividad de procedimiento almacenado

Ejecución y supervisión de la canalización

Azure PowerShell

Crear una factoría de datos

Creación de un servicio vinculado de Azure SQL Database

Crear una canalización con una actividad de procedimiento almacenado

Creación de una ejecución de canalización

Supervisión de la ejecución de la canalización

Crear un desencadenador

Contenido relacionado

Recursos adicionales