21 mayo 2015

Importar un Flat File Delimitado y Determinar Tipo de Datos Automático

Importando un Flat File Delimitado
Problema
Desea importar un archivo plano sobre una base regular y/o aplicar la transformación de datos como parte de un proceso ETL estructurado.
Solución
Crear un paquete SSIS para importar el archivo. Así es como se hace:
Archivo de texto: Invoices.txt
1.    Crear un nuevo proyecto SSIS, o abrir un proyecto existente, y agregar un nuevo paquete.
2.    Agregar una tarea Data Flow en el panel Control Flow. Editar esta tarea al hacer cualquiera de lo siguiente:
·         Doble clic.
·         Clic derecho y seleccionar Edit.
·         Clic en la ficha Data Flow.
3.    Agregue un origen Flat File de los orígenes Data Flow en la caja de herramientas. Haga doble clic en el origen Flat File para editarlo. Debería ver el cuadro de diálogo mostrado en la figura.
 
4.    Haga clic en New para especificar la conexión de archivos, que abre el Flat File Connection Manager Editor. Busque y seleccione el archivo de origen (o escríbalo si lo prefiere). En este ejemplo, es C:\SSIS\Archivos\Invoices.Txt. Especifique si los nombres de columna están en la primera fila, ingresar el calificador de texto y seleccione el delimitador de fila (o dejar los valores por defecto). Debe terminar con algo como lo que se ve en la figura.
 
5.    Haga clic en Advanced. Para cada columna de salida, definir el tipo de datos y la longitud. El cuadro de diálogo debe ser similar a la de la figura.
 
