Sincronizando tablas con SQL Server Integration Services

Sincronizando tablas con SQL Server Integration Services

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

7 Comentarios en “Sincronizando tablas con SQL Server Integration Services

  1. Muchísimas gracias Miguel !! Ha estado excelentemente bien expuesto. Es muy didáctico. Gracias !!

  2. Excelente tutorial, me ayudó mucho, solo me queda una duda: como sería para los NUEVOS, en el caso que la clave primaria tuviese identidad tanto en el origen como en el destino y se quisiera mantener sincronizadas ambas claves.

  3. como poder hacer un control de concurrencia en un procedimiento almacenado que permita hacer un insert into
    solo necesito hacer el lock (control de concurrencia)

  4. Y qu├® ser├¡a m├ís r├ípido, hacer este procedimiento o borrar todos los datos de la tabla destino e ingresar todos los datos de la tabla origen.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *