PowerShell y SQLBI: Ejecución de consultas SQL

La ejecución de sentencias T-SQL a través de PowerShell puede resultarnos útil en diversos escenarios, en mi caso las encuentro una forma eficiente de realizar tareas de despliegue como crear bases de datos, tablas, procedimientos, logins, jobs.

Con esta entrada se inicia una serie con la idea de compartir scripts PowerShell para la automatización de esas tareas despliegue de elementos de BI: objetos SQL relacional, paquetes o proyectos Integration Services, informes de Reporting Services y todo lo que se os pueda ocurrir sobre bases de datos OLAP.

Advertencia. El código provisto en esta página no debe ejecutarse en un entorno de producción, no tiene ninguna garantía de que os vaya a funcionar como esperáis.

Como primera aproximación vamos a ver la forma más sencilla de ejecutar una consulta T-SQL en una instancia SQL Server:

#Ejecutar una consulta T-SQL desde PowerShell
#Declaramos las variables necesarias
$ServerInstance="localhost\sql2016"
$Database="master"
$QueryStr="Create database SQLMeridiano"

#Creamos el objecto de SqlConnection, nativo en framework
[System.Data.SqlClient.SqlConnection]$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

#Asignamos la propiedad connection string  (auth windows integrated, AD)
$SqlConnection.ConnectionString="Server=$ServerInstance;Database=$Database;Integrated Security=SSPI;"

#Creamos el objecto SqlCommand y le asignamos la conexión previamente creada
[System.Data.SqlClient.SqlCommand]$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection=$SqlConnection

#Establecemos el tipo de comando a texto (puede ser text, TableDirect o StoredProcedure) y asignamos la query al comando
$SqlCmd.CommandType = [System.Data.CommandType]::Text
$SqlCmd.CommandText = $QueryStr

#Abrimos la conexión y ejecutamos la query
$SqlConnection.Open()
$SqlCmd.ExecuteNonQuery()

Tened en cuenta que no hay control de errores. Si se ejecuta correctamente el resultado será un -1. En caso contrario devolverá el error correspondiente, que puede deberse a un fallo en la conexión por cualquier motivo (seguridad, discovery, etc..) o en la propia ejecución de la consulta.

Este método es totalmente válido, aunque tiene sus limitaciones. Si necesitamos ejecutar una secuencia de comandos T-SQL separadas por ‘GO’, algo habitual en los scripts de despliegue, el método ExecuteNonQuery() de la clase SqlCommand no lo permite. Si cambiamos la linea 5 por la siguiente

$QueryStr="Create database SQLMeridiano`nGO`nUse SQLMeridiano;"

Obtendremos el siguiente error

Sin embargo disponemos de la clase Microsoft.SqlServer.ConnectionInfo para resolver el problema. Por otra parte, es interesante obtener información sobre la ejecución y resultado de los distintos comandos del script -TSQL para lo que podemos utilizar los eventos de los objetos instanciados que están procesando las peticiones.

En el siguiente script PowerShell se utiliza los ensamblados ConnectionInfo y SMO, además se registran los eventos InfoMessage y ServerMessage del objeto Microsoft.SqlServer.Management.Common.ServerConnection instanciado (https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection(v=sql.120).aspx) Para instanciarlo he utilizado el constructor que admite el objeto System.Data.SqlClient.SqlConnection como parámetro, para mantener el control sobre la base de datos a la que quiero conectar.

#Ejecutar una consulta T-SQL desde PowerShell
#Declaramos las variables necesarias
$ServerInstance="localhost\sql2016"
$Database="sqlmeridiano"
$QueryStr="Create database SQLMeridiano`nGO`nUse SQLMeridiano;"
#Creamos objeto SQLConnection
[System.Data.SqlClient.SqlConnection]$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
[string]$ConnectionString="Server=$ServerInstance;Database=$Database;Integrated Security=SSPI;"
$SqlConnection.ConnectionString=$ConnectionString
$Sqlconnection.FireInfoMessageEventOnUserErrors=$true

#Carga de los ensamblados necesarios
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | Out-Null

#Instanciamos e inicializamos los objetos SMO Server y ServerConnection
[Microsoft.SqlServer.Management.Common.ServerConnection]$SqlServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $SqlConnection
[Microsoft.SqlServer.Management.Smo.Server]$SqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServerConnection
New-variable -name LastMessage -Scope Global -Force -ea SilentlyContinue

#Asociamos acciones para los eventos InfoMessage y ServerMessage
Register-ObjectEvent -InputObject $SqlServerConnection -EventName InfoMessage -Action `
    { 
    Write-Host "`t`t[InfoMessage]:: $($Event.SourceEventArgs)" -fore Cyan
    Write-Host "`t`t$($eventArgs.Message)"
} -SupportEvent
Register-ObjectEvent -InputObject $SqlServerConnection -EventName ServerMessage -Action `
{ 
    Write-Host "`t`t[InfoMessage]:: $($Event.SourceEventArgs)" -fore Cyan
} -SupportEvent
        
#Ejecutamos la consulta con control de posibles errores
try
{
    $SqlConnection.Open()
    $SqlServerConnection.Connect()
    $SqlServerConnection.SqlExecutionModes= [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::CaptureSql
    $result = $SqlServerConnection.ExecuteNonQuery($QueryStr)
    $SqlServerConnection.SqlExecutionModes= [Microsoft.SqlServer.Management.Common.SqlExecutionModes]::ExecuteSql
    $result = $SqlServerConnection.ExecuteNonQuery($Sqlserverconnection.capturedsql.Text)
    return $True
}
catch
{
    Throw "$_"
    return $false
}
finally
{
    remove-variable SqlServer
    $SqlServerConnection.Disconnect()
    remove-variable SqlServerConnection
    $SqlConnection.Close()
    remove-variable SqlConnection
}

Con el siguiente resultado:

Como podéis ver, los mensajes recogidos a través de los eventos facilitan información sobre los distintos comandos ejecutados.

Este último es uno de lo scripts PowerShell que empleo y me permiten agilizar varias de las muchas tareas de despliegue. Espero que os sea tan útil como a mi.

Saludos!