6.    Haga clic en OK dos veces para confirmar y volver al panel Data Flow.
7.    Agregue un destino OLEDB de la caja de herramientas al panel de Data Flow, y conecte la salida (la flecha verde) del origen de texto al destino OLEDB.
8.    Haga doble clic en el destino OLEDB para editarlo. Haga clic en New para agregar un administrador de conexión OLEDB que configure para conectarse a la base de datos de destino (BDBlog_Staging en este ejemplo). Como alternativa, puede seleccionar un administrador de conexión existente.
9.    Seleccione la tabla de destino (dbo.Invoice en este ejemplo). Alternativamente, haga clic en New para crear una nueva tabla, que está diseñada de forma automática para asignar a la estructura de origen percibida. Puede cambiar el nombre de la tabla de destino del nombre sugerido por SSIS si lo prefiere. Confirme con OK.
10.  Haga clic en Mappings de la lista del lado izquierdo del cuadro de diálogo OLE DB Destination Editor. Asegúrese de que los datos de origen se asignan a las columnas de destino.
11.  Haga clic en OK.
12.  Haga clic en Debug Start Debugging para importar los datos del archivo de texto.
SSIS importará sus datos.
Funcionamiento
En el mundo real de la importación de datos, SSIS es probablemente la herramienta que más se utilizará. No obstante, siempre ayuda si se puede abrir el archivo de origen para obtener un vistazo de su contenido, o se le ha informado de su contenido lo suficiente como para saber
·         Si contiene encabezados de columna o no.
·         Si los tipos de datos y longitudes son para cada columna de datos.
·         Que el archivo siempre contenga el mismo número de delimitadores de columna.
Si no tiene la información esencial acerca de los datos en el archivo de texto disponible, sin embargo, entonces puede recoger metadatos para el archivo de origen de SSIS mediante la vista previa de los datos y conseguir que SSIS sugiera los tipos de datos. Asumiendo que tiene esta información, SSIS le ayudará a través del proceso de importación de un archivo de texto. En el caso de archivos planos que no son demasiado grandes, puede abrir el origen en un editor de texto y tratar de deducir los metadatos directamente.
Nota: Si no se especifica ningún tipo de datos al configurar la tarea Data Flow, SSIS asume que cada columna es VARCHAR(50).
Definir y ajustar los tipos de datos como el paso a través de SSIS puede significar un montón de trabajo. Afortunadamente, SSIS tiene maneras para ayudarle en esto. Curiosamente, el verdadero trabajo se hace en el administrador de conexión Flat File, no en la tarea de Origen de Datos.
Si encuentra dificultades, la primera cosa a tener en cuenta es, probablemente, las especificaciones de archivos de texto básicas. Si nos fijamos en el cuadro de diálogo Flat File Connection Manager, que se muestra en el paso 4 del ejercicio, podrá ver los elementos que figuran en la tabla en el panel General.
Text Qualifier. Indica que los datos (de cualquier tipo) están encerrados entre comillas. Esto podría ser, por ejemplo, porque el dato textual contiene el delimitador de columna (con frecuencia una coma). De esta manera, tales caracteres son manejados elegantemente. En cualquier caso, las comillas son eliminadas durante el proceso de importación, obviando la necesidad de eliminarlas después de la importación.Las siguientes son las que probablemente necesitará con más frecuencia:
Column names in the first data row. Indica que la primera fila se compone de la cabecera de las columnas. Como el nombre indica, la primera fila de los datos es tomada para ser los nombres de columna, y, en consecuencia, no son importadas.
Determinar Automáticamente los Tipos de Datos
Problema
Quiere deducir la estructura de los datos en un archivo plano de origen.
Solución
Use la opción de SSIS Suggest Types en el administrador de conexión Flat File.
Se explica el enfoque simple para conseguir una aproximación razonable de los tipos de datos reales y longitudes en un archivo de texto de origen. Esto también se hace usando el panel avanzado del Flat File Connection Manager Editor. Como este se basa en el ejercicio anterior, para evitar la repetición, es necesario haber leído el ejercicio anterior, antes de ver este ejercicio.
1.    Seguir los pasos del 1 a 4 del ejercicio anterior.
2.    Clic en Advanced.
3.    Clic en Suggest Types. Aparecerá el cuadro de diálogo Suggest Column Types, como se muestra en la figura.
4.    Alterar cualquier opción que desee modificar (por ejemplo, el número de filas puede ser demasiado pequeño para una muestra exacta de un archivo grande). Haga clic en OK.
Verá que todos los tipos de datos (y longitudes, donde sea apropiado) han sido mostrados y ajustados en el panel Advanced del cuadro de diálogo.
Funcionamiento
Probablemente ha notado que al importar archivos de texto, el administrador de conexión Flat File asume que todas las columnas de origen en un archivo de texto delimitado son cadenas con una longitud de 50 caracteres. Esto puede ser demasiado restrictivo por varias razones:
·         Muchos campos no son cadenas y consecuentemente requieren la conversión del tipo de datos como parte del paquete SSIS.
·         La longitud sugerida de 50 caracteres es insuficiente para muchos campos de cadena, y puede causar errores de paquete en tiempo de ejecución.
·         Incluso si todas las columnas de origen pueden caber en 50 caracteres, esto es frecuentemente demasiado amplio, ya que reduce el número de registros que pueden caber en el búfer del pipeline de SSIS y, en consecuencia, ralentiza el proceso de carga.
Con frecuencia, por supuesto, el problema es resuelto por las personas que proporcionan los archivos de origen, quienes han entregado meticulosamente una completa descripción de los tipos de dato del contenido del archivo. Sin embargo, puede haber ocasiones en que esto no sea el caso, y tiene que deducir, descubrir o adivinar los tipos de campo (o columna si lo prefiere) y longitudes en un archivo de origen. Esto puede ser un proceso engorroso, especialmente si significa un ciclo basado en prueba y error de carga y recarga de un archivo de texto, hasta que por fin se encuentre, para la columna de cada origen, un tipo de datos aceptable. En consecuencia, una solución mucho más simple es pedir a SSIS adivinar los tipos de datos y longitudes por nosotros. Cierto es que, puede abrir algunos archivos para mirar los datos de origen. Una rápida mirada es rara vez una muestra analítica precisa. Es más, habrá algunos archivos planos de origen que son tan grandes que tardarían una eternidad para abrirlos o simplemente colapsará su editor de texto favorito.
Ayuda a entender sus opciones para determinar los tipos de datos en el archivo de origen, conforme a lo dispuesto en la tabla.
Suponiendo que sabe qué columna necesita establecer a qué tipo de datos, así como establecer un delimitador de columna específica para una o más columnas, es posible que desee afinar los tipos de datos utilizando el panel Advanced. La tabla describe las opciones disponibles.
Por ejemplo, si desea anular los valores actuales para un tipo de datos de una columna, puede establecer el tipo de datos.
Podría cambiar de datos de origen, no tendrá que rehacer toda la estructura de asignación de columna desde cero, como SSIS le permite agregar o quitar columnas para ajustar el paquete a los cambios en la estructura de datos de origen.
Para agregar una columna, haga lo siguiente:
1.    Clic en la columna que precede (o sigue) a la columna a insertar.
2.    Clic en la flecha de la derecha del botón New. Seleccione Insert Before (o After). Una nueva columna es agregada a la estructura de mapeo.
Al hacer clic en New o Add Column se inserta una nueva columna después de las columnas existentes. Eliminar una columna es tan simple como lo siguiente:
1.    Seleccione la columna a remover.
2.    Haga clic en Delete.
El panel Advanced del administrador de conexión Flat File también le permite especificar, para cada columna, si la columna está encerrada entre comillas. Todo lo que tiene que hacer es configurar TextQualified a True, si este es el caso, y False si no hay comillas.

