En esta publicación del blog, exploraremos cómo ejecutar paquetes de SQL Server Integration Services (SSIS) desde PowerShell. Cubriremos la ejecución de paquetes almacenados en la nube utilizando Azure-SSIS Integration Runtime, así como la ejecución de paquetes utilizando el modelo de implementación de paquetes.

Ejecución de paquetes SSIS almacenados en Azure desde PowerShell

Con las capacidades de migración de Azure Data Factory, puede migrar sus proyectos SSIS existentes a Azure Data Factory. Los paquetes se almacenan en un catálogo SSIS, que se guarda en la base de datos SSISDB. Esta base de datos puede alojarse en una base de datos Azure SQL o en una instancia administrada de Azure SQL Server. Los paquetes son ejecutados por Azure-SSIS Integration Runtime (IR), que se aloja en Azure Data Factory (ADF). La programación también se puede realizar en ADF.

Para ejecutar paquetes SSIS almacenados en un SSISDB en la nube utilizando Azure-SSIS IR, podemos usar PowerShell. El script es similar a la ejecución de un paquete SSIS almacenado localmente, con la diferencia principal de la necesidad de proporcionar un nombre de usuario y una contraseña para la autenticación.

Aquí hay un ejemplo de script:

# Variables
$SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
$TargetServerName = "mssqltips.database.windows.net"
$TargetFolderName = "MSSQLTips"
$ProjectName = "MSSQLTIPS"
$PackageName = "ExecuteFromPowerShell.dtsx"
$EnvironmentName = "TEST"
$username = "myusername"
$pw = "mypassword"

# Cargar la biblioteca de Integration Services
$loadStatus = [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SQLServer.Management.IntegrationServices") | Out-Null;

# Crear conexión al servidor
$sqlConnectionString = "Data Source=" + $TargetServerName + ";Initial Catalog=SSISDB;User ID=" + $username + ";Password=" + $pw + ";"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Crear objeto SSIS
$integrationServices = New-Object $SSISNamespace".IntegrationServices" $sqlConnection
# Obtener el catálogo SSIS
$catalog = $integrationServices.Catalogs["SSISDB"]
# Obtener la carpeta
$folder = $catalog.Folders[$TargetFolderName]
# Obtener el proyecto
$project = $folder.Projects[$ProjectName]
# Obtener el paquete
$package = $project.Packages[$PackageName]
# Obtener el entorno
$environment = $folder.Environments[$EnvironmentName]
# Obtener la referencia del entorno
$environmentReference = $project.References.Item($EnvironmentName, $TargetFolderName)
$environmentReference.Refresh()

Write-Host "Ejecutando " $PackageName " en Azure con el entorno..."
$result = $package.Execute("false", $null) # Execute sobrecargado
Write-Host "Hecho. " $result

Tenga en cuenta que al ejecutar un paquete almacenado en una base de datos Azure SQL, actualmente hay un problema con el método “Execute”. Este problema se solucionará en el futuro. Sin embargo, el mismo script debería funcionar cuando se utiliza una instancia administrada de Azure SQL Server para almacenar los paquetes.

Ejecución de paquetes SSIS locales utilizando el modelo de implementación de paquetes

Además de ejecutar paquetes almacenados en Azure, también podemos ejecutar paquetes utilizando el modelo de implementación de paquetes. En este modelo, utilizamos la utilidad DTEXEC para ejecutar el paquete fuera del entorno de desarrollo de Visual Studio.

Aquí hay un ejemplo de script:

$PackageName = "Package.dtsx"
$PackageLocation = "D:\Test\"
$pw = "mssqltips"

dtexec /File ($PackageLocation + $PackageName) /De $pw

Este script llama a la utilidad DTEXEC y especifica la ubicación del archivo del paquete y la contraseña. La salida se imprime directamente en la pantalla.

Si desea pasar parámetros de entrada, puede utilizar un archivo de configuración. Tenga en cuenta que los parámetros de paquete/proyecto no existen en el modelo de implementación de paquetes, por lo que con el archivo de configuración, sobrescribimos el valor de una variable.

Aquí hay un script actualizado con un archivo de configuración:

$PackageName = "Package.dtsx"
$PackageLocation = "D:\Test\"
$pw = "mssqltips"
$configfile = "D:\Test\myconfig.dtsConfig"

dtexec /File ($PackageLocation + $PackageName) /De $pw /Conf $configfile

Con este script, podemos ver el resultado esperado en la tabla de SQL Server.

Conclusión

La ejecución de paquetes de SQL Server Integration Services desde PowerShell proporciona una forma flexible y eficiente de automatizar y gestionar sus flujos de trabajo SSIS. Ya sea que sus paquetes estén almacenados en Azure o en local, los scripts de PowerShell se pueden utilizar para ejecutarlos con facilidad.