09 Manual SSIS – Gestión de errores y depuración en SSIS I

Tarde o temprano tendremos que echar mano de las opciones de depuración y gestión de errores que nos proporciona SSIS. Normalmente todo es mucho más sencillo cuando no hay problemas, pero en caso de errores conocer el funcionamiento de estas herramientas nos hará la vida un poco más fácil.

Podemos agrupar estas herramientas como:

  1. Gestión del control de flujo en los DataFlows.
  2. Visualización de “los datos” en un punto determinado de nuestro DataFlow.
  3. Uso de “Event Handlers” de SSIS.
  4. Seguimiento y adicción de logs o trazas de las ejecuciones de un determinado SSIS.

En este post, veremos el primer caso.

Gestión del control de flujo en los DataFlows

Cualquier componente de SSIS que colocamos en nuestros SSIS tiene al menos dos “caminos” posibles durante su ejecución: correcta e incorrecta. SSIS representa esto mediante dos “flechas” que nos servirán de conectores para el siguiente paso del SSIS.

DataFlow Paths

Por ejemplo en este caso mientras que la lectura de los datos en “Ole DB Source” sea correcta, se ejecutará la transformación Sort. En caso contrario el SSIS fallará y no realizará nada.

Esto se puede cambiar si por ejemplo, añadimos un componente “Flat File Destination” y arrastramos la “flecha roja” sobre este nuevo elemento.

Configure error Path

Al conectar estos dos elementos nos aparece una pantalla de propiedades donde se podrá configurar qué queremos que haga SSIS en caso de producirse un error en este punto.

Configure Error Output

SSIS a nivel de cada campo contempla las posibilidades de “Error” y de “Truncation” y en este cuadro de diálogo le diremos qué debe hacer:

  • Fail component: es decir, que pare la ejecución.
  • Ignore failure: que omita el fallo y prosiga con la ejecución del SSIS:
  • Redirect Row: que redirija la fila al destino que le indicamos (en este caso, un fichero de texto).

Para nuestro caso seleccionaremos en todos los campos la opción “Redirect row” para que en caso una fila tenga en un campo un valor erróneo o que se produzca un truncamiento de datos, nos guarde ese fila o registro en un fichero de texto.

Ahora sólo nos falta especificar el formato del fichero de logs. Para hacemos doble clic sobre el componente “Flat File Destination”.

Flat file configuration Manager

Haremos clic en “New”

Flat File Format

Indicaremos el formato del fichero de texto (en nuestro caso “Delimitied”).

A continuación seleccionaremos la ruta de destino, la codificación, así como los campos que queremos que tenga el fichero. Para ello nos moveremos por cada una de las opciones disponibles.

Flat File Connection Manager Editor

Una vez finalizado, aceptaremos y revisaremos en “Mappings” que la asignación de los campos sea correcta en el momento que se guarde una determinada fila en el fichero. Además en caso de que no queramos guardar un determinado campo en el fichero podemos ignorarlo.

Flat File Mappings

Con esto ya hemos finalizado. Espero que os sea de utilidad!

08 Manual SSIS – Transformación Sort

Usaremos la transformación “Sort”  para realizar la ordenación de los registros leídos en un DataFlow por uno o varios campos de forma simultánea. Además este componente nos da una serie de opciones para realizar una ordenación muy específica que puede resultar muy útil en determinados casos.

Veamos su funcionamiento con un ejemplo muy sencillo. Para ello crearemos un nuevo proyecto, añadimos un nuevo DataFlow con dos elementos: un componente “Ole DB Source” y una transformación “Sort”. Conectamos el origen de datos con nuestro componente “Sort” con el conector verde (es decir, en caso de que nuestra lectura de datos haya finalizado correctamente).

Transformacion Sort

Para configurar la transformación “Sort” debemos hacer doble clic sobre la misma. Esto nos abrirá la pantalla de propiedad de la misma.

Transformation Sort Properties

En la parte superior donde se muestra el listado de campos debemos seleccionar todos los necesarios para realizar la ordenación deseada. En la parte inferior podremos especificar cómo queremos que sea la ordenación de datos.

  • Input Column: es el nombre de entrada de la columna.
  • Output Alias: es el nombre de la columna tras la ordenación.
  • Sort Type: el tipo de ordenación (ascendente o descendente).
  • Sort Order: el orden del campo dentro de la ordenación (primero por, después por, etc.).
  • Comparison Flags: con el que podemos indicar modificadores para personalizar un poco más la ordenación de los datos.

