El aprendiz de brujo

En el artículo "Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos" (parte 1 - parte 2), publicado con anterioridad en este blog, dejábamos pendiente de desarrollar una mejora que nos permitiera acelerar los tiempos de creación de los archivos de script, ya que el uso de algunas colecciones proporcionadas por la clase Database, más concretamente Views, StoredProcedures y UserDefinedFunctions, al contener una importante cantidad de objetos del sistema, provocaba que el tiempo de ejecución del proceso se extendiese más de lo necesario, en el caso de que no necesitáramos añadir al script tales tipos de objetos.

Por tal motivo, la finalidad del presente artículo será ofrecer una solución a dicho problema, exponiendo una técnica mediante la cual conseguiremos reducir los mencionados tiempos de ejecución.

Creación del proyecto de Integration Services

Como gestor de datos utilizaremos SQL Server Denali CTP3, y como entorno de desarrollo "SQL Server Business Intelligence Development Studio" (SSBIDS), con el que crearemos un nuevo proyecto de tipo "Integration Services Project" al que daremos el nombre EstructurasBBDDOptimizado (este proyecto de ejemplo puede descargarse en el siguiente enlace). Para más detalles acerca de la configuración del proyecto: paquete de integración .dtsx, variables, referencias a ensamblados, etc., consultar el proyecto desarrollado en el artículo mencionado al comienzo, ya que su modo de creación es equivalente. En cuanto al código fuente del paquete, utilizaremos como punto de partida el existente en dicho proyecto, sobre el que progresivamente iremos realizando las modificaciones necesarias.

Las vistas INFORMATION_SCHEMA.XXX

Si queremos averiguar cuáles son las vistas que el usuario ha creado en una base de datos, tenemos a nuestra disposición la vista del sistema INFORMATION_SCHEMA.VIEWS, que podemos localizar en el nodo "Views | System Views" de SQL Server Management Studio. 

Una consulta sobre esta vista nos revelará, a través de sus campos TABLE_NAME y TABLE_SCHEMA, el nombre y esquema respectivamente de las vistas de usuario de la base de datos a la que estemos conectados. 

De igual manera ocurre para los procedimientos almacenados y funciones, cuyos nombres y esquemas podemos encontrar en los campos ROUTINE_NAME y ROUTINE_SCHEMA de INFORMATION_SCHEMA.ROUTINES, otra vista del sistema, en la que para diferenciar el tipo de objeto emplearemos el campo ROUTINE_TYPE, comprobando que su valor sea "PROCEDURE" o "FUNCTION".

Utilizando las anteriores vistas del sistema crearemos, dentro del código del paquete de nuestro proyecto de integración, un conjunto de colecciones auxiliares, que contendrán, respectivamente, los nombres de las vistas, procedimientos almacenados y funciones de usuario de la base de datos. Posteriormente recorreremos estas colecciones, y por cada uno de sus elementos, accederemos a la colección-elemento equivalente que existe en la clase Microsoft.SqlServer.Management.Smo.Database, para ejecutar su método Script, agregando la sentencia de creación resultante al archivo de script.

Los espacios de nombres System.Data.SqlClient, System.Collections.Specialized y la creación de colecciones auxiliares

Accediendo al editor de código del paquete, comenzaremos a implementar las operaciones que acabamos de describir declarando el espacio de nombres System.Data.SqlClient, mediante el cual tendremos acceso a las clases SqlConnection, SqlCommand y SqlDataReader, necesarias para manipular los objetos de la base de datos con los que deberemos trabajar.

