Vamos a ver un ejemplo de como leer ficheros de texto desde SQL Server Integration Services. Huyendo un poco de los ejemplos típicos vamos a hacer un paquete de SSIS que corrija la sincronización de los ficheros .srt de subtitulos. Muchos reproductores de video pueden subtitular cualquier contenido multimedia a partir de ficheros de extensión srt. Estos ficheros realmente son texto plano con un formato como este.

Nota: Como podeis ver mi entorno siempre está en Inglés, en mi caso hay dos motivos, primero trabajo en una compañía internacional, en la que el inglés es la lengua “común”. Segundo, es innegable, que en el mundo de la informática el inglés es lo más universal, no me acaban de convencer las traducciones, no porque estén mal sino porque  a fuerza de costumbre no encuentro nada cuando el entorno está en el idioma de Cervantes. Mis disculpas a los más puristas.

1
00:00:01,968 –> 00:00:04,835
Ejemplo de texto 1

2
00:00:04,938 –> 00:00:08,237
Ejemplo de texto 2

En resumen se trata de una linea indicando el orden, otra indicando el intervalo de tiempo separado por –> y una tercera que es el texto. Nuestro ejemplo tratará de crear un paquete de SSIS que cambie (adelantando o atrasando) la sincronización de estos ficheros .srt.

Para comenzar lo que haremos será crear un nuevo paquete de SSIS. Los ejemplos que vais a ver están hechos con la CPT3 de SQL Server 2012 , AKA Denali.

Colocaremos un Data flow Task. En entorno nuevo es realmente muy atractivo, el zoom se ve muy claramente, las barras de tareas están muy mejoradas, incluso la ayuda parece más adecuada al comienzo con SSIS

Renombraremos la caja del data flow task con un nombre más descriptivo, recordad que esto es bastante importante. Si lo hacemos bien los paquetes serán prácticamente auto-documentados y eso será realmente interesante, particularmente transcurrido algún tiempo desde el comienzo.

Despues, pulsaremos botón derecho sobre la superficie de Connection Managers y crearemos una nueva conexión a fichero de texto pulsando sobre “New Flat File Connection”.

Ahí simplemente especificaremos un fichero del formato que estamos tratando, en mi caso demo.crt como puede observarse en la siguiente figura. Notese que he tocado la propiedad “column names in the first data row” porque no es así.

En nuestro caso las columnas como tal no existen, pero si tendremos 4 tipos de filas, los casos más normales quizá los campos vengan separados por comas o por puntos y comas, o por cualquier caracter. Cualquier escenario común se podrá manejar tocando unas pocas propiedades. Nuestro caso, en el que vienen mezcladas cosas hemos de probar a distinguir cada una de las lineas. Al pulsar en la segunda pestaña veremos una pantalla como la siguiente

Esto nos dice que efectivamente cada línea tendremos que tratarla de una forma distinta. A continuación vamos a poner un conector a este fichero de texto como origen de datos siguiendo el wizard de SQL Server 2012

En nuestro caso seguiremos el wizard, elegiremos fichero de texto plano y el origen de datos que acabamos de crear.  Eso nos dejará un componente origen de datos de texto  que veremos en las siguientes imágenes. En nuestro caso además le cambiaremos el nombre, ya sabéis con el objeto de que sea autodescriptivo.

En nuestro caso, lo único que queremos hacer es modificar las líneas que tienen fecha y horas desplazandolas un tiempo hacia adelante o hacia atrás.

Las columnas que tienen este dato tienen un formato como este

00:00:01,968 –> 00:00:04,835

Ambas horas han de ser desplazadas.

Para poder hacer el desplazamiento necesitaremos dos cosas

• Identificar que es una línea de tipo rango de tiempos
• Añadir o quitar el tiempo determinado

Para poder añadir o quitar un tiempo variable, usaremos eso.. variables. Vamos pues a añadir una variable “desplazamiento”.  Para ello pulsaremos con el botón derecho del mouse sobre la superficie del dataflow task y pulsaremos la opcion “variables”. A continuación pulsaremos el botón añadir variable , que es el resaltado en amarillo en la siguiente figura.