Además disponemos de la opción “Remove rows with duplicate sort values” que eliminará filas con valores duplicados en el momento de realizar la ordenación.

Transformation Sort

Espero que haya quedado claro todo!

Prestashop – Permitir configurar el número de categorías en módulo “blockcategories”

En ocasiones el número de categorías en un portal en Prestashop es demasiado grande y no deseamos mostrarlas todas directamente como se hace por defecto. La idea sería mostrar por defecto un número determinado de categorías, y añadir un enlace “Mostrar Todas” que muestre todas las categorías. Por ejemplo,

Imagen

Esto se puede conseguir modificando el módulo “blockcategories” que viene incluido en Prestashop.

Este módulo tiene la siguiente estructura:

blockcategories structure

El fichero “blockcategories.php” tiene el código necesario para el funcionamiento del módulo, así como para su instalación, desinstalación y la gestión de sus “hooks”.

El fichero “blockcategories.tpl” es la plantilla de Smarty para la visualización del módulo de categorías.

El fichero “category-tree-branch.tpl” contiene el código Smarty para la viualización de cada elemento del menú.

Los parámetros de UI que muestra el módulo “blockcategories” por defecto este módulo son:

blockcategories default configuration

Pero nosotros vamos a añadir un nuevo elemento que nos permita en indicar el número de elementos a mostrar por defecto, quedando:

blockcategories new configuration

Para añadir estos nuevos elementos a la UI tenemos que modificar el fichero “blockcategories.php” y buscaremos el método “displayForm”. Antes de la línea que pone:

<center><input type="submit" name="submitBlockCategories" value="'.$this->l('Save').'" /></center>

Añadimos el siguiente código:

<label>N&uacute;mero de elementos a mostrar</label>
 <div class="margin-form">
 <input type="text" name="nbrShowItems" value="'.(int)Configuration::get('BLOCK_CATEG_ITEMS_SHOW').'" />
 <p class="clear">Indique el número de categor&iacute;as a mostrar. -1 para todas.</p>
 </div>

Donde básicamente añadimos las etiquetas de texto a mostrar, un textbox para que el usuario pueda configurar el número de elementos a mostrar que se guardará en una nueva entrada de configuración en Prestashop (BLOCK_CATEG_ITEMS_SHOW).

Para poder emplear esa nueva entrada, tendremos que registrarla en la instalación del módulo. Por ello buscaremos el método “Install” y pondremos el siguiente código:

	public function install()
	{
		if (!parent::install() ||
			!$this->registerHook('leftColumn') ||
			!$this->registerHook('footer') ||
			!$this->registerHook('header') ||
			// Temporary hooks. Do NOT hook any module on it. Some CRUD hook will replace them as soon as possible.
			!$this->registerHook('categoryAddition') ||
			!$this->registerHook('categoryUpdate') ||
			!$this->registerHook('categoryDeletion') ||
			!$this->registerHook('actionAdminMetaControllerUpdate_optionsBefore') ||
			!$this->registerHook('actionAdminLanguagesControllerStatusBefore') ||
			!Configuration::updateValue('BLOCK_CATEG_MAX_DEPTH', 4) ||
			!Configuration::updateValue('BLOCK_CATEG_DHTML', 1) ||
            !Configuration::updateValue('BLOCK_CATEG_ITEMS_SHOW', -1)) //Añadimos nuestra nueva entrada
			return false;
		return true;
	}

De la misma forma, eliminaremos esta entrada en el método “Uninstall” del módulo:

	public function uninstall()
	{
		if (!parent::uninstall() ||
			!Configuration::deleteByName('BLOCK_CATEG_MAX_DEPTH') ||
			!Configuration::deleteByName('BLOCK_CATEG_DHTML') ||
            !Configuration::deleteByName('BLOCK_CATEG_ITEMS_SHOW')) //Eliminamos nuestra entrada
			return false;
		return true;
	}

Para realizar el registro en BD de esta variable, lo más rápido es desinstalar y volver a instalar este módulo.

