Integration Services, SQL Server, SQL Server 2012, SSIS

Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos (1)

Dentro del contexto de cualquier sistema de información, resulta fundamental la existencia de una adecuada política de copia de seguridad, como medio de contar con un respaldo para proteger los datos almacenados.

Si nos ceñimos, sin embargo, al desarrollo de sistemas Business Intelligence, con cierta frecuencia podemos encontrarnos ante un almacén de datos en el que cada vez que debamos procesar los cubos y dimensiones que lo integran, primeramente habremos de vaciar las tablas asociadas a dichos elementos, para recargarlas por completo con datos actualizados provenientes de muy diversas fuentes.

En un escenario como el que presentamos, antes que la salvaguardia de los datos resulta más importante hacer una copia de la estructura de las tablas, vistas, procedimientos almacenados, etc., de la base de datos, máxime si se trata de un almacén de datos que se encuentra en pleno proceso de desarrollo, ya que la información reside en las fuentes de datos originales que se usan para realizar la carga del almacén, las cuales, habitualmente, disponen de sus propios mecanismos de copia de seguridad.

Por dicho motivo, en el presente artículo vamos a centrarnos en desarrollar un proceso que se ocupe de generar, para cada una de las bases de datos de nuestra instancia de SQL Server, un archivo de script con todas las instrucciones de definición (Data Definition Language) de los objetos integrantes de la base de datos. Para lograrlo emplearemos paquetes de SSIS (SQL Server Integration Services), y más concretamente, el componente “Script Task”, que nos otorgará un alto grado de flexibilidad y control sobre la generación de los archivos de script y los objetos de la base de datos a incluir en dichos archivos.

 

Scptxfr.exe. Los orígenes en SQL Server 2000

Pero antes de adentrarnos en el desarrollo del proceso en SSIS, queremos hacer mención a scptxfr.exe, una herramienta que puede resultar de utilidad para aquellos lectores que todavía tengan que realizar el mantenimiento de sistemas basados en SQL Server 2000.

Ejecutada en modo línea de comando desde una ventana “Símbolo del sistema”, o bien desde un procedimiento almacenado, también nos permite, aunque con menos flexibilidad que en SSIS, como veremos más adelante, generar un conjunto de archivos con las instrucciones de creación de tablas, índices, claves primarias, vistas, etc., necesarios para la adecuada regeneración de una base de datos.

Entre los parámetros de que dispone, destacaremos los siguientes:

— /s. Nombre del servidor SQL Server con el que vamos a trabajar.

— /d. Nombre de la base de datos a partir de la que vamos a generar los scripts.

— /I. Tipo de autenticación.

— /F. Nombre (incluyendo ruta si es preciso) del archivo script destino.

Para una descripción más detallada de todos los parámetros, sugerimos al lector que ejecute la instrucción SCPTXFR /? desde una ventana de símbolo del sistema.

De esta forma podríamos crear un procedimiento almacenado como el que vemos a continuación, que recorriera las bases de datos del servidor SQL Server, y creara, por cada una de ellas, una carpeta con los archivos de script correspondientes.

CREATE PROCEDURE CrearEstructurasDB
AS
 
CREATE TABLE #NombresDBs
(
Linea_ID int identity(1,1), 
Nombre varchar(150)
)
 
INSERT INTO #NombresDBs
SELECT name FROM master..sysdatabases 
ORDER BY name
DECLARE @sDirectorioBak varchar(50)
SET @sDirectorioBak = 'c:pruebas' + convert(varchar,getdate(),112) + ''
 
DECLARE @nLineaID int
SET @nLineaID = 1
 