Nombraremos a la variable como estimemos oportuno (en nuestro ejemplo desplazamiento) y le daremos como tipo de datos int y como Valor el desplazamiento que queremos obtener (milisegundos) . El objeto debe tener como Scope (alcance) el necesario para que funcione en nuestro ambiente. El scope es el equivalente a donde se ven las variables (todo el paquete, este objeto, el flujo de trabajo.. etc). Nuestra variable ha de quedar como puede verse en la siguiente figura.

El siguiente paso será distinguir las líneas que son de tipo desplazamiento. Si lo es, entonces simplemente sustituiremos el valor por el incremento que necesitamos en ambos casos.

Para distinguir estos casos usaremos la funcion FindString, que localiza la posición de una ocurrencia. Esta condición

FINDSTRING([Column 0],”–>”,1) == 0

Nos daría verdadero solamente si la cadena no se encuentra. Si la cadena se encuentra entonces tendremos que incrementar la función.

Para incrementar desplazamiento sobre la fecha hora hay que hacer lo siguiente :

• Separar la hora de comienzo
• Convertirla en fecha hora
• Incrementarla
• Convertirla en texto
• Separar la hora de fin
• convertirla en fecha hora
• incrementarla
• convertirla en texto
• Unirla

Todas estas operaciones se podrían hacer en una sola expresión, pero esto seguramente nos haría muy compleja esa expresión, en su lugar, y con el fin de mejorar la claridad lo vamos a hacer en varios pasos

Paso 1. Crear dos columnas con el texto desde hasta.

Para desde usaremos la siguiente expresión

FINDSTRING([Column 0],”–>”,1) != 0 ? LEFT([Column 0],FINDSTRING([Column 0],”–>”,1) – 1) : “00:00:00.000″

Entendida significa,

si la posición que devuelve findstring es distinta de cero, (es una de las que nos interesan)

Empezando por la izquierda quedate los caracteres hasta que empieza el “—>”

si no

pon un “00:00:00.000”

Un caso equivalente es el de la expresión hasta

FINDSTRING([Column 0],”–>”,1) != 0 ? SUBSTRING([Column 0],FINDSTRING([Column 0],”–>”,1) + 3,100) : “00:00:00.000″

sla posición que devuelve findstring es distinta de cero, (es una de las que nos interesan)

Empezando por donde está el “—>” quedate con todos los caracteres hasta el final

si no

pon un “00:00:00.000”

Así queda el derived column component que tiene estas expresiones

Ambas expresiones devuelven un string.. y no se puede operar con esos strings como si fueran fechas, así que vamos a añadir un componente adicional que convierta a fecha esos strings y les añada el desplazamiento

La formula que usaremos será la siguiente  para desde

DATEADD(“Ms”,@[User::Desplazamiento],(DT_DBTIMESTAMP)(DT_DBTIME2,3)REPLACE(Desde,”,”,”.”))

Es decir en un solo paso convertirmos a fecha, añadimos los milisegundos que necesitamos y guardarmos en una nueva variable

El motivo de que la expresión sea tan compleja es para asegurarnos que no tenemos molestos problemas de conversión. Pasando por DT_DBTIME2 nos aseguramos de convertir adecuadamente el formato que recibimos “00:00:00,000”, como la coma que recibimos esperamos sea un punto.. pues hay que sustituirla. Despues convertimos a D_DBTIMESTAMP para poder hacer la operación de suma de milisegundos y por último sumamos esos milisegundos.

La figura muestra como queda la cajita de derived column

Conseguido esto ahora hemos de deshacer el camino, deshacer el camino implica convertir esto a un formato tal que así .

00:00:01,968 –> 00:00:04,835

Aunque también podría hacerse en un solo paso, vamos a usar varios para que se vea con más claridad. La expresión que usaremos será la siguiente

SUBSTRING((DT_WSTR,30)[Desde Fecha],12,12)