02 mayo 2015

Asistente Import/Export – Tarea Bulk Insert

Asistente Import/Export

Este asistente proporciona una sencilla ETL y es fácil de usar para las operaciones básicas de transferencia de datos. Con este asistente puede elegir un origen, un destino y mapear columnas con pocas restricciones sobre las opciones de transferencia de datos.

Alistarse

Necesita tener las bases de datos de muestra AdventureWorks2014 y AdventureWorksLT2014 instalados.
Crear una nueva base de datos en SQL Server. Abra el SQL Server Management Studio (SSMS) desde el menú Inicio | Microsoft SQL Server 2014 | SQL Server Management Studio. En el SSMS, conéctese a la instancia del equipo local y cree una nueva base de datos. Nombre esta base de datos como AIE_SSIS.
Tenga en cuenta que debe ejecutar SSMS como administrador, para ello simplemente haga clic derecho en SQL Server Management Studio de la ruta anterior y seleccione Ejecutar como administrador.

Ejercicio

1.    Abra el Asistente Import/Export; hay tres maneras que puede hacerlo:
·         En la ventana Ejecutar, escriba DtsWizard.
·         Abra el asistente desde la siguiente dirección: Inicio | Todos los programas | Microsoft SQL Server 2014 | Importación y Exportación de Datos.
·         En SSMS, haga clic derecho en cualquier base de datos y, a continuación, en Tareas, seleccione Importar datos o Exportar datos.
2.    En el primer paso del Asistente Import/Export, aparecerá una página de bienvenida. Haga clic en Next para entrar a Seleccionar un Origen de Datos, en este paso se debe elegir de donde viene el Data Source. El Data Source puede ser cualquier origen; de una base de datos Oracle o SQL Server para cualquier otra base de datos, archivos planos (como .txt y .csv) o incluso archivos Excel, la gama de orígenes y destinos está basada en los proveedores de datos instalados en la máquina. Para este ejemplo, deje la opción Data Source como su opción por defecto que es SQL Server Native Client 11.0.
3.    Queremos exportar dos tablas de la base de datos AdventureWorks2012 a otra base de datos. Por lo tanto, dejar Server name como (local) o un solo punto (.) o si tiene una instancia con nombre podría utilizar .\<Instance-Name>, y en la sección Autenticación deje el tipo de autenticación como la Autenticación de Windows. Esta opción usará tu cuenta de Windows para conectarse a la base de datos, por lo que, obviamente, la cuenta de Windows debe tener acceso de lectura a la base de datos originaria.
4.    En el cuadro desplegable Base de datos, seleccione AdventureWorks2014 de la lista. Luego haga clic en Siguiente y vaya al siguiente paso. 
5.    El siguiente paso es requerido para elegir un Destino, por lo tanto, proporcionar los detalles de conexión del destino de datos (los tipos de destinos pueden diferir de bases de datos a archivos planos). Para este ejemplo dejar el destino como el valor por defecto que es SQL Server Native Client 11.0.
6.    Establecer el Nombre de servidor a (local) o a un punto (.) Para conectar la instancia predeterminada de la máquina actual. Establezca la Autenticación como la Autenticación de Windows. Seleccione AIE_SSIS en la lista desplegable Base de datos. Luego haga clic en Siguiente para continuar los pasos del asistente.
7.    En el paso Especificar copia de tabla o consulta puede elegir entre seleccionar un nombre de tabla o vista para una base de datos de origen o escribir una consulta para obtener los datos de un origen. Para este ejemplo, seleccione la opción Copiar los datos de una o varias tablas o vistas.
8.    Luego, una lista de tablas y vistas de la base de datos de origen aparecerá. Para este ejemplo, seleccione HumanResource.Department, Person.Address y Production.Product. Luego haga clic en Siguiente.
9.    El paso Guardar y Ejecutar el paquete es el siguiente; que ofrece la posibilidad de guardar todos los ajustes y configuraciones que ha establecido para un paquete SSIS. Guardaremos este paquete para ver como luce el paquete SSIS. Marque la opción Guardar el paquete SSIS y haga clic en el botón Siguiente.
10.  En el siguiente paso, que es el diálogo Guardar el paquete SSIS, escriba el nombre AsistenteImportExport y elija una ubicación para el archivo de paquete. Luego haga clic en Siguiente.
 