En este fichero sólo nos falta añadir el código para que se guarden los cambios del usuario una vez realice cambios en esta nueva entrada de configuración. Esto lo haremos en el método “getContent” quedando así el código:

	public function getContent()
	{
		$output = '<h2>'.$this->displayName.'</h2>';
		if (Tools::isSubmit('submitBlockCategories'))
		{
			$maxDepth = (int)(Tools::getValue('maxDepth'));
			$dhtml = Tools::getValue('dhtml');
			$nbrColumns = Tools::getValue('nbrColumns', 4);
			if ($maxDepth < 0)
				$output .= '<div class="alert error">'.$this->l('Maximum depth: Invalid number.').'</div>';
			elseif ($dhtml != 0 && $dhtml != 1)
				$output .= '<div class="alert error">'.$this->l('Dynamic HTML: Invalid choice.').'</div>';
			else
			{
				Configuration::updateValue('BLOCK_CATEG_MAX_DEPTH', (int)($maxDepth));
				Configuration::updateValue('BLOCK_CATEG_DHTML', (int)($dhtml));
				Configuration::updateValue('BLOCK_CATEG_NBR_COLUMN_FOOTER', $nbrColumns);
				Configuration::updateValue('BLOCK_CATEG_SORT_WAY', Tools::getValue('BLOCK_CATEG_SORT_WAY'));
				Configuration::updateValue('BLOCK_CATEG_SORT', Tools::getValue('BLOCK_CATEG_SORT'));
//Guardamos este valor en BD
                Configuration::updateValue('BLOCK_CATEG_ITEMS_SHOW',  Tools::getValue('nbrShowItems', -1));

				$this->_clearBlockcategoriesCache();
				$output .= '<div class="conf confirm">'.$this->l('Settings updated').'</div>';
			}
		}
		return $output.$this->displayForm();
	}

Por último, nos falta enviar el valor configurado a la plantilla Smarty del módulo. Esto lo haremos en el método “hookLeftColumn”. Tendremos que añadir la siguiente línea:

$this->smarty->assign('itemsShow', Configuration::get('BLOCK_CATEG_ITEMS_SHOW'));

Justo después de:

$this->smarty->assign('isDhtml', $isDhtml);

Ahora sólo nos falta, cambiar la visualización de las categorías en las plantillas de Smarty. Para ello vamos la fichero “blockcategories.tpl” y añadiremos el siguiente código:

            //Ocultamos las categorías configuradas en el módulo.
            {if $itemsShow > -1}
                $('ul#menuCategorias')
                        .find('li:gt({$itemsShow})')
                        .hide()
                        .end()
                        .append(
                        $('<li><a href="#"><b>[MOSTRAR TODAS]</b></a></li>').click( function(){
                            $(this).siblings(':hidden').show().end().remove();
                        })
                );

Justo después de:

			// we hide the tree only if JavaScript is activated
			$('div#categories_block_left ul.dhtml').hide();

Descarga el código completo en GitHub.

07 Manual SSIS – Publicación de paquetes SSIS

De cara al despliegue de nuestros paquetes SSIS en un entorno de producción normalmente realizaremos su publicación desde Visual Studio. El proceso de publicación es realmente sencillo.

Simplemente con nuestro paquete abierto iremos al menú “File” de Visual Studio y seleccionaremos la opción “Save a copy of…”.

Save a copy of

En nuestro caso seleccionaremos la opción SQL Server en “Package Location” indicando el nuestro de nuestro servidor SQL Server.

En la opción “Package Path” seleccionaremos la carpeta de destino en nuestro servidor. Si deseamos crear una nueva carpeta para organizar mejor nuestros paquetes no tendremos más remedio que conectarnos a nuestro servidor SSIS con SQL Server Management Studio.

SSIS Server

Una vez creada la seleccionaremos desde Visual Studio especificando el nombre del SSIS en el entorno de producción.

SSIS destination Folder

Por último en “Protection Level” podremos configurar cómo queremos cifrar nuestras cadenas de conexión en caso de que contengan información sensible.  Podéis acceder a la documentación completa de estas posibilidades en MSDN.

06 Manual SSIS – Programación ejecución de SSIS mediante Jobs

Una tarea bastante frecuente a la hora de ejecutar SSIS es automatizar su ejecución mediante Jobs en SQL Server. Para esta cuestión necesitamos tener una serie de conceptos claros:

  1. Para ejecutar Jobs necesitamos los servicios de SQL Server Agent que no están incluidos en las versiones Express de SQL Server.
  2. Para ejecutar paquetes SSIS en Jobs necesitamos tener el servicio de Integration Services que tampoco viene incluido en las versiones Express.

Por tanto para estas tareas necesitaremos adquirir alguna licencia de SQL Server. Se puede consultar la lista completa de características de las diferentes versiones en MSDN.

Una vez realizadas estas aclaraciones, veamos cómo crear Jobs en SQL Server.

Para las programaciones de tareas SQL Server usa SQL Server Agent por tanto debemos asegurarnos que lo tengamos iniciado. La administración del mismo se realizará desde SQL Server Management Studio.

SQL Server Agent