Primero convertimos a string, esto nos da el formato 2011-10-25 00:00:15.988 y de este nos quedamos con 12 caracteres empezando en la posición 12, es decir.. el mismo formato de origen. Así queda el cuadro derived column.

Por último usaremos una nueva columna derivada para unir ambas tal y como estaba la original, la fórmula será, y aprovecharemos para sustituir la actual con esta expresión en el caso que sea la columna que esperamos.

FINDSTRING([Column 0],”–>”,1) != 0 ?  [Desde Texto] + ” –> ” + [Hasta Texto] :  [Column 0]

Así queda el componente

Por último, grabamos nuestro fichero de salida, para eso creamos una nueva conexion a archivos de texto y seguimos el asistente como destino.

Configuramos el fichero de la misma forma, excepto que en advanced añadiremos nosotros la columna de forma manual como string de 200 caracteres. Despues en los destinos buscaremos el flat file dstination y lo enchufaremos para que funcione. Entraremos en mappings y conseguiremos mapear la columna 0 del origen a la columna 0 del destino

Por último vamos a permitir que tanto el fichero de origen como el de destino puedan ser expresados por parámetros, usando variables. Así pues crearemos dos variables, origen y destino de tipo string

Estas variables nos serviran para ponerlas como expresión en las cadenas de conexion. Lo que haremos será poner en expresiones que el connection string se tome desde una variable, como puede verse en la imagen siguiente

Haremos la misma operación con el destino, quedará tal que así

Nuestro paquete finalmente quedó así

Dentro de la línea de recursos de formación que seguro irán apareciendo para SQL Server 2012 (AKA Denali) hace unos dias aparecía publicado su Developer training Kit, que permitirá aprender vias de construcción tanto de aplicaciones del mundo relacional como BI, aquí teneis el link

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27721

Cuando hay un gran número de datos por sincronizar entre tablas es muy común usar estrategias para disminuir el número de filas afectadas. TSQL puede servir, incluso si hay dos servidores, podemos llevar una tabla al lugar remoto, hacer un full join, o usar el comando MERGE, pero.. suele ser una solución que escala poco y que carga el server.

Curiosamente esta misma estrategia, usando SSIS puede ser muy muy ágil. Básicamente por la forma en la que SQL Server Integration Services va a manejar los datos, usando buffers que va a ir liberando progresivamente. Lo que vamos a ver durante el artículo es una parte bastante básica de SSIS, aún así es un modelo que puede ser perfectamente extrapolable a muchísimas situaciones y puede ser realmente ágil y util, veamos lo que diga.

Lo que vamos a usar en SQL Server Integration Services va a ser un DataFlow o flujo de datos. Dentro de él, tendremos dos componentes que leerán las tablas de origen y destino, un componente que hará el join (en nuestro caso haremos full join) y un conditional split que nos permitirá distinguir que tipo de comportamientos encontramos en la tabla. O hablando de forma mas clara, al final lo que vamos a hacer es leer el origen y el destino, comparar los registros y  seguir los siguientes criterios:

• Si un registro está en origen y no en destino… es un registro nuevo.
• Si un registro está en destino y no en el origen … es un registro borrado. (alguna vez estuvo.. ya no)
• Si un registro está en ambos sitios, hay que comparar los datos, si son iguales, no hay nada que hacer y si no lo son entonces hay que actualizar el registro en destino.

Sobre este proceso que lee todo hay mil mejoras que pueden hacerse si se tiene control sobre como pasan las cosas en el origen. Aspectos como CDC (Change Data Capture), Triggers en origen, tablas de log que puedan dejar las aplicaciones pueden cambiar sensiblemente este planteamiento y pueden además hacerlo mucho mas eficiente. En cualquier caso, lo que vamos a escribir en este artículo es la versión más sencilla, fuerza bruta,leer y sincronizar todo.

El esquema global del paquete será el siguiente