11.  Ahora un resumen de todos los ajustes que ha hecho aparece aquí; tras revisar el resumen, haga clic en Finalizar.
12.  Después de hacer clic en el botón Finalizar, el Asistente Import/Export se mostrará y podemos ver todos los mensajes generados durante la ejecución del paquete. Se muestra el número de filas copiadas, o cualquier otra información, como el número de filas transferidas, validadas, y cualquier otra acción.
13.  Abrir y ver el informe de la ejecución haciendo clic en el botón Informe.
14.  Cierre el asistente y abra el SSMS para comprobar la base de datos de destino, se puede ver aquí las tablas transferidas con datos.

Funcionamiento

Este escenario simple exporta algunas tablas de la base de datos AdventureWorks a una base de datos vacía. En los últimos pasos, guardamos todo el escenario de transferencia de datos a un paquete SSIS en un sistema de archivos.
Con el Asistente Import/Export puede importar o exportar datos desde un origen a un destino, este es el escenario ETL más simplista. En el paso Seleccionar un origen de datos realiza la parte Extracción del ETL y obtiene los datos de la base de datos del SQL Server (origen de datos). El segundo paso, fue Seleccionar un destino, fue configurado durante la parte Carga del ETL. La Carga indica dónde se deben exportar los datos; exportamos datos a una base de datos SQL Server. En este primer escenario de ejemplo no tenemos ninguna etapa específica de Transformación.
Cuando elige tabla(s) de la base de datos AdventureWorks en la sección Seleccione Tablas o Vistas de Origen, las tablas que todavía no existen en la base de datos de destino serán creadas primero.
Después de comparar las columnas y los metadatos, los datos serán transferidos y un resumen de todos los registros mostrará lo que pasó durante la ejecución.
Guardamos este paquete para el sistema de archivos; también podemos guardar los paquetes a un SQL Server.

Bulk Insert

Bulk Insert es utilizado en escenarios donde un gran volumen de datos que debe insertarse rápidamente en una base de datos de destino. Hay varias maneras de tratar con esto bajo SSIS, pero la diferencia estará en la velocidad de insertar tales volúmenes de datos en una base de datos. Este enfoque es utilizado para asegurar la velocidad al copiar los datos o realizar las transformaciones mientras los datos están moviéndose.
Algunas restricciones deben ser consideradas antes de utilizar este enfoque:
Ø  Los datos deben ser transferidos desde un archivo de texto a una tabla o vista de SQL Server.
Ø  El destino debe ser una tabla o vista en una base de datos de SQL Server.
Ø  Sólo miembros del rol de servidor fijo sysadmin pueden ejecutar un paquete con esta tarea.

Alistarse

