Si alguna vez tu jefe te pide que escribas un único DTS que sea capaz de atacar tanto a SQL Server como a Oracle… ¡trata de disuadirle! Si no lo consigues y tienes que emprender la labor, tal vez este post te ayude.
El otro día me pidieron precisamente esta misión imposible: modificar un DTS para Integration Services escrito para importar datos desde una base de datos SQL Server, para que pudiese importar datos desde una Oracle con un mismo esquema relacional. Hoy he conseguido que funcione en ambas bases de datos sin más cambios que la cadena de conexión… así que allá va la receta que tal vez no te asegure el éxito, pero que estoy seguro de que te facilitará las cosas.
- El primer consejo es sencillo: Utiliza DataSources en lugar de usar directamente orígenes OleDB. En un DataSource puedes cambiar mil veces la cadena de conexión, que no afectará a tu ETL. En un origen OleDb "normal" SSIS no deja cambiar el cliente sin tener que eliminar el origen completo y crear uno nuevo. Si no puedes usar un DataSource, hay un pequeño truco que te puede ayudar: modificar directamente la propiedad "ConnectionString" desde el diálogo de propiedades del control (sin hacer doble click en el nodo). Los DataSources no existían en los DTS de SQL Server 2000… son una nueva abstracción de SQL Server 2005 que te permiten definir una conexión a la que luego poder referirte desde tantos DTS como quieras. Alterando el DataSource, ¡estás alterando todos los orígenes de datos que hacen referencia al mismo!
- Nunca referencies directamente una tabla. Utiliza siempre sentencias SQL. Las referencias a tablas están bien para hacer pruebas y prototipar tu ETL, pero el ETL definitivo jamás debe leer directamente una tabla. El primer motivo ya lo he expuesto alguna que otra vez… seguro que no usas todos los campos de la tabla… ¿entonces para qué te los traes a SSIS? No desperdicies buffers cargando datos que no vas a usar. Además, si vas a usar Oracle y SQL Server, ten en cuenta que los nombres de las tablas se especifican de forma diferente en ambas bases de datos. Puedes llevarte la desagradable sorpresa de tener que cambiar todas las referencias a tablas del formato [schema].[tabla] a "TABLESPACE"."TABLA".
- En las fuentes de datos de las que vayas a leer de tablas Oracle, y pienses traerte campos de texto, pon a "true" la propiedad "AlwaysUseDefaultCodePage" y establece a mano la tabla de códigos usada por Oracle. Los proveedores OleDB de Oracle no proporcionan esta información a Integration Services, y con esta operación evitarás molestos warnings (que por otra parte, es seguro ignorar si se ha establecido la tabla de códigos por defecto en la fuente de datos).
- No hagas cálculos en las SQL. En lugar de usar funciones SQL, utiliza la transformación "Derived Column" de SSIS. Además de evitar líos con funciones incompatibles entre los distintos tipos de datos, evitarás que errores en las funciones queden enmascarados (prueba a poner en una SQL una función que divida un número por cero, y observarás que el error queda totalmente oculto).
- Si tienes que atacar a Oracle 8, recuerda que no soporta la sintaxis estándar ANSI de la Left Outer Join. Si tienes costumbre de usar este tipo de Join para limpiar aquellos datos en los que se ha roto la integridad referencial, usa en su lugar la transformación "Lookup" de SSIS. Es menos eficiente, pero es el precio que hay que pagar por al compatibilidad.
- En tiempo de diseño, conecta el DataSource a una base de datos SQL Server en lugar de Oracle. SSIS necesita acudir a la fuente de datos para cargar los metadatos externos. Mi experiencia es que esto va mejor con SQL Server, y que te evitas malas pasadas del generador de consultas (que te genere, por ejemplo, nombres de tabla totalmente calificados con el formato de Oracle).
- Si hay ligeras variaciones en los tipos de datos, usa la transformación Convert de SSIS justo inmediatamente después de leer los datos de la base de datos. Si haces esto, pon a "false" la propiedad "ValidateExternalMetadata". De esta forma, SSIS no validará los metadatos que cargó en tiempo de diseño contra los metadatos de la base de datos que actualmente tiene conectada. O sea, si en SQL Server se están usando tipos NVARCHAR y en Oracle VARCHAR, se podrá cambiar de una base de datos a otra sin que SSIS se queje por el cambio. La conversión debe realizarse justo después de la carga de los datos, y a partir del nodo conversor, el resto de nodos deben validar los metadatos externos. Los metadatos se usan, entre otras cosas, para que SSIS reserve espacio a sus buffers, así que es imprescindible que los metadatos establecidos en el flujo de datos coincidan con la realidad ANTES DE QUE SE REALICE LA PRIMERA TRANSFORMACIÓN ASÍNCRONA (Sort, Merge, Merge Join, Multicast… etc). Recuerda que SSIS genera un buffer con cada transformación asíncrona, así que si en ese preciso instante, los metadatos no están establecidos correctamente, Integration Services fallará.
- Esta última técnica que he descrito no funciona con las transformaciones "Lookup". Vamos, que en mi caso al menos, por más que establezca a "false" la propiedad "ValidateExternalMetadata", sigue fallando la Lookup al cambiar el origen de datos (SSIS 2005 con SP1). No problemo: se puede sustituir la Lookup por la lectura desde un origen OleDB junto con la transformación Merge.
Espero que con estos consejos y un poco de esfuerzo, seas capaz de sorprender a tu jefe.
¡Felices transformaciones!