Lo primero que haremos será poner un data flow, al que cambiaremos el nombre por “Sincronización de XX” en nuestro caso  Sincroniza Dbo_dim_customers. Las descripciones no admiten algunos caracteres especiales, sin embargo quiero recalcar la importancia de poner nombres muy descriptivos, siempre he dicho que los paquetes de SSIS pueden considerarse “Auto documentados” si están bien estructurados, y además dejan mucho más claro lo que hacen y son más mantenibles, así pues… invertir (que no perder) unos segundos en poner nombres descriptivos me parece que es ganar mucho tiempo global en el desarrollo.

Dentro de este  flujo de datos en donde vamos a sincronizar nuestra tabla, comenzaremos añadiendo dos conexiones a nuestro Connection manager, tal y como puede verse en la siguiente figura. La imgaen puede resultar un pcoo engañosa, yo he renombrado después los objetos de conexión para que me resulten más fácilmente identificables. Comenzando por Origen y Destino, de esta forma me resulta mucho más sencillo.

 En amarillo resaltado podeis ver que los origenes han sido renombrados a Origen AdventureWorks y DestinoAdventureworks.

Tras eso hemos arrastrado un OLE Db Source que hemos llamdo ORIGEN Dim Customer, que va a contener un SELECT a la TABLA dbo.DimCustomer que es la que vamos a sincronizar.  Una de las cosas que mas complica los paquetes de SSIS de este tipo es que los nombres de columnas en origen y destino suelen ser iguales, con lo que luego el paquete internamente los diferencia añadiendo delante como prefijo el nombre del objeto que los “creó″ dentro de SSIS, esto da nombres larguísimos y muchas posibilidades de equivocarse, así que yo suelo añadir un alias siempre, para origen ORIGEN_<nombre de campo> y para destino igual DESTINO_<nombre de campo>, sin embargo en tablas como esta o mayores en número de columnas (27) esta operación puede ser un poco pesada, yo lo que suelo hacer es usar un truquito, la siguiente sentencia, para que los nombres de las columnas se me escriban “solas”, esto y un poquito de copiar y pegar arreglan muchas cosas

 SELECT COLUMN_NAME +' as ORIGEN_'+COLUMN_NAME +', ' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='DimCustomer'

En nuestro caso el resultado obtenido tras todas esas operaciones de copiar y pegar es el siguiente

SELECT CustomerKey as ORIGEN_CustomerKey, GeographyKey as ORIGEN_GeographyKey, CustomerAlternateKey as ORIGEN_CustomerAlternateKey, Title as ORIGEN_Title, FirstName as ORIGEN_FirstName, MiddleName as ORIGEN_MiddleName, LastName as ORIGEN_LastName, NameStyle as ORIGEN_NameStyle, BirthDate as ORIGEN_BirthDate, MaritalStatus as ORIGEN_MaritalStatus, Suffix as ORIGEN_Suffix, Gender as ORIGEN_Gender, EmailAddress as ORIGEN_EmailAddress, YearlyIncome as ORIGEN_YearlyIncome, TotalChildren as ORIGEN_TotalChildren, NumberChildrenAtHome as ORIGEN_NumberChildrenAtHome, EnglishEducation as ORIGEN_EnglishEducation, SpanishEducation as ORIGEN_SpanishEducation, FrenchEducation as ORIGEN_FrenchEducation, EnglishOccupation as ORIGEN_EnglishOccupation, SpanishOccupation as ORIGEN_SpanishOccupation, FrenchOccupation as ORIGEN_FrenchOccupation, HouseOwnerFlag as ORIGEN_HouseOwnerFlag, NumberCarsOwned as ORIGEN_NumberCarsOwned, AddressLine1 as ORIGEN_AddressLine1, AddressLine2 as ORIGEN_AddressLine2, Phone as ORIGEN_Phone, DateFirstPurchase as ORIGEN_DateFirstPurchase, CommuteDistance as ORIGEN_CommuteDistance FROM dbo.DimCustomer

El resultado es  el que podeis ver en las dos siguientes figuras

Es la configuración, exceptuando el error, del componente oleDB que nos sirve de origen, lo más interesante de las imágenes es que como puede verse el alias indica claramente que estamos hablando de los datos de origen, de forma que no hay duda, estemos a la altura del flujo de datos que estemos de donde provienen estos campos y resultan menos probables errores por este punto.

