martes, 5 de noviembre de 2013

SSIS Básico: Bucles ForEach para ejecutar paquetes en FileSystem

Orchestrator1

 

Es bastante común que entre los paquetes ETL que generamos para realizar una serie de procesos diseñemos un ‘Orquestador’, esto es un paquete en el que a través de tareas Ejecutar Paquete (Execute package task) establezcamos un orden de ejecución pudiendo asignar lógica variables, etc… El termino orquestador define bastante bien el propósito de este tipo de paquetes. Imaginaros un director de orquesta que da pie a cada grupo de instrumentos acompasando los ritmos, adecuando intensidad, etc… En SSIS los grupos de instrumentos serían los distintos paquetes ETL a ejecutar y el director el paquete "Orquestador”.

Es habitual como digo, pero siempre hay una primera vez para todos así que esta entrada esta dedicada a los usuarios del foro de SSIS en castellano

Que vamos a hacer?

Resolver la siguiente cuestión de Pachecora:

“Buenas tardes tengo la siguiente consulta estoy usando SQL Server 2008 R2 y lo que necesito es poder ejecutar varios package que estarán en una carpeta por medio de un Job lo que hice fue crear varios Steps para este Job que apuntan a determinado package, pero lo que necesito ahora es ejecutar todos los package dentro de una carpeta. Sera posible hacerlo?”

Antes de empezar…

Entre los distintos componentes nativos que nos ofrece Integration Services desdForEach loop containere la versión 2005 se encuentran los bucles ForEach que son de gran utilidad para realizar iteraciones utilizando distintos enumeradores: Sistema de archivos, un dataset ADO, etc.. Para ser más claro, recorre una colección y en cada vuelta devuelve el elemento actual (algo así como un cursor en TSQL, por buscar un símil)

Escenario

Vamos a desarrollar un proyecto de SSDT2012 con plantilla de Integration Services, aunque se podría realizar con la versión BIDS 2005 y posteriores sin ningún tipo de problema.

Este proyecto consta de un solo paquete: el orquestador. Los paquetes que va a ejecutar este orquestador están desplegados en una carpeta del sistema de archivos. Realmente no nos interesa que hacen esos paquetes (en mi ejemplo están vacíos), nos vamos a ocupar de como ejecutar todos ellos a través de un punto único.

Solución

Siempre hay más de una solución a un problema, por lo que la siguiente no es sólo una de ellas.

1) Crear una variable para la ruta del paquete: Desde el proyecto de SSDT, con el paquete vacío (que he renombrado como ‘Maestro.dtsx’) que agrega la plantilla, añadimos una variable de tipo string que llamaré RutaPaquete. He añadido un valor por defecto a la variable.

string variable

El valor de esta variable será establecido en cada iteración del bucle ForEach, ahora veremos como…

2) Añadir un bucle ForEach al Control Flow, y una vez en el área de diseño editamos sus propiedades haciendo doble click. En la sección Colección debemos asegurarnos de que el enumerador seleccionado es el de archivos (Foreach File Enumerator). Cada enumerador lleva una configuración específica. En el de archivos, debemos establecer que carpeta vamos a recorrer y que tipo de archivos. La siguiente imagen muestra la configuración que he utilizado para recorrer la carpeta que contiene los archivos con extensión dtsx:

ForEach loop File Enum configuration 01

Fijaros que he seleccionado la opción “Fully qualified” como nombre de archivo. Esto me devolverá la ruta completa de cada fichero.

3) Asignar el valor a una variable. Aquí es donde entra en juego por primera vez la variable que hemos creado anteriormente (RutaPaquete). En la configuración de mapeo de variables (Variable Mappings) del bucle ForEach asignamos la variable User::RutaPaquete al resultado de cada iteración. Como este tipo de bucle sólo devuelve un resultado (el nombre del archivo) la vinculamos con el índice 0:

ForEach loop File Enum configuration 02