Para crear un Job haremos clic con el botón derecho sobre la carpeta Jobs y seleccionaremos “New Job…”.

SQL Server New Job

En el panel izquierdo del cuadro de diálogo “New Job” disponemos de todas las opciones necesarias para generar un Job. En “General” le asignaremos el nombre y una categoría. Además, en caso de crear varios Job puede ser especialmente útil cubrir una descripción para que nos sirva de recordatorio en el futuro.

En “Steps” crearemos cada uno de los pasos (tareas) que debe realizar el Job.

Job Steps

Le daremos un nombre a la tarea o paso del Job y en “Type” especificaremos el tipo de tarea que tiene que ejecutar el Job. Con los tipos disponibles, realmente podremos realizar casi cualquier tipo de tarea. En nuestro caso, seleccionaremos “SQL Server Integration Services Package”. En “Package Source” le indicaremos al Job dónde tiene que ir a buscarlo. En este ejemplo, seleccionaremos “File System” dado que no hemos publicado el paquete en el servidor de SQL Server Integration Services.

Dentro de las propiedades de la ejecución del paquete SSIS, cabe destacar en este momento la pestaña “Data Sources” que nos permite cambiar las cadenas de conexión de cada uno de los Data Sources incluidos en el paquete.

Job Data Source Configuration

Con la opción “Advanced” indicaremos el flujo que debe realizar el job en caso de que la ejecución sea correcta o incorrecta.

Job Step Advanced

Una vez creado el “Step” con la opción “Schedules” crearemos la programación del Job. Para ello SQL Server Agent nos da multitud de opción de programación permitiendo que sea diaria, en intervalos de horas, determinados días de la semana, etc.

Job Scheduled

Con estos pasos, ya tenemos nuestro Job creado y listo para ejecutar.

Para realizar el seguimiento de la ejecución de un Job podemos realizar clic con el botón derecho sobre un determinado Job y hacer clic en “View History”.

Job History

05 Manual SSIS – Ejecutar consultas SQL

En algunas ocasiones necesitaremos ejecutar alguna consulta SQL dentro de nuestro paquete SSIS. Para realizar esta tarea BIDS pone a nuestra disposición el componente “Execute SQL Task”.

Este componente podremos utilizarlo dentro de la pestaña “Control Flow” donde se define el flujo general de nuestra ETL.

Execute SQL Task

Una vez añadido este componente hacemos doble clic sobre él y podremos asignarle valores a sus propiedades. Las dos propiedades más básicas son la conexión y la sentencia a ejecutar.

Si no tenemos creada ninguna conexión, podremos añadirla en la sección “Connection Managers” pulsando con el botón derecho en un área vacía.

Connection Managers

Una vez creada, daremos valor a las propiedades “Connection” y “SQL  Statement”.

Execute SQL Task 2

Este componente nos dará mucho más juego pudiendo realizar sentencias SQL dinámicas mediante el uso de variables dentro de los paquetes SSIS, pero para ello le dedicaremos un post completo porque da mucho para hablar.

04 Manual SSIS – Conversión entre tipos de datos

Una transformación habitual en los paquetes SSIS es la conversión entre tipos de datos. SSIS dispone de un componente específico para estas cuestiones y se trata del objeto “Data Conversión”. Para emplear este componente simplemente tendremos que agregarlo dentro de nuestro Data Flow en un punto determinado. Por ejemplo,

Data Conversion

Al hacer doble clic sobre el mismo aparecerán todos los campos de entrada disponibles hasta el momento.

Para realizar la conversión de tipos de datos simplemente tendremos que seleccionar cada uno de los campos que queremos convertir, indicar el tipo de dato deseado y asignar un nuevo nombre para la columna. En este caso vamos a convertir la columna “Title” de la tabla Person de AdventureWorks de un texto Unicode a no Unicode. Esta conversión quedaría como:

Data Conversion

Como se puede ver la columna “Data Type” contiene numerosas opciones de conversión de tipos de datos.

Otra opción disponible para realizar conversiones entre tipos de datos es el componente “Derived Column”. Esta opción es un poco más laboriosa pero igual de efectiva. El sistema de conversión de tipos de datos es un “cast” inspirado en lenguajes basados en C. El formato sería:

Destino = (NUEVO_TIPO_DE_DATO) Campo_Origen

Por ejemplo: (DT_UI8) Campo_Numerico.

En este caso haremos la conversión de un campo de tipo “Unsigned Int” de 8 bytes.

Data Conversion con Derived Column

Espero que se haya entendido todo!