De la misma forma configuramos el destino que quedará de una forma muy similar a lo que hemos visto en el origen, pero simplemente con los alias cambiados.

Vamos a configurar un poco más el componente. Básicamente son SELECTs, sin embargo, es conveniente que estos datos vengan ordenados. El motivo de esta ordenación es que cuando se va a hacer un merge join  las entradas han de estar ordenadas y el componente sort de SSIS es un componente bloqueante, lo que no quiere decir que no se use, simplemente que si se puede evitar sin perjudicar mucho al origen mejor. En estos casos suele ser un orden por la clave primaria, y eso suele ser muy poco costoso para los origenes, pero si que ganamos muchísima velocidad de ejecución. Para eso tenemos que asegurarnos que SSIS sabe que los datos vienen ordenados, no basta con ordenarlos. Para eso seguiremos los siguientes pasos, sobre el origen de datos (origen y destino, claro), botón derecho, mostrar editor avanzado, y ahí lo primero asegurarnos que el flujo de datos marca que está ordenado.

Además de eso hay que entrar en las columnas y especificar cual de ellas ocupa la posición 1 de orden, la posición 2 etc. Es decir si el conjunto de registros viene ordenado por más de un campo hay que indicar el orden. Esto se hace como puede verse en la siguiente figura.

Una vez tenemos estos dos componentes configurados y ordenados (recordad que hay que hacerlo para ambos componentes) 

A continuación tenemos que añadir el compnente merge Join y unirlo a ambas entradas. También le cambiaremos el nombre para describir su cometido.

Despues entramos a configurarlo señalando que es un full outer join y seleccionando todas las columnas.  El componente quedará como podemos  ver en la figura siguiente.

A continuación hemos de distinguir entre los registros nuevos, los registros borrados y los registros modificados.  Este punto es lo que contabamos en el origen más desarrollado

• Los registros nuevos serán aquellos que estando en origen no están en destino, lo que podremos comprobar mirando si la clave en destino es NULL (será porque no ha hecho match y por tanto deja Destino a NULL).
• Los registrosborrados serán aquellos que estando en destino ya no se encuentran en origen, esto lo podemos comprobar mirando si la clave de origen es null (no ha hecho match con el origen). 

Ambos casos podeis verlos implementados en la siguiente pantalla.

Lo que esto no soluciona es el problema de la comparación. comparar no es que sea complicado pero si es… pesado, hay 27 campos y la linea de condition del componente conditional split no es que sea precisamente el sitio indicado para escribir cantidad de código. Una alternativa es escribir código personalizado en un componente, no es demasiado complicado y hay asunciones que podemos hacer si hemos seguido la estructura (campo ORIGEN_ se corresponde con otro DESTINO_) pero aún así, no me atrae esa idea, prefiero de alguna forma escribirlo aquí. Otro problema adicional son los nulos, ningun lenguaje, ni siquiera transact SQL sabe comparar nulos, tiene su explicación, pero el caso es que nosotros debemos hacer es que ambos sean nulos o que sean iguales. No nos vale sustituir el valor nulo por un valor por defecto, porque si algún campo tuviera valor por defecto y se cambia por nulo podriamos perder el cambio. Así pues lo haremos en varios pasos.

• Primero comparamos los valores que no son NULABLES, es decir valores que no pueden contener un nulo. En este caso no hay que preocuparse por los nulos
• Luego comparamos aquellos campos que en origen sean nulos y en destino no. De esta forma detectamos los cambios entre nulos
• Por último aquellos que son distintos, pero para que no falle en ningún caso sustituimos los nulos por valores por defecto.  

 Para poder teclear todo esto pondremos un conditional split tal y como vemos en la siguiente figura.

El código que pondremos dentro será  el que se ve en la siguiente figura, y que para conseguirlo, en lugar de teclear he usado los comandos que veis más abajo como código.

Para los distintos no anulables :

select '( ORIGEN_'+COLUMN_NAME +' !=  DESTINO_'+COLUMN_NAME +')  || '
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='DimCustomer'  
AND IS_NULLABLE ='NO'