Asegúrese de que la base de datos AIE_SSIS esté disponible en el SQL Server Management Studio (SSMS).
En este ejercicio se requerirá la tabla Customers de la base de datos de muestra AIE_SSIS para almacenar cada valor suministrado desde el archivo plano.
Para prepararse para este ejercicio, utilice los siguientes pasos:
1.    Abra el SQL Server Management Studio (SSMS) y ejecute la sentencia SQL siguiente.
USE AIE_SSIS
GO
CREATE TABLE [dbo].[Customers](
   [ID] [int] NOT NULL,
   [Firstname] [varchar](50) NULL,
   [Lastname] [varchar](50) NULL,
   [Ammount] [decimal](18, 2) NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
   [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
2.    Abra el SQL Server Data Tools (SSDT) y cree un nuevo proyecto de SSIS.
3.    Proporcione un nombre (TareaBulkInsert por ejemplo) y una ubicación para el proyecto SSIS y proceda.
4.    Seleccione el paquete creado por defecto y renómbrelo a CargaBulk.dtsx.

Ejercicio

Considere el escenario donde SSIS necesita periódicamente importar un gran volumen de datos en un repositorio central. Esta importación debe ser lo más rápido posible y ser basado en un archivo plano producido por un Sistema Operativo (OS).
1.    Como se mencionó anteriormente, es necesario que la conexión de origen sea un archivo plano. Por lo tanto, y antes de configurar la tarea principal, debería ser creada una conexión de archivos planos.
2.    Creamos un archivo plano, lo nombramos EjemploArchivoTxt, con el siguiente contenido.
 
3.    En el panel Connection Managers, le damos clic derecho y seleccionamos New Flat File Connection.
4.    Buscamos el archivo plano EjemploArchivoTxt.txt, vamos a la pestaña Columns, mantenemos todas las propiedades predeterminadas y pulsamos OK. La renombramos como cmArchivoPlano.
5.    Agregue una tarea Bulk Insert al Control Flow para la inserción masiva de los datos de origen.
6.    Abra la tarea para la edición.
7.    En la ficha Connection del editor de la tarea, seleccione la casilla desplegable de la propiedad File y luego seleccione la conexión de archivo plano creado en el paso anterior que apunta al origen.
8.    Seleccione el valor que establece la tabla de destino Customers a la base de datos de ejemplo AIE_SSIS en el cuadro desplegable DestinationTable.
 
9.    Para ignorar la primera fila en el archivo plano que corresponde a la cabecera, cambie la propiedad FirstRow de 1 a 2.
10.  Ejecutar el paquete pulsando F5. Si la ejecución tiene éxito, ahora deben existir los nuevos datos en la base de datos AIE_SSIS.
Funcionamiento
La tarea Bulk Insert proporciona un método para insertar registros desde un archivo plano en una base de datos en modo por lotes. El número de línea de línea de inicio y el número de línea final, el delimitador de columnas, los delimitadores de fila, y algunas otras opciones son configurables.
La tarea Bulk Insert utiliza la operación integrada de inserción masiva de SQL Server.
En la conexión de destino, hicimos una conexión a una base de datos y a una tabla originaria para la operación de inserción masiva. La conexión de origen apunta a un archivo plano que es sólo necesaria para crear el administrador de conexión del archivo.
Format define la forma en que está estructurado el archivo de origen. Hay dos maneras de proporcionar el formato, y nuestra primera opción es Specify. Cuando se selecciona Specify en la propiedad desplegable Format, es posible especificar el RowDelimiter y ColumnDelimiter exactamente.
RowDelimiter define cómo son separadas las filas en el archivo de origen (por defecto, este valor es {CR}{LF}, lo que significa una nueva línea, pero es posible especificar cualquier carácter o combinación de caracteres aquí como delimitador). ColumnDelimiter define cómo se separan las columnas. Delimitadores de columnas comunes son coma (,) punto y coma (;) o una línea vertical (|), pero podemos definir cualquier otro conjunto de caracteres(s) como ColumnDelimiter.
Cuando se selecciona Use File en el cuadro desplegable Format, otra propiedad aparecerá y es posible insertar la dirección física de un archivo de formato. El formato de archivo es un archivo de sintaxis que es útil cuando se requiere cargar datos en SQL Server con el modo de inserción masiva. Para obtener más información acerca de cómo el archivo de formato luce y cómo crear un archivo de formato, echa un vistazo a http://msdn.microsoft.com/en-us/library/ms178129.aspx.
El archivo de formato es muy útil cuando es necesario definir estructuras de datos en un archivo plano de formas más avanzadas que una sola fila o columna delimitadora. Con un archivo de formato, es posible definir cada columna de flujo de datos, su tipo de dato, y algunas otras opciones avanzadas.