Desplegar datos con Visual Studio 2010 y BULK INSERT

Article
10/26/2011

Visual Studio 2010 permite crear proyectos de base de datos que ayudan a crear y gestionar los elementos de una base de datos. Estos proyectos permiten realizar el despliegue de la base de datos que en la que estamos trabajando a un determinado servidor de SQL Server. El despliegue de una base de datos lo podemos invocar desde el IDE de Visual Studio (botón derecho sobre el proyecto y “Deploy”) o utilizando MSBuild para invocar el target “Deploy”.Es muy común que estos despliegues sean parte de un proceso de Integración Continua en el cuál ejecutemos una batería de pruebas las cuales requieren ciertos datos (datos maestros y datos para un contexto determinado).

En los proyectos de base de datos se pueden utilizar los script “Post-Deployment” para insertar datos maestros que queremos estén disponibles en la base de datos desde el primer instante de su creación.

Durante el despliegue, el archivo “Script.PostDeployment.sql” se ejecuta después de que se haya creado la base de datos, si incluimos comandos SQL para insertar datos en determinadas tablas, conseguiremos tener nuestros “datos maestros” disponibles para posteriores usos.

En el caso de que los datos provengan de una base de datos que ya está en explotación o tengamos muchas tablas en las que queremos información (por ejemplo datos para crear un determinado contexto de pruebas), es muy probable que el volumen de información a manejar sea grande, con lo que el tamaño de los scripts de inserción de datos aumenta, de manera desmesurada me atrevería a decir, y el tiempo que tarda en ejecutarse la inserción se dilata. En este contexto, podemos llegar a una situación inmanejable: los archivos grandes ralentizan las operaciones con el control de código fuente y demora en la capacidad de re-crear la base de datos, puede repercutir en la agilidad para ejecutar determinados test, por ejemplo.

Para mejorar la eficiencia del despliegue podemos considerar el uso de la utilidad “bcp” para exportar datos de manera masiva. En nuestro ejemplo utilizaremos “bcp” para extraer los datos de una tabla para utilizarlos durante el despliegue. En un contexto real, el origen puede ser la base de datos que está en producción o una base de datos que ya tiene el contexto concreto de datos para realizar las pruebas.

“Bcp” es una una herramienta muy completa y dispone de muchas opciones, pero en general necesitaremos extraer de cada tabla su definición de formato (definición de la tabla y tipos de datos de cada columna) y los datos propiamente dichos.

 bcp BulkInsertSample.dbo.SampleTable format nul -n -x -f SampleTable.xml -T -S(local)\SqlExpress
bcp BulkInsertSample.dbo.SampleTable out SampleTable.dat -n -T -S(local)\SqlExpress

Con los comandos previos (a ejecutar desde una consola de comandos), se genera el archivo “SampleTable.xml” con el formato de la tabla y el archivo “SampleTable.dat” con los datos que contiene.

Incluimos dichos archivos en el proyecto de base de datos de Visual Studio con la intención de que estén disponibles cuando se ejecute el despliegue de la base de datos.

Después modificaremos el archivo “Script.PostDeployment.sql” para invocar la sentencia “BULK INSERT” de T-SQL utilizando los archivos generados.

En este punto, se nos debe plantear la siguiente cuestión: ¿cómo indicamos la ruta de los archivos al comando “BULK INSERT”?

La primera aproximación que se nos puede ocurrir es que añadiendo los archivos como contenido al compilar, el despliegue los encontrará utilizando una ruta relativa, de manera que la instrucción “BULK INSERT” quedaría como sigue

 PRINT 'INSERTANDO DATOS DE LA TABLA SampleTable'
BULK INSERT dbo.SampleTable FROM 'Bcps\SampleTable.Dat' WITH (DATAFILETYPE='native', FORMATFILE='Bcps\SampleTable.xml'); 
GO

Pero al ejecutar el deployment…

Obtenemos la siguiente salida (en la ventana “Output” de Visual Studio):