Para que uno de los campos valga nulo y el otro cualquier otra cosa

select '((ISNULL(ORIGEN_'+COLUMN_NAME +')  && ! ISNULL(DESTINO_'+COLUMN_NAME +'))  ||  '+
'(!ISNULL(ORIGEN_'+COLUMN_NAME +')  &&  ISNULL(DESTINO_'+COLUMN_NAME +')))  ||  '
from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='DimCustomer'  
AND IS_NULLABLE ='YES'

Finalmente, para los casos en que haya alguna diferencia :

select '( ( ISNULL(ORIGEN_'+COLUMN_NAME +') ? ' + 
    case WHEN DATA_TYPE in ('int','money','tinyint','numeric') then ' 0 '
         WHEN DATA_TYPE in ('datetime','smalldatetime') then '(DT_DATE)"1990-01-01"  '
         WHEN DATA_TYPE in ('char','varchar','nchar','nvarchar') then '""' 
         WHEN DATA_TYPE in ('bit') THEN ' false' END +
  ' : ORIGEN_' + COLUMN_NAME +')  !=   (ISNULL(DESTINO_'+COLUMN_NAME +') ? ' + 
    case WHEN DATA_TYPE in ('int','money','tinyint','numeric') then ' 0 '
         WHEN DATA_TYPE in ('datetime','smalldatetime') then '(DT_DATE)"1990-01-01"  '
         WHEN DATA_TYPE in ('char','varchar','nchar','nvarchar') then '""' 
         WHEN DATA_TYPE in ('bit') THEN ' false' END +
   ': DESTINO_' +COLUMN_NAME + ' ) ) || '
from INFORMATION_SCHEMA.COLUMNS c where TABLE_NAME='DimCustomer'  
AND IS_NULLABLE ='YES'

Una vez que tenemos todos los casos registrados hemos de comenzar a tratar cada uno de ellos, el primer caso es el de las inserciones de registros nuevos. No hay muchos considerandos, cuanto más rápido se inserten mucho mejor, sin embargo el modo por defecto del componente OLE-DB Destination produce table lock, y eso obviamente impide cualquier otra operación en la tabla, incluso de nosotros mismos. Por tanto podemos caer en bloquearnos a nosotros mismos, y eso además de bastante incordiante es un poco de lelos, así pues evitémoslo.  Para evitarlo, quitemos el table-lock (aunque haga que las inserciones sean un poquito más lentas) y también es útil a veces cambiar los parámetros de rows per batch, que indican cuantos registros tiene cada bloque insertado y maximunt insert commit size que indica de que tamaño es el máximo insert (por defecto a un número ridiculamente enorme).  Yo suelo poner bloques de 10.000 o 1.000 filas si no hago ningún estudio. En casos más complejos en los que algunos insert puedan fallar por otros motivos (integridad referencial o cualquier otra cosa) lo que se hace es poner un tamaño pequeño ya que esos bloques pueden tratarse posteriormente registro a registro para quedarte en un lugar exclusivamente con los que producen los errores. Este tipo de tratamiento está totalmente fuera del alcance de este artículo.

La segunda pestaña muestra el mapeo entre entradas y salidas, en el común de los casos no hace falta tocarlo, porque al final simplemente se liga por nombre, pero en nuestro caso hemos añadido el prefijo ORIGEN_ por lo tanto el link no puede hacerse de forma automatica, yo simplemente me pongo en primero y pulso la letra O,  dos veces tab y otra vez O y así de forma mecánica. como el orden de los campos es el mismo, lo normal es que en unos segundos esté todo linkado y bien linkado

En el caso que nos ocupa así quedan configuradas las entradas.

No hemos tocado  nada en la pestaña de Error output.

La siguiente salida que vamos a tratar va a ser la de elementos eliminados. En nuestro caso vamos a implementar un borrado. Realmente esto no es tan sencillo, un borrado físico, habiendo integridad referencial puede ser un tema muy complejo, además se hace fila a fila y eso puede perjudicar el rendimiento si se produjesen muchos borrados. Hay más alternativas y mejoras, pero desde luego esta es una opción a la hora de tratar eliminados sin excedernos en complejidades.