DECLARE @nLineaIDUltimo int
SET @nLineaIDUltimo = (SELECT MAX(Linea_ID) FROM #NombresDBs)
 
DECLARE @sCmdCrearDirectorio varchar(200)
DECLARE @sNombreDB varchar(150)
DECLARE @sCmd varchar(300)
 
WHILE (@nLineaID <= @nLineaIDUltimo)
BEGIN
SELECT @sNombreDB = Nombre FROM #NombresDBs WHERE Linea_ID = @nLineaID
 
SET @sCmdCrearDirectorio = 'MKDIR' + @sDirectorioBak + @sNombreDB
 
EXECUTE master..xp_cmdshell @sCmdCrearDirectorio
 
SET @sCmd = '"' + 'C:Archivos de programaMicrosoft SQL ServerMSSQLUpgradescptxfr' + '" /s ' +
@@servername + ' /d ' + @sNombreDB + ' /I /F ' + @sDirectorioBak + @sNombreDB
 
EXECUTE master..xp_cmdshell @sCmd
 
SET @nLineaID = @nLineaID + 1
end
 
DROP TABLE #NombresDBs

 

Dado que el anterior procedimiento almacenado crea los archivos de script en una ruta cuyo nombre incluye la fecha del sistema, podemos programar su ejecución dentro de una tarea de SQL Server, y obtener de esa forma una copia de respaldo diaria o con la frecuencia que establezcamos, de los scripts de creación de las bases de datos.

 

SSIS y los paquetes de integración. La evolución

Pero según reza su título, nos encontramos ante un artículo basado en SSIS, por lo que tendremos que usar SQL Server 2005 o superior, ya que es a partir de dicha versión cuando se incluyó esta tecnología de integración de datos.

Utilizaremos por lo tanto SQL Server Denali CTP3, dado que se trata de la versión más reciente disponible de SQL Server en el momento de desarrollar los ejemplos del artículo. Para generar los scripts de estructuras emplearemos las bases de datos AdventureWorks2008R2 y Chinook.

 

El proyecto de servicios de integración

En primer lugar iniciaremos “SQL Server Business Intelligence Development Studio” (SSBIDS), o lo que es lo mismo, el entorno  de desarrollo de Visual Studio 2010, situado en la ruta de menú “Inicio |Microsoft SQL Server Denali CTP3”.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_01

 

Esta versión especial de Visual Studio 2010, que acompaña a SQL Server, contiene las plantillas específicas para la creación de proyectos de inteligencia de negocio. Por lo que seleccionando la opción de menú “File | New | Project”, abriremos el diálogo “New Project”, donde elegiremos un proyecto de tipo “Integration Services Project” al que daremos el nombre EstructurasBBDD.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_02

 

Este tipo de proyecto incluye por defecto un paquete llamado Package.dtsx, al que cambiaremos el nombre por PckEstructurasBBDD.dtsx, utilizando la propiedad “File Name” de la ventana de propiedades. También podemos hacer clic derecho en el paquete y elegir la opción de menú “Rename”.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_03

 

Script Task. Desarrollo programático de operaciones personalizadas  

Una vez abierto el paquete, el siguiente paso consistirá en añadir al diseñador una tarea de código fuente haciendo doble clic en el icono “Script Task”, situado en la ventana “SSIS Toolbox” del entorno de desarrollo.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_04

 

También cambiaremos el nombre predeterminado de este elemento por scptEstructurasBBDD editando su propiedad Name.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_05

 

Escritura y ejecución del código de Script Task

Para escribir el código de nuestras operaciones haremos clic derecho sobre el objeto Script Task, y elegiremos la opción de menú “Edit”, que abrirá el diálogo “Script Task Editor”. De las diversas propiedades disponibles, ScriptLanguage, ubicada dentro de la categoría “Script”, nos permite establecer el lenguaje que vamos a usar para programar el proceso (por defecto es C#, aunque también podemos utilizar Visual Basic). Haciendo clic en el botón “Edit Script” abriremos el editor de código de Visual Studio 2010, ventana que nos resultará familiar si hemos trabajado anteriormente con este entorno de desarrollo.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_06

 

El punto de entrada en la ejecución del paquete lo representa el método Main de la clase ScriptMain, que viene integrada por defecto en el código del paquete. En este método escribiremos el código con aquellas operaciones necesarias para interactuar con los objetos de las bases de datos de la instancia de SQL Server con la que vayamos a trabajar.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_07

 

Para familiarizarnos con el modelo de objetos a manejar y facilitar así el aprendizaje del proceso, vamos a comenzar implementando una operación muy sencilla: recorrer las bases de datos del servidor y visualizar su nombre.

En primer lugar necesitamos tener acceso a las clases que representan los objetos del servidor SQL, por lo que debemos añadir las siguientes referencias al paquete:

–Microsoft.SqlServer.ConnectionInfo

–Microsoft.SqlServer.Management.Sdk.Sfc

–Microsoft.SqlServer.Smo

Haciendo clic derecho sobre el nodo “References” de la ventana “Solution Explorer” elegiremos la opción “Add Reference”, que abrirá un diálogo con el mismo nombre desde cuya pestaña “.NET” realizaremos la selección y agregación de componentes al paquete.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_08

 

A continuación escribiremos el siguiente bloque de código, que creará una instancia de la clase Microsoft.SqlServer.Management.Smo.Server (que representa al servidor SQL), pasando como parámetros a su constructor una cadena con el nombre del servidor. Utilizando dicho objeto Server, recorreremos su colección Databases, visualizando el nombre de cada una mediante una ventana MessageBox.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
//....
public void Main()
{
    Server oServer = new Server("WINTERFELL-PC");

    foreach (Database oDatabase in oServer.Databases)
    {
        MessageBox.Show(oDatabase.Name);
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

 

Seguidamente cerraremos el editor de código y aceptaremos el diálogo “Script Task Editor”, poniendo en ejecución el paquete mediante la opción de menú “Debug | Start debugging” o pulsando la tecla F5, que irá mostrando los mensajes correspondientes con los nombres de las bases de datos.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_09

 

Finalizada la ejecución, haremos clic en el enlace “Package execution completed…” que aparece en la parte inferior del diseñador del paquete, o seleccionaremos la opción de menú “Debug | Stop Debugging” para salir de la ejecución desde el depurador.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_10

 

No obstante, a pesar de resultar factible, no consideramos muy adecuado el uso de MessageBox para comunicar información al usuario acerca de la ejecución del paquete, ya que estimamos que en un alto porcentaje de las ocasiones, un paquete de estas características se desarrolla para su ejecución desatendida a través de una tarea (Job) del Agente de SQL Server.

Si bien con el depurador podemos obtener toda la información necesaria en tiempo de ejecución, su uso se limita a Visual Studio, por lo que solamente puede ser utilizado durante la fase de desarrollo del paquete. Debemos, por lo tanto, encontrar un medio alternativo para poder informar al usuario de lo que está sucediendo durante la ejecución.

La solución a este problema la encontramos en el método Dts.Events.FireInformation, que hace que el paquete envíe un evento informativo al exterior. Sustituyendo la llamada a MessageBox.Show por este otro método, nuestro código quedará como vemos a continuación.

//....
bool bOtraVez = false;
foreach (Database oDatabase in oServer.Databases)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
}
//....

 

De esta forma, al ejecutar el paquete podremos realizar un seguimiento en la ejecución del mismo desde la pestaña “Execution Results” (“Progress” en tiempo de ejecución) del diseñador, y también desde la ventana “Output” del depurador, donde veremos los mensajes generados mediante el método FireInformation.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_11

 

Ejecutando el paquete fuera del entorno de SSBIDS

Pero comprobemos, ejecutando el paquete de forma independiente, que estos mensajes de notificación también funcionan fuera del entorno de SSBIDS. Supongamos que el archivo PckEstructurasBBDD.dtsx del proyecto de integración que estamos desarrollando se encuentra en la ruta C:pruebasSSISEstructurasBBDDEstructurasBBDD; lo primero que tenemos que hacer es iniciar SQL Server Management Studio (SSMS), conectando con el servicio “Integration Services” como vemos en la siguiente imagen.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_12

 

A continuación desplegaremos  los nodos “Stored Packages | File System”, haremos clic derecho en este último, y seleccionaremos la opción “Import Package”, abriéndose una ventana con el mismo nombre. En esta ventana desplegaremos la lista “Package location”, eligiendo “File System”, mientras que en el campo “Package path” introduciremos la ruta y el nombre del archivo .dtsx perteneciente al paquete que estamos programando. El nombre del paquete (campo Package name) se rellena por defecto con el mismo del archivo .dtsx, aunque podemos utilizar otro distinto.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_13

 

Una vez completados los campos de esta ventana haremos clic en “OK”, siendo el paquete agregado al servidor de Integration Services de SQL Server.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_14

 

Ejecutaremos el paquete haciendo clic derecho en el mismo y seleccionando la opción “Run Package”, que abrirá la ventana “Execute Package Utility”, en la que haremos clic en el botón “Execute”.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_15

 

Esta ventana utiliza internamente el ejecutable DTExec.exe, que también podemos usar desde una ventana “Símbolo del sistema”, como explicaremos en la segunda parte del artículo.

Durante la ejecución del paquete, la ventana “Package Execution Progress” mostrará una serie de mensajes informativos acerca de su progreso, incluyendo aquellos que enviamos desde el código fuente utilizando el método FireInformation anteriormente explicado.

UtilizandoSSISCrearScriptsRespaldoEstructuraBD_16

 

Llegados a este punto hacemos una pequeña parada en el camino y concluimos esta primera parte del artículo. En la segunda entrega abordaremos la creación de los archivos de script que contendrán las sentencias para crear los objetos de nuestras bases de datos.

Saludos.

5 Comentarios

  1. kiquenet

    Muybueno Luismi, esperaremos al siguiente artículo. A la máquina virtual le sacas partido 😀

  2. lmblanco

    Hola Enrique

    Muchas gracias por tu interés en el post. Acabo de subir la segunda parte al blog por si también quieres echarle un vistazo 😉

    Tienes razón con las máquinas virtuales. Para algunos casos dan mucho juego 8-))))

    Un saludo,
    Luismi

  3. anonymous

    En la primera entrega de este artículo realizábamos una introducción al desarrollo

  4. anonymous

    En el artículo "Utilizando SSIS para crear scripts de respaldo con la estructura de la base

  5. luchero21

    man muy bien x este post, me puedes ayudar teniendo un messagebox para una tabla que hace un join consultando a dos tablas si se encuentra registros y que lance mensaje no se encuentra en la tabla maestro

Leave a Reply

Tema creado por Anders Norén