------ Build started: Project: BulkInsertDeployment, Configuration: Debug Any CPU ------
… ------ Deploy started: Project: BulkInsertDeployment, Configuration: Debug Any CPU ------ Deployment script generated to: C:\Incubator\BulkInsertDeployment\BulkInsertDeployment\sql\debug\BulkInsertDeployment.sql

INSERTANDO DATOS DE LA TABLA SampleTable C:\Incubator\BulkInsertDeployment\BulkInsertDeployment\sql\debug\BulkInsertDeployment.sql(68,0):
Error SQL01268: .Net SqlClient Data Provider: Msg 4861, Level 16, State 1, Line 14 Cannot bulk load because the file "Bcps\SampleTable.Dat" could not be opened. Operating system error code 3(failed to retrieve text for this error. Reason: 15105).

… ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== ========== Deploy: 0 succeeded, 1 failed, 0 skipped

Obtenemos un error al ejecutar la instrucción “Bulk Insert” debido a que la ruta donde se ejecuta el script de Sql Server durante el deployment no se corresponde con el directorio donde se ha generado el script de la base de datos. En otras palabras, no podemos utilizar una ruta relativa.

Para solventar este inconveniente, tendremos que hacer uso de dos características muy potentes que están disponibles en los proyectos de base de datos de Visual Studio:

1. El archivo de variables de SQLCMD:

Añadiendo una variable que va a almacenar la ruta donde están los “bcps”, en este ejemplo la llamaremos “ $(BcpsPath) ”

2. Un poco de “magia”, o lo que es lo mismo, una tarea de MSBuild para sobre escribir el valor de la variable SQLCMD cuando se realice el despliegue. Para añadir esta “magia”:

Descargamos el proyecto:

Editamos el archivo de proyecto proyecto

Al final del contenido, antes del tag de cierre de proyecto, insertamos el Target de MSBuild “BeforeDeploy” que va a encargarse de sobre escribir el valor de la variable “BcpsPath” antes de que se ejecute el despliegue, utilizando el item SqlCommandVariableOverride:

 <Target Name="BeforeDeploy">
  <ConvertToAbsolutePath Paths="$(ProjectDir)">
    <Output TaskParameter="AbsolutePaths" PropertyName="AbsolutePath" />
  </ConvertToAbsolutePath>
  <CreateItem Include="BcpsPath=$(AbsolutePath)Bcps\">
    <Output TaskParameter="Include" ItemName="SqlCommandVariableOverride" />
  </CreateItem>
</Target>

Recargamos el proyecto y modificamos el archivo “Script.Postdeployment.sql” para utilizar la variable de SQLCMD “$(BcpsPath)”:

 PRINT 'INSERTANDO DATOS DE LA TABLA SampleTable'
BULK INSERT dbo.SampleTable FROM '$(BcpsPath)SampleTable.Dat' WITH (DATAFILETYPE='native', FORMATFILE='$(BcpsPath)SampleTable.xml'); 
GO

Volvemos a ejecutar el despliegue, esta vez con una salida satisfactoria y con nuestros datos en la base de datos!

------ Build started: Project: BulkInsertDeployment, Configuration: Debug Any CPU ------
…

------ Deploy started: Project: BulkInsertDeployment, Configuration: Debug Any CPU ------ Deployment script generated to: C:\Incubator\BulkInsertDeployment\BulkInsertDeployment\sql\debug\BulkInsertDeployment.sql

INSERTANDO DATOS DE LA TABLA SampleTable : (50 row(s) affected)

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== Deploy: 1 succeeded, 0 failed, 0 skipped

Espero que este ejemplo os sirva en futuros despliegues, no solo por cómo llevar datos con “Bulk Insert” a vuestras bases de datos, si no que también podáis utilizar la “sobre-escritura” de variables SQLCMD para otros cometidos en vuestros proyectos de base de datos.

Desplegar datos con Visual Studio 2010 y BULK INSERT

Additional resources