Y hasta aqui la configuración del bucle ForEach. Ahora vamos con la tarea Ejecutar Paquete.

4) Añadir tarea Ejecutar Paquete dentro del bucle. Hacemos doble click para editarlo y en la sección Paquete cambiamos la configuración del componente.

a) En tipo de referencia establecemos External Reference (antes de 2012 y los modelos de proyecto de despliegue de proyecto, esta parte no existía).

b) En la localización cambiamos a sistema de archivos (File System)

c) En conexión desplegamos el menú, seleccionamos nueva conexión y seleccionamos cualquier archivo. Este parámetro será sustituido por el valor de la variable, pero por ahora lo dejamos así.

Execute Package task configuration 01

Y ya estaría lista esta tarea.

Existen más opciones de configuración en esta tarea como el password de los paquetes, el mapeo de variables, si se debe ejecutar con una nueva instancia de proceso,etc.. pero para este ejemplo no necesitamos nada más. Os invito a leeros el BOL sobre esta tarea para obtener más información http://technet.microsoft.com/es-es/library/ms137609.aspx

5) Crear un expresión para la conexión. Seleccionando el Administrador de conexión de fichero que creamos en el paso 4.C y pulsando la tecla F4 accedemos al panel de propiedades. Buscamos la propiedad expresiones (Expressions) y establecemos la propiedad ConnectionString asignando la variable @[User::RutaPaquete]

image

Esto provocará que en cada ejecución de la tarea, la conexión tome la ruta del fichero actual dentro de la iteración del bucle.

Si ejecutamos este paquete desde el proyecto podemos comprobar que se ejecuta cada uno de los paquetes que existen en la carpeta que hemos configurado en el bucle ForEach (c:\ETL Packages). He añadido un tarea ejecutar Script con el código c# para lanzar un evento de tipo información con la finalidad de mostrar la ruta del fichero actual en cada iteración:

image

Ya solo quedaría configurar un Job del Agente SQL con un único paso que ejecute este paquete maestro para automatizar la ejecución de todos los contenidos en la carpeta.

Podéis descargar este paquete desde Skydrive

Espero que os haya sido de utilidad!

Saludos.

martes, 25 de junio de 2013

SQL Server 2014 disponible en CTP1

 

SQL Server logo

Hoy se ha hecho pública la Community Technology Preview 1 de la nueva versión de SQL Server 2014.

Este lanzamiento de este producto fue anunciado en el Tech Ed que se realiza en Estados Unidos, junto a otras novedades como la versión R2 de Windows Server 2012, que también se encuentra disponible en versión Preview.

Las principales novedades de SQL Server 2014 son las siguientes:

  • Bases de datos en memoria, o como se le conoce por su nombre clave Hekaton. ¿Qué es esto? Microsoft ha unido los esfuerzos de la división de investigación (Microsoft Research) con los del equipo de producto SQL Server. El propósito de esta nueva vía de desarrollo en SQL Server fue proporcionar un sistema sin bloqueos, que son el principal motivo cuellos de botella en sistemas multiprocesador, limitando la utilización de las capacidades de CPU. Hekaton es capaz de mantener los datos íntegros, sin corrupción, sin realizar bloqueos en las estructuras (filas, páginas, tablas). El resultado es un motor relacional en memoria que ofrece un rendimiento hasta 100 veces superior al motor tradicional. Además, y al contrario que otros fabricantes (SAP, Oracle) que ya cuentan con sistemas similares, Hekaton se ofrece como una característica integrada del motor de bases de datos.

    Más info en http://research.microsoft.com/en-us/news/features/hekaton-122012.aspx y http://blogs.technet.com/b/dataplatforminsider/archive/2012/12/11/how-fast-is-project-codenamed-hekaton-it-s-wicked-fast.aspx
  • xVelocity ColumnStore, o almacenamiento por columnas con tecnología xVelocity. A estas alturas xVelocity ya debería resultarnos familiar, hasta Excel cuenta con ella. Comenzó llamandose VertiPaq y es la piedra filosofal de las bases de datos de Analysis Services de modelo tabular. Provee de una compresión espectacular a nivel de columnas y en la versión anterior, SQL Server 2012, ya se incluía en el motor relacional para los índices columnares. En SQL Server 2014 también se utiliza para el almacenamiento por columnas. Una de las mayores ventajas con respecto a la versión anterior es que los índices permiten lectura y escritura. Es un gran avance para las cargas y consultas sobre los data marts/warehouses.

    Más info en ttp://blogs.technet.com/b/dataplatforminsider/archive/2012/03/08/introducing-xvelocity-in-memory-technologies-in-sql-server-2012-for-10-100x-performance.aspx
  • Uso de discos SSD como memoria
  • Mejoras en alta disponibilidad