En las siguientes imágenes podeis ver como queda el componente OLE DB command que servirá para el borrado. Notad como el valor que tomamos para la clave es Destino_CustomerKey, esto es debido a que en esta salida el origen viene como NULL.

Por último nos queda que hacer las modificaciones a los registros existentes. Esto implica unir tres ramas distintas , para lo que usaremos un componente union all  despues de ese componente haremos el update, para lo que necesitamos un comando update  del tipo Update TABLA SET CAmpo =?, CAmpo2=?,… CAmpoN=? where CamposClave=? and…

Este comando puede simularse de forma parecida a la que hemos hecho con las escrituras para el conditional split, dejo del lado del lector que lo haga . 

En las siguientes lineas podeis ver como queda configurado el comando update

En proximos articulos veremos las pruebas que le vamos a hacer a este paquete para comprobar su buen funcionamiento.

Podeis descargar el paquete de ejemplo de aquí mismito

Debió ser el sábado, pero mi filtro de spam ha mantenido la intriga hasta hoy lunes, a media mañana volví a recibir la noticia, he sido nominado como SQL Server MVP. Lo fuí la primera vez en 2001, aún recuerdo el salto que dí de mi mesa por la oficina donde trabajaba, también recuerdo que muy pocos (para ser sincero creo que solamente yo) sabían que era eso de ser MVP. En aquella época ya conocía a Fermando G. Guerrero (@FernanSQL) y a Eladio Rincón , también a Emilio Boucau. Con Emilio comenzó la primera versión de PortalSQL, y por su culpa cada vez que Fernando, Eladio y Yo nos hemos juntado (los tres en españa, Emilio está en chile ahora), Eladio se llamó Emilio .

Mi primera cena, con Tella, Mengual, Peris y Sveltlana, todos (creo) los que eramos MVP ese año por España, Fernando lo era por UK, El guille por USA al igual que Pablo Roca, inolvidable.

Todo el cariño de este grupo de amigos ha ayudado a hacerme la persona que soy, no se si buena o mala, inteligente o estúpido, amable o arisco, seguramente un poco de todo. La verdad es que mi vida tiene un antes y un después del programa MVP.

Leer esta mañana el mail de Cristina ha sido tan emocionante como la primera vez, y llevo hoy todo el día como tonto, como lejos, como ausente. Solo espero ser realmente merecedor del galardón, y que me sirva para seguir aportando cosas a esta comunidad, que con tantas creces me las ha devuelto

Cross Apply y las funciones escalares, una comparativa con el uso de funciones y como cazar lo que están haciendo realmente. Adicionalmente a lo que dice Luis, las funciones escalares no solamente pueden degradar el rendimiento cuando esas funciones a su vez llaman a tablas (en ese caso la degradacion es espectacular), sino que también lo es cuando sin ese acceso se usan como herramientas matemáticas por ejemplo (una multiplicación, operaciones con fechas o cosas así )

El motivo es que se ejecutan para cada fila, sin posibilidad de optimización y acaban sobrecargando la cpu de la máquina, y dando un mal rendimiento. Así pues.. usad la forma que dice Luis.

Por cierto, si alguno me visteis dar una charla en el developer day de ¿2009? con Fernando G. Guerrero , esto de lo que habla Luis, fué la demo que hice .

En los grupos de noticias en castellano de SQL Server un amigo preguntaba sobre como hacer un autoincremental sobre un campo texto. lo cierto es que los campos texto no tienen autoincrementales, pero su objetivo era poder generar números muchísimo más grandes que los 18 mil millones de millones que tiene un bigint. La verdad es que todos le dijimos que si tienes en una tabla por estrecha que sea, más de 18 mil millones de millones de filas, el menor de tus problemas es el autoincremental,.. pero en cualquier caso si que me dió una idea para hablar sobre las secuencias.