A continuación nos desplazaremos hasta el bucle foreach que recorre la colección Databases del objeto Server, y mediante una conexión (objeto SqlConnection) contra la base de datos en curso, ejecutaremos una consulta a través de un objeto SqlCommand sobre la vista INFORMATION_SCHEMA.VIEWS, que nos devolverá, en forma de objeto SqlDataReader, el conjunto de resultados con los nombres de las vistas y esquemas de la base de datos. Seguidamente recorreremos este SqlDataReader y añadiremos sus valores a un objeto StringCollection (para usar este tipo de colección declararemos el espacio de nombres System.Collections.Specialized). Como resultado, obtendremos una colección con los nombres de las vistas de usuario y esquemas de la base de datos que queremos incluir en el script, evitando de esta manera las vistas del sistema. 

//....
using System.Data.SqlClient;
using System.Collections.Specialized;
//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);


        // conexión
        SqlConnection oConnection = new SqlConnection("Data Source=" + oServer.Name +
            ";Initial Catalog=" + oDatabase.Name + ";Integrated Security=True");

        SqlCommand oCommand = new SqlCommand();
        oCommand.Connection = oConnection;
        oCommand.CommandType = CommandType.Text;

        oConnection.Open();


        // vistas
        oCommand.CommandText = "SELECT TABLE_NAME + ' ' + TABLE_SCHEMA AS Vista " +
            "FROM INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_SCHEMA, TABLE_NAME";

        SqlDataReader oDataReader = oCommand.ExecuteReader();
        StringCollection cllVistas = new StringCollection();

        while (oDataReader.Read())
        {
            cllVistas.Add(oDataReader["Vista"].ToString());
        }

        oDataReader.Close();

        //....

Con respecto a los procedimientos almacenados y funciones procederemos de igual modo, pero empleando en este caso la vista INFORMATION_SCHEMA.ROUTINES para crear las colecciones auxiliares, como vemos en el siguiente bloque de código.

//....
// procedimientos almacenados
oCommand.CommandText = "SELECT ROUTINE_NAME + ' ' + ROUTINE_SCHEMA AS ProcedimientoAlmacenado " +
    "FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' " +
    "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";

oDataReader = oCommand.ExecuteReader();
StringCollection cllProcedimientosAlmacenados = new StringCollection();

while (oDataReader.Read())
{
    cllProcedimientosAlmacenados.Add(oDataReader["ProcedimientoAlmacenado"].ToString());
}

oDataReader.Close();


// funciones
oCommand.CommandText = "SELECT ROUTINE_NAME + ' ' + ROUTINE_SCHEMA AS Funcion " +
    "FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' " +
    "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";

oDataReader = oCommand.ExecuteReader();
StringCollection cllFunciones = new StringCollection();

while (oDataReader.Read())
{
    cllFunciones.Add(oDataReader["Funcion"].ToString());
}
oDataReader.Close();

oConnection.Close();
//....

Creación de las sentencias para el script

Finalizada la creación de las colecciones auxiliares, nuestro siguiente paso consistirá en crear las sentencias de script para la base de datos y tablas; operaciones que no han sufrido modificación con respecto al anterior artículo, ya que en este caso no debemos lidiar con objetos del sistema.

//....
// base de datos
oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
oDatabase.Script(oScriptingOptions);

// tablas
foreach (Table oTable in oDatabase.Tables)
{
    Dts.Events.FireInformation(0, string.Empty,
        "Procesando tabla: " + oDatabase.Name + '-' + oTable.Schema + '.' + oTable.Name,
        string.Empty, 0, ref bOtraVez);
    oTable.Script(oScriptingOptions);
}
//....

A continuación crearemos un bucle foreach para recorrer cada una de las colecciones auxiliares, accediendo, en cada iteración, al elemento de la colección equivalente perteneciente al objeto Database. Para ello, cada cadena (conteniendo los nombres del objeto y esquema, separados por un carácter de espacio) que recuperemos de la colección auxiliar, será asignada, mediante el método String.Split, a un array de tipo string con dos elementos. Como separador para el método Split, utilizaremos un array de tipo char con un carácter de espacio en blanco.