Evolución de SQL Server (imagen de http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx)

WhitePaper

Durante las próximas semanas tendré la oportunidad de montar un entorno para poner a prueba este flamante SQL Server 2014 y descubrir más detalles acerca de sus capacidades. Mantente en línea si quieres estar informado
Sonrisa

Pero no esperes más Descárgate ya tu copia de SQL Server 2014 CTP1 y comparte tu experiencia!! Además, si te gusta jugar en las nubes también está disponible una imagen para máquinas virtuales de Azure que monta esta CTP1 de SQL Server 2014.

También, y desde hace ya algunos días, se encuentra disponible para descarga la Guía de producto SQL Server 2013 CTP1. Ayer día 24-06-2013 se realizó la última actualización de este paquete de documentación a la versión 3.0.

Os dejo algunos enlaces de interés referentes a las novedades de SQL Server 2014:

Centro de recursos SQL Server 2014 

Bloggers:

viernes, 31 de mayo de 2013

GeoFlow Preview para Excel 2013

Excel se está consolidando como una poderosa herramienta para consumo y análisis de datos, especialmente en el ámbito del Business Intelligence personal o Self-Service BI. En la versión 2013 ya tiene integradas de forma nativa la tecnología xVelocity, el add-in PowerPivot y Power View. Pero además  Microsoft ha hecho pública la preview de un nuevo complemento que nos permitirá representar nuestros datos posicionándolos geográficamente, en un entorno de visualización tridimensional con un interfaz de usuario que se enmarca perfectamente dentro del estilo Office. Estamos hablando de GeoFlow, aunque quizás este nombre cambie más adelante.

Este complemento utiliza mapas de Bing y renderiza los datos que pueda ubicar a través de coordenadas o nombres de ciudades estados, provincias y países y nos permitirá realizar análisis, detectar patrones, obtener una vista en forma de secuencia cinematográfica a través del tiempo, etc..

Ya puedes descargarte e instalar esta versión

