Cómo duplicar un entorno del Catálogo de Integration Services empleando las herramientas que el propio Catálogo nos proporciona
Introducción
Los que trabajáis con SSIS ya sabréis que la aparición del Catálogo de Integration Services ha supuesto una gran ayuda a la hora de configurar proyectos. Los entornos nos permiten definir conjuntos de variables cuyos valores se pasarán a las diferentes propiedades de un proyecto en tiempo de ejecución. De esta forma, podemos cambiar el comportamiento de un proyecto con tan sólo cambiar los valores de estas variables sin volver a cambiar, generar ni desplegar el proyecto.
Desde el Catálogo de Integration Services podemos crear, modificar y mover un entorno pero no hay una opción para copiar entornos en el Catálogo de SSIS.
Motivos para copiar entornos en el Catálogo de SSIS
Bajo mi punto de vista, es más frecuente usar un mismo entorno en dos proyectos de la misma organización que cambiar un proyecto de carpeta y haber de mover el entorno que lo configuraba a esa nueva carpeta. Por esa razón, la posibilidad de copiar entornos en el Catálogo de SSIS resultaría muy útil a los desarrolladores o administradores de BI.
Dicho esto, el hecho de que existiese la opción de mover entornos me hizo pensar que, tal vez, investigando la manera en que eso se llevaba a cabo, podría llegar a copiarlos por mis propios medios… y me puse a fisgar en las interioridades del Catálogo de Integration Services.
Fisgando en las interioridades del Catálogo de SSIS
El Catálogo de Integration Services no es más que un interfaz que nos permite gestionar una base de datos que se crea cuando lo activamos. Su nombre por defecto es SSISDB y, a diferencia de otras BBDD de sistema o de aplicaciones de terceros, es bastante comprensible. Tiene 32 tablas, 35 vistas y 115 procedimientos almacenados todos ellos con nombres bastante explicativos de qué contienen o qué hacen.
Si repasamos los objetos que he dicho, veremos un procedimiento almacenado llamado [catalog].[move_environment] que, a juzgar por su nombre, cualquiera diría que mueve entornos.
Si lo abrimos y estudiamos un poco su código veremos que hace lo siguiente:
- Toma 3 parámetros de entrada; la carpeta de origen, el nombre del entorno y la carpeta de destino
- Verifica que las credenciales con las que se ejecuta el procedimiento tienen los permisos necesarios
- Verifica que los parámetros son correctos, es decir, que las carpetas y el entorno existen
- Modifica la tabla [internal].[environments] sustituyendo en la columna [folder_id] el id de la carpeta de origen por el de la de destino
- Controla las transacciones para confirmarlas o revertirlas
Si no las habíamos visto antes, descubrimos aquí dos tablas interesantes; [internal].[environments] y [internal].[folders]
Vemos que están relacionadas. Si creamos un diagrama con esas dos tablas y sus tablas relacionadas, aparecerán otras dos [internal].[environment_variables] e [internal].[environment_permissions]
La primera almacena las variables que contiene cada entorno y la segunda los permisos que los usuarios tienen sobre los entornos.
Si de copiar entornos en el Catálogo de SSIS se trata necesitaremos copiar no sólo el entorno sino sus variables. Ahora ya sabemos dónde se guardan los datos de los entornos, las variables y las carpetas.
Cómo crea el Catálogo de SSIS los entornos y las variables
Llegados a este punto podemos optar por escribir nosotros el código que lea los metadatos de un entorno y sus variables para luego escribirlo duplicado o reutilizar (fusilar) el código de otros para copiar entornos en el Catálogo de SSIS. ¿Y qué mejor código para reutilizar que el propio del Catálogo de Integration Services?
Si volvemos a repasar los objetos del Catálogo descubriremos dos procedimientos llamados [catalog].[create_environment] y [catalog].[create_environment_variable]. El primero crea un entorno y el segundo una variable; más claro, agua.
No sólo eso sino que ambos verifican que las credenciales con las que se ejecuta el procedimiento tengan los permisos necesarios, que los parámetros son correctos y controlan las transacciones para confirmarlas o revertirlas.
¿Vamos a despreciar todo ese trabajo hecho para nosotros? Obviamente, no.
Escribiendo un procedimiento para copiar entornos en el Catálogo de SSIS
Nuestro procedimiento deberá recibir el nombre del entorno a duplicar, la carpeta donde se halla y el nombre del nuevo entorno así como la carpeta a donde lo queremos copiar. También deberá leer los metadatos de las variables que contiene.
Con esta información, podemos llamar al procedimiento [catalog].[create_environment] para crear un nuevo entorno en la carpeta que indiquemos y luego al procedimiento [catalog].[create_environment_variable] tantas veces como variables hayamos leído para irlas creando en el nuevo entorno…. y listo… Pues no.
Lo anterior funciona sólo cuando el entorno original no tiene variables cuyo valor sea confidencial. Cuando hay variables cuyo valor es confidencial, un procedimiento como el descrito creará el nuevo entorno pero fallará a la hora de copiar las variables. Vamos a ver por qué.
Cómo protege el Catálogo de SSIS los valores confidenciales
Si creamos una variable en un entorno, marcamos la casilla Confidencial y a continuación abrimos la tabla [internal].[environment_variables] veremos lo siguiente, el registro de dicha variable contiene las columnas [sensitive], [value] y [sensitive_ value] con los valores 1, NULL y una ristra binaria respectivamente. También vemos la columna [base_data_type] donde se guarda el tipo de datos primario de la variable.
La culpable de que la creación de variables falle cuando el valor es confidencial es la columna [value] que, en las variables no confidenciales, guarda el valor de la variable tal cual. Sin embargo, en las confidenciales, este valor se encripta, se guarda ya encriptado en la columna [sensitive_ value] y el valor de la columna [value] se deja a NULL.
Si revisamos el código del procedimiento [catalog].[create_environment] veremos que, al crearse un entorno, se generan un certificado para ese entorno con un nombre de esta forma: MS_Cert_Env_###
Donde ### es el id del entorno. También se crea una clave simétrica con un nombre análogo: MS_Enckey_Env_###.
Esta clave simétrica es la que se emplea para encriptar los valores confidenciales.
Por otra parte, el procedimiento [catalog].[create_environment_variable] tiene un parámetro de entrada llamado @value donde espera recibir el valor de la variable que ha de crear. Revisando su código, podemos ver que, si este parámetro recibe un valor NULL, el procedimiento dispara un error pero si no es el caso y el parámetro @sensitive es TRUE, encriptará el valor y lo guardará encriptado dejando la columna [value] a NULL.
Dado que cada entorno tiene su propia clave para cifrar y descifrar, no podemos limitarnos a copiar el valor encriptado tras crear la variable con un valor provisional ni pasar el valor encriptado ya que se reencriptaría.
El valor está encriptado, quién lo desencriptará…?
Bien, ya habréis deducido que, tras leer la lista de variables y antes de cada llamada al procedimiento [catalog].[create_environment_variable] hemos de comprobar si la columna [value] de la variable está a NULL y, en ese caso, desencriptar el valor con la clave del entorno de origen para pasarlo desencriptado al procedimiento que se encargará de cifrarlo con la nueva clave.
¿Fácil, no? Pues tampoco. Si intentamos hacerlo con un CASE o similar aprovechando que la columna [value] y el parámetro @value son de tipo sql_variant, para desencriptar todas las variantes de una vez no funcionará. Habremos de convertir cada valor por separado.
Para ello, ya que conocemos el tipo de datos del valor original, lo desencriptaremos y convertiremos el resultado a ese tipo.
El procedimiento final con el que copiar entornos en el Catálogo de SSIS
A continuación incluyo mi procedimiento con comentarios aclaratorios. Dado que se limita a copiar sin borrar ni modificar lo ya existente, usarlo no debería ser ningún riesgo.
En todo caso, probadlo antes en un entorno de testing.
Si queréis más información sobre Integration Services o Business Intelligence en general, no olvidéis pasar por nuestra web para ver nuestra oferta de formación y consultoría.
Saludos y hasta otra.