Seguidamente, utilizando los valores del array string, accederemos al elemento de la colección que corresponda en cada caso (Views, StoredProcedures o UserDefinedFunctions) perteneciente al objeto Database, para ejecutar su método Script. Adicionalmente, emplearemos la enumeración ObjetosBD en el índice del array, declarada con ámbito de la clase ScriptMain, para facilitar la lectura del código.  

//....
enum ObjetosBD : int
{
    Nombre,
    Esquema
};

public void Main()
{
//....
    foreach (Database oDatabase in oServer.Databases)
    {
        if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
        {
            //....
            char[] aSeparador = { ' ' };


            // vistas
            string[] aVista;

            foreach (string sVista in cllVistas)
            {
                aVista = sVista.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando vista: " + oDatabase.Name + '-' +
                    aVista[(int)ObjetosBD.Esquema] + '.' + aVista[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);

                ((View)oDatabase.Views[

                     aVista[(int)ObjetosBD.Nombre], 

                     aVista[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }


            // procedimientos almacenados
            string[] aProcedimientoAlmacenado;

            foreach (string sProcedimientoAlmacenado in cllProcedimientosAlmacenados)
            {
                aProcedimientoAlmacenado = sProcedimientoAlmacenado.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando procedimiento almacenado: " + oDatabase.Name + '-' +
                    aProcedimientoAlmacenado[(int)ObjetosBD.Esquema] + '.' + aProcedimientoAlmacenado[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);

                ((StoredProcedure)oDatabase.StoredProcedures[

                     aProcedimientoAlmacenado[(int)ObjetosBD.Nombre], 

                     aProcedimientoAlmacenado[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }


            // funciones
            string[] aFuncion;

            foreach (string sFuncion in cllFunciones)
            {
                aFuncion = sFuncion.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando función: " + oDatabase.Name + '-' +
                    aFuncion[(int)ObjetosBD.Esquema] + '.' + aFuncion[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);

                ((UserDefinedFunction)oDatabase.UserDefinedFunctions[

                     aFuncion[(int)ObjetosBD.Nombre], 

                     aFuncion[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }
//....

Tras añadir el anterior bloque de código al paquete de integración, volveremos a ejecutar éste, comprobando cómo los tiempos de proceso se reducirán notablemente.

Ajustar el tamaño de la base de datos en la sentencia de creación

Si abrimos cualquiera de los archivos de script generados tras la ejecución del paquete, observaremos que la sentencia "CREATE DATABASE" asigna, para los archivos de datos y log, el tamaño actual de la base de datos.

Pero supongamos que algunos de estos archivos de datos y log tienen un tamaño considerable, y necesitamos que al ser creada la base de datos correspondiente mediante la ejecución del script, dicho tamaño inicial no sea elevado.

Por tal motivo, la técnica que proponemos para solucionar este problema consiste en acceder y manipular el contenido del archivo de script mediante los métodos de las clases File y String, modificando el tamaño inicialmente asignado por uno menor. Para ello, pasaremos el contenido del archivo a una variable de tipo string, dentro de la cual, buscaremos las asignaciones de tamaño para la base de datos, reemplazándolas por los nuevos valores. Finalmente, borraremos el archivo original, creando uno nuevo a partir del contenido modificado existente en la variable string. 

//....
Dts.Events.FireInformation(0, string.Empty, "Ajustando tamaño inicial de archivos: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);


string sTextoScript = File.ReadAllText(oScriptingOptions.FileName);

int nPosicionInicio = sTextoScript.IndexOf(", SIZE");
int nPosicionFin = sTextoScript.IndexOf(", MAXSIZE");

sTextoScript = sTextoScript.Replace(sTextoScript.Substring(nPosicionInicio, 

(nPosicionFin - nPosicionInicio)), ", SIZE = 3072KB");


nPosicionInicio = sTextoScript.IndexOf(", SIZE", nPosicionInicio + 6);
nPosicionFin = sTextoScript.IndexOf(", MAXSIZE", nPosicionFin + 6);

sTextoScript = sTextoScript.Replace(sTextoScript.Substring(nPosicionInicio, 

(nPosicionFin - nPosicionInicio)), ", SIZE = 1024KB");


File.Delete(oScriptingOptions.FileName);

File.WriteAllText(oScriptingOptions.FileName, sTextoScript);

Realizadas las anteriores modificaciones sobre nuestro código fuente, damos por concluido este artículo, en el que hemos expuesto una técnica para optimizar el tiempo empleado por un paquete de Integration Services en generar los archivos de script conteniendo las estructuras de las bases de datos de SQL Server. Espero que os sirva de ayuda.

Un saludo.

En la primera entrega de este artículo realizábamos una introducción al desarrollo de proyectos de Integration Services desde el entorno de BIDS, con el objetivo de crear scripts de la estructura de las bases de datos de nuestro servidor utilizando Script Task, un componente de los paquetes de integración. Será en esta segunda parte, cuando nos centraremos en la generación de los mencionados archivos de script.

La clase ScriptingOptions, el método Script y la generación del archivo de script

Pasemos a la siguiente fase en el desarrollo de nuestro proceso (el proyecto de Integration Services con los ejemplos puede descargarse aquí), que consistirá en crear un archivo de script, que de momento solamente incluirá las sentencias de creación de la base de datos. Esta operación será realizada dentro del bucle que recorre la colección de bases de datos del servidor, obteniendo un archivo por cada base de datos. Previamente deberemos crear la carpeta "C:\EstructurasBBDDCopias", que será donde se generen los archivos.

La manera de especificar el archivo de script pasa por utilizar un objeto de la clase ScriptingOptions, que como adivinamos por su nombre, es la encargada de configurar las opciones relativas a la generación del archivo. Una vez instanciado dicho objeto, la propiedad FileName será la que utilicemos para indicar el nombre y ruta del archivo que se va a crear; mientras que el acto de generación lo llevaremos a cabo llamando al método Server.Script, pasándole como parámetro el objeto ScriptingOptions.

Dado que este es un proceso que probablemente ejecutaremos con cierta frecuencia, añadiremos las instrucciones necesarias para crear, dentro de la carpeta "C:\EstructurasBBDDCopias", una subcarpeta cuyo nombre sea la fecha de ejecución del paquete, siendo esta última, la carpeta en donde se depositarán los archivos de script. Para poder utilizar los objetos necesarios para la manipulación de carpetas, añadiremos a nuestro código la declaración del espacio de nombres System.IO. 

using System.IO;
//....
Server oServer = new Server("WINTERFELL-PC");

bool bOtraVez = false;

string sRutaArchivo = @"C:\EstructurasBBDDCopias\" + DateTime.Today.ToString("yyyyMMdd") + @"\";


if (Directory.Exists(sRutaArchivo))
{
    Directory.Delete(sRutaArchivo, true);
}


Directory.CreateDirectory(sRutaArchivo);


ScriptingOptions oScriptingOptions = new ScriptingOptions();


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

Después de ejecutar nuevamente el paquete, en la ruta especificada aparecerán los archivos recién creados, en cuyo interior encontraremos las sentencias de creación de la base de datos. 

CREATE DATABASE [Chinook]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Chinook', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chinook.mdf' , 
SIZE = 5184KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Chinook_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Chinook_log.ldf' , 
SIZE = 1040KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
 COLLATE Modern_Spanish_CI_AS
GO
ALTER DATABASE [Chinook] SET COMPATIBILITY_LEVEL = 110
GO
--....

Con toda seguridad habremos observado que se han generado archivos tanto para las bases de datos de usuario como de sistema, aunque probablemente en estas últimas no estemos interesados. Si este es el caso, podemos evitar su creación interrogando a la propiedad Database.IsSystemObject, de tipo bool, mediante la que averiguaremos si se trata de una base de datos de sistema o usuario.

Con las bases de datos del servidor de informes estamos en una situación parecida, pero como se trata de bases de datos de usuario, si no queremos generar script para ellas lo haremos comprobando el comienzo de su nombre. 

//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
        oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
        oDatabase.Script(oScriptingOptions);
    }
}
//....

Modificación de valores a través de variables

Para especificar el nombre del servidor con el que trabajamos y la ruta en la que se crearán los archivos, actualmente utilizamos valores fijos de cadena, lo cual se convertirá en un inconveniente cuando tengamos que instalar el paquete en un servidor distinto del utilizado para el desarrollo, o bien queramos emplear una ruta diferente para la creación de los scripts.

Podemos remediar este problema gracias a la posibilidad de declarar variables en el diseñador del paquete, cuyos valores pueden ser recuperados desde el código fuente en el momento de su ejecución.

Para declarar una variable nos situaremos en la ventana "Variables" de Visual Studio y haremos clic en el botón "Add Variable" de su barra de herramientas, creándose la nueva variable vacía, cuyos campos de propiedad (Name, Data type y Value) completaremos con los valores oportunos. En la siguiente imagen vemos la creación de la variable RutaArchivos, de tipo cadena, con el valor "C:\EstructurasBBDDCopias\".

En el caso de que esta ventana esté oculta, la mostraremos seleccionando la opción de menú de Visual Studio "SSIS | Variables".

Además de las variables creadas por el desarrollador, disponemos también de un conjunto de variables de sistema, que podemos visualizar haciendo clic en el botón "Show System Variables" de la barra herramientas de esta misma ventana. Para el ejemplo que estamos desarrollando, haremos uso de la variable "MachineName", que nos proporcionará el nombre del servidor en el que se ejecuta el paquete. 

El siguiente paso consistirá en preparar las variables para que puedan estar accesibles desde el código fuente del paquete. Para ello, abriremos la ventana "Script Task Editor" del objeto Script Task, y en la propiedad "ReadOnlyVariables", perteneciente a la categoría "Script", escribiremos los nombres de las variables como una lista separada por comas, o bien haremos clic en el botón de puntos suspensivos, que abrirá la ventana "Select Variables", en donde realizaremos la selección, lo que tendrá el mismo efecto. 

A continuación volveremos a la ventana del editor de código, donde para recuperar el valor de las variables emplearemos la colección "Variables" del objeto Dts, situando en el índice de la colección una cadena con el nombre de la variable a obtener, como vemos en el siguiente bloque de código. 

//....
Server oServer = new Server(Dts.Variables["System::MachineName"].Value.ToString());
bool bOtraVez = false;
string sRutaArchivo = Dts.Variables["User::RutaArchivos"].Value.ToString() + DateTime.Today.ToString("yyyyMMdd") + @"\";
//....

Para pasar valores a las variables del paquete, una vez éste ha sido importado al servidor de Integration Services, podemos utilizar la ventana "Execute Package Utility". En la categoría "Command Line" haremos clic en el RadioButton "Edit the command line manually", añadiendo al final de las opciones del comando DTExec la siguiente opción /SET, con la que cambiaremos la ruta en donde se crearán los scripts. 

/SET "\Package.Variables[User::RutaArchivos].Value";"C:\Pruebas\\" 

Estos mismos valores serán los que usaremos en el caso de ejecutar el paquete desde la línea de comandos en una ventana "Símbolo del sistema" mediante la utilidad DTExec.exe.

Incorporando las tablas al proceso

Los siguientes objetos que vamos a generar en el script serán las tablas, por lo que siguiendo la misma mecánica utilizada con las bases de datos, crearemos un bucle que recorrerá la colección Tables del objeto Database en el que estemos posicionados, añadiendo cada tabla al archivo por medio de su método Script. 

//....
foreach (Database oDatabase in oServer.Databases)
{
    if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
        oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
        oDatabase.Script(oScriptingOptions);


        foreach (Table oTable in oDatabase.Tables)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando tabla: " + oDatabase.Name + '-' + 
                oTable.Schema + '.' + oTable.Name, string.Empty, 0, ref bOtraVez);
            oTable.Script(oScriptingOptions);
        }
    }
}
//....

Al ejecutar el paquete los archivos serán generados de nuevo, pero si abrimos cualquiera de ellos nos llevaremos una desagradable sorpresa, ya que sólo contendrán una única sentencia de creación de tabla. Esto es debido a que, por defecto, el método Script, cada vez que es llamado, sobrescribe el contenido del archivo con la sentencia recién generada.

Igualmente nos encontraremos ante otra serie de inconvenientes como son la ausencia de las sentencias para la creación de los índices de las tablas, claves primarias, externas, etc.

Para evitar este comportamiento recurriremos nuevamente al objeto ScriptingOptions, asignando el valor true a las propiedades que describimos a continuación.

--AppendToFile. Las sentencias generadas mediante el método Script se irán agregando al archivo sobre el que estamos trabajando, evitando así que la última sentencia reemplace su contenido.

--IncludeDatabaseContext. Antes de cada sentencia de creación de tabla se creará una sentencia "USE NombreBBDD", que nos asegure que la tabla sea creada en la base de datos correcta.

--Indexes. Añade las sentencias para la creación de índices (agrupados / no agrupados) y claves primarias en las tablas.

--DriForeignKeys. Añade las sentencias para la creación de las claves externas de las tablas. 

ScriptingOptions oScriptingOptions = new ScriptingOptions();
oScriptingOptions.AppendToFile = true;
oScriptingOptions.IncludeDatabaseContext = true;
oScriptingOptions.Indexes = true;
oScriptingOptions.DriForeignKeys = true;

Dentro del apartado de creación de índices, gracias a las propiedades DriPrimaryKey, ClusteredIndexes y NonClusteredIndexes, pertenecientes a la clase ScriptingOptions, vamos a tener la capacidad de ser más selectivos a la hora de elegir el tipo de índice a incluir en el script. Si por ejemplo, en el script solamente queremos añadir sentencias para crear los índices agrupados y las claves primarias, dejando fuera los índices no agrupados, en primer lugar asignaremos false a la propiedad Indexes, para impedir así la creación de todos los tipos de índices, y después realizaremos la asignación de las propiedades antes mencionadas, para que sean creados los tipos de índices elegidos. 

oScriptingOptions.Indexes = false;
oScriptingOptions.DriPrimaryKey = true;
oScriptingOptions.ClusteredIndexes = true;
oScriptingOptions.NonClusteredIndexes = false;

Vistas, procedimientos almacenados, funciones y objetos del sistema

La inclusión en el proceso de otros objetos de la base de datos tales como vistas, procedimientos almacenados, funciones, etc., no reviste mayor complicación que la de añadir un nuevo bucle foreach, que recorra la colección correspondiente del objeto Database: Views, StoredProcedures, UserDefinedFunctions, etc.

Entre los tipos de objetos que acabamos de mencionar, además de los creados por el usuario, existen versiones del sistema, y adicionalmente, algunos de estos procedimientos almacenados y funciones del sistema se encuentran encriptados (los reconoceremos en SQL Server Management Studio porque su icono muestra un candado), provocando un error si intentamos incluirlos en el script. Es por ello que debemos evitar esta situación, consultando su propiedad IsEncypted, como vemos en el siguiente bloque de código.

//....
if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
{
    //....
    foreach (View oView in oDatabase.Views)
    {
        Dts.Events.FireInformation(0, string.Empty, "Procesando vista: " + oDatabase.Name + '-' + 
           oView.Schema + '.' + oView.Name, string.Empty, 0, ref bOtraVez);
        oView.Script(oScriptingOptions);
        }


    foreach (StoredProcedure oStoredProcedure in oDatabase.StoredProcedures)
    {
        if (!oStoredProcedure.IsEncrypted)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando procedimiento almacenado: " + oDatabase.Name + '-' + 
                oStoredProcedure.Schema + '.' + oStoredProcedure.Name, string.Empty, 0, ref bOtraVez);
            oStoredProcedure.Script(oScriptingOptions);
        }
    }


    foreach (UserDefinedFunction oUserDefinedFunction in oDatabase.UserDefinedFunctions)
    {
        if (!oUserDefinedFunction.IsEncrypted)
        {
            Dts.Events.FireInformation(0, string.Empty, "Procesando función: " + oDatabase.Name + '-' + 
                oUserDefinedFunction.Schema + '.' + oUserDefinedFunction.Name, string.Empty, 0, ref bOtraVez);
            oUserDefinedFunction.Script(oScriptingOptions);
        }
    }
}
//....

Por otra parte, la inclusión de estos objetos del sistema hará que el archivo de script crezca hasta alcanzar un tamaño considerable, por lo que es posible que no deseemos añadirlos.

Si este es nuestro caso, el modo más sencillo de resolver el problema consiste en asignar false a la propiedad ScriptingOptions.AllowSystemObjects. De esta forma tampoco sería necesario comprobar si ciertos objetos están encriptados, obteniendo además un archivo de script con un tamaño más reducido. 

ScriptingOptions oScriptingOptions = new ScriptingOptions();
//....
oScriptingOptions.AllowSystemObjects = false;
//....
foreach (View oView in oDatabase.Views)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando vista: " + oDatabase.Name + '-' + 
        oView.Schema + '.' + oView.Name, string.Empty, 0, ref bOtraVez);
    oView.Script(oScriptingOptions);
}

foreach (StoredProcedure oStoredProcedure in oDatabase.StoredProcedures)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando procedimiento almacenado: " + oDatabase.Name + '-' + 
        oStoredProcedure.Schema + '.' + oStoredProcedure.Name, string.Empty, 0, ref bOtraVez);
    oStoredProcedure.Script(oScriptingOptions);
}


foreach (UserDefinedFunction oUserDefinedFunction in oDatabase.UserDefinedFunctions)
{
    Dts.Events.FireInformation(0, string.Empty, "Procesando función: " + oDatabase.Name + '-' + 
        oUserDefinedFunction.Schema + '.' + oUserDefinedFunction.Name, string.Empty, 0, ref bOtraVez);
    oUserDefinedFunction.Script(oScriptingOptions);
}
//....

Sin embargo, el hecho de no añadir los objetos del sistema al script no va a suponer una mejora significativa en la duración del proceso, ya que a pesar de que el valor de la propiedad AllowSystemObjects sea false, las colecciones Views, StoredProcedures y UserDefinedFunctions siguen conteniendo los objetos del sistema.

Con el fin de agilizar estos tiempos de ejecución, las colecciones que utilizamos para construir el script deberían contener única y exclusivamente aquellos objetos que vayamos a incluir en el archivo. No obstante, esta es una tarea de optimización que queda fuera del ámbito del presente artículo, pero que abordaremos próximamente. Por el momento, confiamos en que la solución aquí propuesta  resulte de utilidad para todos aquellos lectores que precisen crear scripts con las estructuras de sus bases de datos.

Un saludo.

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 programa\Microsoft SQL Server\MSSQL\Upgrade\scptxfr' + '" /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".  

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. 

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". 

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.  

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

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.

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.

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.

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. 

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. 

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. 

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:\pruebasSSIS\EstructurasBBDD\EstructurasBBDD; 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. 

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.  

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

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". 

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. 

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. Mientras tanto, el lector que quiera experimentar con el proyecto de integración desarrollado a lo largo del artículo, puede descargarlo aquí.

Saludos.