Llevaba algún tiempo queriendo conocer más acerca de este producto, ya que se han ido publicando algunas cosas (http://sqlserverbiblog.wordpress.com/2013/03/05/sql-server-community-world-tour-with-geoflow/) en la red antes de que se hiciera pública esta preview.

Por fin he tenido oportunidad de instalarlo y probarlo, y ciertamente es muy sencillo de manejar para crear presentaciones realmente impresionantes.El unico requisito fundamental es contar con un rango de datos dentro de Excel que contengan información que permita posicionar los datos en un mapa, como mencionamos arriba, a través de coordenadas o nombre de la ubicación. Para hacer mis primeras pruebas con GeoFlow he utilizado un pequeño conjunto de datos con los Tweets que se han publicado desde el día 14/03/2013  y que contienen las palabras @SolidQ, @SolidQEs, @SolidQIT, #SQSummit13 y #SolidQ.

Cuando pensé en utilizar este conjunto de datos no esperaba unos resultados positivos, ya que la mayoría de los tweets no tienen una localización muy clara. Aunque muchos usuarios tienen activado el uso de la geolocalización al publicar los tweets, esta información no siempre se encuentra registrada en el tweet. De forma que la unica forma viable que encontré de poder posicionar los datos fue por la propia localización del usuario, aquella que se configura en el perfil…. y en dónde se puede poner lo que uno quiera:

image

Como se puede comprobar en las columnas TweetLatitude y TweetLongitude encontramos un hermoso 0, aunque la columna UserGeoEnabled tenga un valor TRUE. Por tanto nos quedamos con la columna UserLocation y vamos a ver que es capaz de hacer GeoFlow con esa información.

Una vez hemos descargado e instalado el complemento, en la cinta de Insertar  de Excel aparece el grupo GeoFlow, que contiene un solo botón con dos comandos: Iniciar GeoFlow y Añadir datos seleccionados a GeoFlow, e inicialmente solo aparece activado el primero:

image

 

Si lanzamos GeoFlow nos aparecerá una ventana con cuatro areas.

  • En la parte superior disponemos de la cinta Inicio, con las opciones disponibles: cambiar el tema del mapa pudiendo seleccionar un mapa geográfico o político con distintas iluminaciones, insertar gráficos o cajas de texto, añadir capas, buscar geolocalizaciones, capturar la vista mostrada, etc..
  • El panel de escenas del “tour”, situado a la izquierda. A través de este panel podemos seleccionar y configurar las distintas escenas que componen nuestro “tour”
  • El panel de tareas, desde el que podremos configurar el elemento seleccionado, ya sean las propiedades de las escenas o las distintas capas visuales que podemos añadir al mapa.
  • Y por fin el área central, dónde figura el globo terraqueo y en el que podemos visualizar los datos geo posicionados y animaciones a través del tiemp que vayamos configurando en las distintas capas.

 

image

En una misma escena podemos tener varias capas y en cada capa configurar una visualización de datos. Para mis pruebas he utilizado una sola escena con dos capas, en la primera capa muestro columnas agrupadas con número de tweets que se van produciendo a lo largo del tiempo, y para crear los grupos utilizo la palabra asociada (@SolidQ, @SolidQEs, @SolidQIT, #SQSummit13 y #SolidQ). En la segunda capa mostraré el número de usuarios que están publicando esos tweets, y el lenguaje que tienen configurado.

Por defecto nos aparecerá una capa, Layer1, a la que podemos cambiar el nombre a través del panel de tareas pulsando el botón image que encontramos en la parte superior del panel. Desde aqui podemos elegir si se muestra o no cada capa, configurarla o eliminarla. La siguiente imagen muestra una breve descripción de la función de cada botón que encontramos en el panel de tareas cuando seleccionamos la vista de capas:

 

image

 

Una vez tenemos establecido el nombre de la capa, accedemos al botón 'Configuración de datos de la capa’ y veremos la lista de campos disponibles. Esta lista de campos depende de las tablas que existan en el modelo de datos del libro Excel y que hayamos añadido a GeoFlow (desde el botón de la cinta Insertar).

Llegados a este punto debemos definir como se van a localizar los datos. Para ello en la parte inferior de la lista de campos vemos una sección con el título Geography. Es en esta sección dónde debemos arrastrar y solar los campos que demarcan ubicación. Para este ejemplo recordad que utilizaremos la columna UserLocation y la vamos a configurar de tipo City, y solo queda pulsar el botón Map it para poder continuar.

image

A continuación la lista de campos toma otro aspecto, en la parte superior (bajo el desplegable de selección de capas) aparece una sección con el nombre de Map by UserLocation (county) junto a un botón Edit y justo abajo un porcentaje:

image

Ese 25% siginifica que ha sido capaz de ubicar correctamente la cuarta parte de los registros utilizando el contenido de la columna UserLocation como identificadores para regiones de tipo City. No es mucho, así que me he creado una columna para insertar el país y asi subir el porcentaje de localizaciones de tipo ciudad hasta un 56%…

image

 

Lo siguiente es configurar la representación gráfica de datos en el mapa. Podemos elegir para cada capa entre gráficos de tipo columnas (agrupadas o apiladas), burbúja o mapa de calor, pero antes vamos a añadir datos a las distintas secciones: Height (peso), Category (categoría) y Time (tiempo).

  • Height: Asignamos la columna TweetID y se cambia la función de agregación por Count() en lugar de Sum(). Esto hará que las columnas crezcan según el número de tweets.
  • Category: Arrastrar la columna TokenWord para que por cada una de las palabras clave cree una columna.
  • Time: En esta sección añado la columna Created_at que representa la fecha de publicación del tweet. Una vez añadimos el campo a la sección aparece un menú desplegable que nos permite cambiar la unidad en que se mostrará la linea de tiempo. Además podemos cambiar la configuración de tiempo en Time Settings haciendo posible que cuando se visualicen los datos a través de la línea de tiempo, se representen de distintas formas
    • Instant: Se mostrarán los datos correspondientes a cada momento del tiempo según se avance.
    • Time Accumulation: Se mostrará un crecimiento paulatino acumulando los valores segun avance el tiempo.
    • Persist the Last:  Muestra el último valor para el periodo y avance.

image

 

Si pulsamos el botón de reproducción en la línea de tiempo, podemos observar como se representan los datos según la configuración que hayamos puesto para el tiempo. Además se puede controlar el ritmo al que avanza esta linea de tiempo y acotar los margenes entre el rango de fechas existente, a través del botón de configuración que encontramos en la mismo control de reproducción del tiempo:

image

 

Echo en falta la posibilidad de exportar los “Tour” creados en algun formato (video, PowerPoint) o incrustar escenas en una hoja de Excel junto a otras presentaciones gráficas, además de la posibilidad de profundizar en los datos… pero aún estamos con la versión Preview Sonrisa

viernes, 19 de abril de 2013

SolidQ Summit 2013 Madrid #SQSummit13

 Regreso a SolidQ Summit

 

Un año más SolidQ celebra su Summit en Madrid, la novena edición, los días 11,12 y 13 de Junio en el Regus Campo de las Naciones.

 

El Summit que organiza SolidQ anualmente en España está de vuelta, como seguramente ya estaréis enterados los miembros de esta comunidad. Porque lo somos, comunidad, y contadas son las ocasiones en las que podemos reunirnos y compartir experiencias y vivencias, técnicas y personales, hablando en sesiones que comprenden los aspectos más relevantes y novedosos de la industria o en petit comité memorando buenos y malos ratos compartidos o llanamente interesándose por los más allegados aquellos que llevamos años de relación y este es uno de esos momentos en los que celebramos reencontrarnos.

SolidQ

Este será mi quinto año asistiendo, primero como público y luego como ponente, y las sensaciones siempre han sido positivas, cumpliendo mis expectativas sobre la formación y el aprendizaje, y sintiéndome integrado en un grupo de fantásticas personas con intereses y pasiones comunes. Porque aunque hay muchas otras cosas en las que empleo mi tiempo y energías que son más corrientes entre los mortales y siempre hay alguien con quien hablar, debatir o discutir; no siempre puedo contar penas y alegrías sobre mis batallas, resolviendo problemas con MDX, optimizando un diseño para mejorar los tiempos de respuesta, etc… sin que piense que estoy hablando en klingon para mi interlocutor, aunque ponga toda su capacidad atención para hacer por entenderme (dedicado agradecimiento a Eva)

Vale, no es un campamento de verano... el objeto principal de este evento es la transferencia de conocimientos, muchas veces de forma circular o recíproca. Y es que, como cada año, se han organizado multitud de sesiones, 42 concretamente, para todos los niveles en cuanto a profundidad técnica divididas en cuatro temáticas principales Inteligencia de Negocios, SharePoint, Azure y SQL Server, abarcando varias perspectivas como rendimiento, usabilidad, monitorización, metodología, filosofía, etc.. desde un enfoque que parte del conocimiento basado en la experiencia.

En cualquier caso no te quedes sólo con mi opinión, algunos asistentes de ediciones anteriores han querido expresar la suya: http://summit.solidq.com/madrid/2013/Paginas/Opinion-de-asistentes-al-SolidQ-Summit.aspx

Puedes consultar la agenda completa en el sitio del Summit 2013 para planificar tus jornadas, y seguir el hashtag #SQSummit13 en Twitter para estar al día.

Yo estaré allí, ¿y tú?

jueves, 18 de abril de 2013

SSIS Básico: Guardar valor de columnas en una variable

 

En relación a uno de los comentarios que me han dejado en el post SSIS Básico: Ajustando la longitud de una columna, volvemos a la carga con pequeño how-to de como guardar el valor de una columna en una variable dentro de un Data Flow a utilizando un Script component.

Para poneros en situación, voy a utilizar una tabla que guarda tweets publicados con ciertas palabras, y guardaremos en la variable el último usuario que haya publicado en función de la fecha  del tweet registrada en la columna Created_at.

Primero debemos contar con la variable en la que necesitamos guardar el valor, para este ejemplo he creado la variable VariableCadena, con ámbito de paquete para poder utilizarla en el Control Flow o subsiguientes Data Flows:

image

Dentro del Data Flow en el que vamos a registrar el valor de alguna columna en la variable, añadimos un Script component en modo transformación:

image

En la configuración del componente, en la sección Input columns, seleccionamos la columna desde la que queremos obtener el valor, y cualquier otra que necesitemos si la lógica de asignación del valor tiene más dependencias. Para este ejemplo vamos a utilizar las columnas UserName y Created_at del flujo de datos:

image

Nos movemos a la sección Script y añadimos nuestra variable a la lista de variables con acceso de escritura.

image

Pulsamos el botón Edit Script para escribir el código necesario (ejemplo en C#). Algo que debemos tener en cuenta es que no se permite la escritura de valores en variables fuera del método PostExecute(), por lo que nos tendremos que apoyarnos en variables de la clase ScriptMain para poder dar salida a nuestra variable de paquete.

Otro detalle a comentar es el método <nombreInput>_ProcessInputRow, que se ejecutará para cada una de las filas que provea el flujo de datos conectado a este componente.

Con esto en mente, escribimos el siguiente código:

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
//variables de apoyo para la lógica
string UserName = string.Empty;
DateTime Created_at = DateTime.MinValue;

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
//evaluamos si la fila actual tiene una fecha mayor a la registrada por la última fila
if (Row.Createdat > this.Created_at)
{
//asignamos el nombre del usuario a la variable interna
this.UserName = Row.UserName;
this.Created_at = Row.Createdat;
}
}

public override void PostExecute()
{
base.PostExecute();
//asignamos el valor de la variable interna a la variable de paquete
this.Variables.VariableCadena = this.UserName;
}
}

Como habeis comprobado, utilizamos la variable interna UserName para guardar el valor que queremos asignar a la variable de paquete en el método PostExecute().


Otra nota importante: Dentro del mismo Data Flow no se puede acceder al contenido de la variable modificada. Cuando el proceso de ejecución entra en el Data Flow se bloquea el valor que tuviera la variable en el momento del evento PreExecute(), como se muestra en el post SSIS Utilizando el RowCount. Recordad que a la variable de paquete le asignamos el valor en el evento PostExecute().


Sin embargo, el nuevo valor esta diponible a partir de entonces y por tanto para cualquier tarea que se ejecute a continuación. Podemos mostrar el valor de la variable en un Script task desde el Control Flow con propósitos didacticos, aunque este tipo de mecanismos se suele utilizar para luego pasar como parámetro a filtros en consultas en otro Data Flow o Execute SQL…


image


 





Entradas populares