Las secuencias son un objeto nuevo en SQL Server Denali, que van a ayudar a que podamos tener “más de un autonumérico” por cada una de las tablas. Este artículo será una versión corta de como se pueden solucionar este tipo de problemas. De momento, lo que se me ocurrió fué lo siguiente:

1.- Crear dos campos bigint, que sean la clave primaria de la tabla

2.- Crear un campo calculado de tipo texto que sea la concatenación de ceros y el valor del campo 1 en los 18 dígitos menos significativos y la misma operación para el campo 2

3.- Comprobar cuando el campo menos significativo supera el rango de los bigint, es decir, cuando vuelve a cero.

4.- En ese caso incrementar en uno el campo más significativo.

Creo que con esta técnica el campo calculado responde a la pregunta de nuestro amigo, aunque realmente no tenga más que una utilidad educativa y no práctica (en mi humilde opinión)

Como quiera que  un trozo de código vale más que mil palabras, aquí está la solución que propongo.

create sequence SecuenciaA as bigint start with 1 increment by 1 no maxvalue cycle;
create sequence Secuenciab as bigint start with 0 increment by 1 no maxvalue;
go
create table t (id1 bigint not null ,id2 bigint not null, datotest varchar(10),
campotexto as right('000000000000000000'+ cast( id2 as varchar(19)),19)+
right('000000000000000000'+ cast( id1 as varchar(19)),19) , constraint pk_t primary key (id1,id2))
go
create procedure insertaent @datotest varchar(10)
as
begin
  declare @valor1 bigint
  declare @valor2 bigint
  select @valor1=NEXT VALUE FOR SecuenciaA;
  if @valor1=0 
    select @valor2=NEXT VALUE FOR secuenciaB;
  else
   select @valor2=cast(current_value as bigint) FROM sys.sequences where name='SecuenciaB';
  insert into dbo.t values (@valor1,@valor2,@datotest);
end;
go
exec insertaent 'hola'
go
SELECT * FROM DBO.t
go
drop procedure insertaent;
drop table t;
drop sequence secuenciaA;
drop sequence secuenciaB;

Es muy común que para desarrolladores de base de datos, incluso los más instruidos, no necesiten nunca o casi nunca usar el sistema de automatización que proveen todos los productos de microsoft. Generalmente todas las herramientas (SQL Server Management Studio es un ejemplo), no son más que interfaces que usan esa funcionalidad, que por otra parte está disponible para cualquier desarrollador con un poquito de inquietud y una necesidad de emular esos comportamientos.

Lo que os dejo hoy son dos links, uno más general sobre como usar estas funcionalidades desde scripts de windows, y un segundo enlace que va orientado a aquellos desarrolladores que quieren usar estas funcionalidades desde dentro de TSQL, no es que sea una practica a hacer en todos los casos, pero es posible que nos saque de un apuro alguna vez y eso siempre se agradece. Así que si quereis podeis seguir este enlace . En el vereis como conseguir la generación de un script de una tabla (por ejemplo) desde TSQL.

A veces hay que leer no solo lo que la gente usa como buenas prácticas, sino también lo que se usa como malas prácticas. En este caso Luis nos cuenta algun caso que seguramente se habrá encontrado por ahí, y su analisis sobre la fragmentación de los heaps y como alguna gente intenta evitarla, sin demasiado éxito y consumiendo recursos.

Totalmente recomendable como lectura

Artículo de BI Corner, Mitos y leyendas, defragmentacion de heaps

Aunque es un artículo para SQL 2005 lo cierto es que vale para 2008 también. La historia es que dado que la autenticación de Analisis Services es siempre basada en windows, se presentan problemas como por ejemplo que alguien que no esté en el dominio tenga que acceder a los datos. O que alguien, aunque tenga un usuario del dominio, necesite ver los datos desde casa.

La problematica es doble, de un lado, garantizar la seguridad, de otro, que los datos, usuario y password viajen encriptado. La solución es habilitar el acceso a través de IIS y delegar esa parte en IIS. Se securiza el sitio con https a través de un certificado y simplemente se sigue el paso a paso que indica este artículo. Si tu versión es más moderna, aquí lo tienes con IIS 7.0