Script de Powershell para MSSQL

Este script puede utilizarse para importar automáticamente archivos SQL con más de 20 minutos de antigüedad en la base de datos MSSQL. Es útil si activas el registro de texto SQL para configuraciones de alto rendimiento. Para ejecutar este script, debes guardarlo con la extensión .ps1, y habilitar la ejecución de scripts de PowerShell en Windows mediante el editor de directivas de grupo (o debes ejecutar el script como Administrador).

Video 1 - Cómo usar un script de PowerShell en una base de datos MSSQL (Tutorial en video)

Crea la siguiente tabla en MSSQL

CREATE TABLE messagereport
(
    logid int IDENTITY(1,1),
    messageid varchar(64),
    fromconnection varchar(64),
    fromaddress varchar(64),
    requestedconnection varchar(64),
    toconnection varchar(64),
    toaddress varchar(64),
    messagetext varchar(1024),
    route varchar(64),
    datecreated datetime,
    datedeliverytoconnectionsuccess datetime,
    datedeliverytoconnectionfailed datetime,
    deliverytoconnectionreference varchar(256),
    deliverytoconnectionstatusmessage varchar(1024),
    datedeliverytorecipientsuccess datetime,
    datedeliverytorecipientfailed datetime,
    deliverytorecipientstatusmessage varchar(1024),
    status varchar(64)   
)

El primer paso es crear la tabla messagereport en la base de datos. Todos los mensajes entrantes y salientes se almacenarán en esta tabla y se actualizarán según su estado. Si ya has creado esta tabla, puedes omitir este paso. Copia el script de creación de tabla anterior y ejecútalo en tu base de datos como se muestra en la Figura 1.

crear tabla messagereport
Figura 1 - Crear la tabla messagereport

Guarda el siguiente script de PowerShell como "dbimport.ps1"

El siguiente paso es crear el archivo "dbimport.ps1" en la carpeta C:\Ozeki como se muestra en la Figura 2. Este archivo contendrá el script que ejecuta consultas SQL periódicamente en la base de datos.

crear el archivo de script
Figura 2 - Crear el archivo de script

$ProcessedDir = "C:\Ozeki\Processed"
New-Item -ItemType Directory -Force -Path $ProcessedDir

#bucle infinito
while (1) {
	#listar archivos con más de 20 minutos de antigüedad
	#asegúrate de que el tiempo sea mayor que el período configurado en el informe
	Get-ChildItem "C:\Ozeki\Reporting_SQL" -Filter *.sql | 
		Where{$_.LastWriteTime -le (Get-Date).AddMinutes(-20)} |
	
	Foreach-Object {
		#cargar el contenido del archivo en la base de datos MSSQL
		Write-Output $_.FullName
		$Text = Get-Content -Path $_.FullName
		Invoke-Sqlcmd -ServerInstance MyComputer\MainInstance -Database MyDatabase
		-Query "$Text" -Username "MyUser" -Password "MyPass"

		
		#mover el archivo procesado al directorio de procesados
		Move-Item -Path $_.FullName -Destination $ProcessedDir
	}
	
	#esperar 5 segundos
	Start-Sleep -s 5
}

Copia el script de PowerShell anterior y pégalo en el archivo "dbimport.ps1" como se muestra en la Figura 3.

pegar el script en el archivo
Figura 3 - Pegar el script en el archivo

Finalmente, modifica el script según tu propia base de datos MSSQL. En la instancia del servidor, define el nombre del equipo y la instancia de la base de datos MSSQL. Ingresa el nombre de usuario y la contraseña que deseas usar, y reemplaza "Mydatabase" con el nombre de tu base de datos (Figura 4).

modificar comando mysql
Figura 4 - Modificar el comando MySQL

Ejecuta el script como administrador

powershell -ExecutionPolicy Bypass -File dbimport.ps1

Ahora ejecuta el script en PowerShell ejecutando el comando anterior como se muestra en la Figura 5.

ejecutar script de powershell
Figura 5 - Ejecutar script de PowerShell

Puedes ver que el script ejecuta todos los archivos SQL con más de 20 minutos de antigüedad en la carpeta 'C:\Ozeki\Reporting_SQL' uno por uno (Figura 6).

script ejecuta consultas sql
Figura 6 - Script ejecuta consultas SQL

Cada archivo SQL que el script ha ejecutado se mueve a la carpeta Processed para que no se pierda ninguna consulta (Figura 7). Desde aquí, puedes eliminarlos manualmente si ya no los necesitas.

archivos sql movidos a la carpeta procesados
Figura 7 - Archivos SQL movidos a la carpeta procesados

Finalmente, si revisas la base de datos, puedes ver que todos los mensajes han sido insertados en la tabla messagereport como muestra la Figura 8.

mensajes insertados en la base de datos
Figura 8 - Mensajes insertados en la base de datos

More information