Filas de Longitud Variable - Flat File

Un archivo plano de filas de longitud variable es un archivo de origen de texto. Puede ser un archivo de valores separados por coma (CSV) o un archivo delimitado por tabuladores (TDF). Puede ser un archivo de longitud fija donde las columnas son identificadas por posición o por ordinales. La principal diferencia entre un archivo plano "normal" y un archivo plano de filas de longitud variable es que el número de posiciones de texto es fijado en un archivo plano normal y ese número puede cambiar con cada fila en un archivo plano de longitud variable.

Veamos un ejemplo de un archivo plano de longitud variable (FilasLonVar.csv)

TipoRegistro,Nombre1,Valor1,Nombre2,Valor2,Nombre3,Valor3

A,Varios Uno,11

B,Varios Dos,22,Varios Dos A,23

C,Varios Tres,33,Varios Tres A,34,Varios Tres B,345

A,Varios Cuatro,44

C,Varios Cinco,55,Varios Cinco A,56,Varios Cinco B,567

B,Varios Seis,66,Varios Seis A,67

Hay siete columnas posibles: TipoRegistro, Nombre1, Valor1, Nombre2, Valor2, Nombre3, Valor3. No todas las filas contienen siete valores. De hecho, la primera fila contiene sólo tres valores:

A, Varios Uno,11

En este formato, el TipoRegistro está en la primera columna y esto indica cuántas columnas de datos se esperan en la fila. Las filas de TipoRegistro A contienen tres valores, las filas de TipoRegistro B contienen cinco valores, y las de TipoRegistro C contienen siete valores.

Leyendo un Data Flow

Es típico cargar los datos desde un archivo plano en un Data Flow SSIS usando un administrador de conexión Flat File. Caminaremos a través de la configuración de un administrador de conexión Flat File para este archivo.

Para empezar, agregue un nuevo paquete SSIS llamado FilasLongitudVariable.dtsx a su proyecto SSIS. Agregue una tarea Data Flow al Control Flow y abra el Editor Data Flow (Pestaña). Arrastre un adaptador de origen Flat File en la superficie de la tarea Data Flow y abra su editor. Haga clic en el botón New para crear un nuevo administrador de conexión Flat File.

Nombré a su administrador de conexión Flat File Archivo Longitud Variable. Crear un archivo de texto con los datos del ejemplo anterior y llámelo FilasLonVar.csv. Guarde y navegue a esa ubicación para la propiedad File Name. También marque la casilla de verificación Column Names in the First Data Row. Cuando hago clic en la página Columns, el Flat File Connection Manager Editor aparece como se muestra en la figura siguiente.

 

Este comportamiento es diferente de las ediciones anteriores de SSIS. En versiones anteriores, el administrador de conexión Flat File lanzaría un error.

Tipos de registros Splitting

Gracias a la nueva funcionalidad en la versión SSIS 2014 del administrador de conexión Flat File, tenemos todos los datos entrando como filas separadas. Pero las filas de datos contienen información de diferentes tipos. Las filas necesitan ser filtradas basadas en el tipo de registro. Ahora necesitamos analizar los datos como fluyen a través de la tarea Data Flow. Hay un par de maneras de abordar esto, pero lo veremos con la transformación División Condicional.

Arrastre una transformación División Condicional en la superficie de la tarea Data Flow y conectar una ruta de flujo de datos desde el adaptador de origen Flat File a la División Condicional. Abra el editor de la transformación. En la columna Output Name de la grilla, introduzca TipoA. En la correspondiente Condition, arrastrar (o escribir) la columna TipoRegistro, agregando el texto =="A" (note que "A" está entre comillas dobles). Repita esto para cada tipo de registro, =="B" y =="C", como es mostrado en la figura siguiente.

 

Haga clic en el botón OK para cerrar el Conditional Split Transformation Editor. Es importante notar que esto habría requerido un componente Script en versiones anteriores de SSIS porque el administrador de conexión Flat File en las versiones anteriores no podría analizar los archivos que contienen filas con un número variable de columnas.

Terminando el Flujo

Puede utilizar varios componentes de flujo de datos para terminar una ruta de flujo de datos. En un entorno de producción, esto sería probablemente un adaptador destino OLE DB. En un entorno de desarrollo o prueba, es posible que desee terminar con un componente que no requiera conectividad de base de datos o la creación de objetos de base de datos, en este caso usaremos destinos Flat Files.

Puede usar cualquier componente que no requiera de configuración para ejecutarse y tener éxito en la tarea Data Flow, tales como una transformación Columna Derivada o Multidifusión. Aquí, utilizará transformaciones Multicast para terminar el flujo de la ruta Data Flow.

Arrastre tres transformaciones Multicast en la superficie de la tarea Data Flow. Conecte una salida de la transformación Conditional Split a la Multicast TipoA. Cuando se le solicite, seleccione el búfer de salida TipoA en la ventana Input Output Selection, como se muestra en la figura siguiente.

 

Repita este proceso para las conexiones TipoB y TipoC. Cuando haya terminado, su flujo de datos podría aparecer como se muestra en la figura siguiente.

 

Primero crearemos 3 archivos planos vacíos en nuestro directorio con el blog de notas, los podemos llamar DestinoA.csv, DestinoB.csv y DestinoC.csv.

Arrastre un destino de Archivo Plano a la superficie de la tarea Data Flow. Conecte la salida de la transformación Multidifusión al destino de Archivo Plano; editamos el destino Flat File, lo llamamos DestinoA y cargamos el archivo DestinoA.csv del directorio.

De la misma forma arrastramos dos destinos Flat File para el DestinoB y DestinoC y los configuramos adecuadamente.

La figura siguiente nos muestra cómo se mostrará.

 

Vamos a ejecutarlo, la ejecución debería tener éxito, y cuando lo hace, los resultados serán marcados en verde como se ve en la figura siguiente.

 

La siguiente figura nos muestra el resultado del archivo de texto DestinoA.csv.

Leer Mas

Cargando Varios Flat File en Paralelo

Problema

Desea cargar los datos de varios archivos planos estructurados de forma idéntica, desde un único directorio, más rápido de lo que podía cargar los archivos en secuencia estricta.

El siguiente archivo es Stock01.csv, se debe crear con datos otros datos, pero con la misma estructura, Stock02.csv, Stock03.csv y Stock04.csv.

 

Solución

Cargar los datos en paralelo, como sigue.

1.    Crear una tabla en SQL Server para mantener los nombres de archivo con el siguiente DDL:

USE BDBlog;

GO

CREATE TABLE dbo.CargaParalelaSimple

(

 ID INT IDENTITY(1,1) NOT NULL,

 FileName VARCHAR (250) NULL,

 ProcessNumber AS (ID%(4))

) ;

GO

2.    Crear una tabla en SQL Server para contener los datos, una vez cargados, utilizar la siguiente DDL:

CREATE TABLE dbo.StockParalelo

(

 ID bigint IDENTITY(1,1) NOT NULL,

 Make VARCHAR (50) NULL,

 Marque VARCHAR(50) NULL,

 Model VARCHAR (50) NULL,

 Colour TINYINT NULL,

 Product_Type VARCHAR (50) NULL,

 Vehicle_Type VARCHAR (50) NULL,

 Cost_Price NUMERIC(18, 2) NULL

);

3.    Crear un nuevo proyecto SSIS (llámelo CargarVariosFlat) y renombre el paquete por defecto a ProcesoParaleloSimple. Añadir dos administradores de conexión, una OLEDB, y otra ADO.NET, que se conectan a la base de datos que va a utilizar para cargar los datos y metadatos (BDBlog en este ejemplo). Nómbrelos BDBlog_OLEDB y BDBlog_ADONET, respectivamente.

4.    Agregue las siguientes variables a nivel de paquete, así como los valores iniciales dados:

Por supuesto, debe utilizar su propio filtro de archivos y directorio de origen si no está siguiendo este ejemplo exactamente.

5.    Agregar un contenedor Sequence en el panel Control Flow, y nómbrelo Crear Tabla de Archivos a Procesar.

6.    Añadir una tarea Execute SQL en el contenedor Sequence, y nómbrelo Preparar Tabla. Dele doble clic para editarlo. Ajuste los siguientes elementos:

Connection Type: OLEDB

Connection: BDBlog_OLEDB

SQLStatement: TRUNCATE TABLE dbo.CargaParalelaSimple

El cuadro de diálogo Execute SQL Task Editor debe ser similar a la figura.

 

7.    Haga clic en OK para confirmar.

8.    Agregar una tarea Script en el contenedor Sequence bajo la tarea Execute SQL que acaba de crear. Nómbrela Bucle sobre Archivos y Escribir a Tabla y conéctela de la tarea Execute SQL Preparar Tabla.

9.    Haga doble clic para editar, ajuste el ScriptLanguage a Microsoft Visual Basic 2012 y agregue las siguientes ReadOnlyVariables:

User::FileFilter

User::FileSource

10.  Haga clic en Edit Script.

11.  Reemplace el método Main con el siguiente:

    Public Sub Main()

        Dim sqlConn As SqlClient.SqlConnection

        Dim sqlCommand As SqlClient.SqlCommand

        sqlConn = DirectCast(Dts.Connections("BDBlog_ADONET").

   AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

        Dim FileSource As String = Dts.Variables("FileSource").Value.ToString

        Dim FileFilter As String = Dts.Variables("FileFilter").Value.ToString

        Dim dirInfo As New System.IO.DirectoryInfo(FileSource)

        Dim fileSystemInfo As System.IO.FileSystemInfo

        Dim FileName As String

        Dim sqlText As String

        For Each fileSystemInfo In dirInfo.GetFileSystemInfos(FileFilter)

            FileName = fileSystemInfo.Name

            sqlText = "INSERT INTO dbo.CargaParalelaSimple (FileName)

   VALUES('" & FileName & "')"

            sqlCommand = New SqlClient.SqlCommand(sqlText, sqlConn)

            sqlCommand.CommandType = CommandType.Text

            sqlCommand.ExecuteNonQuery()

        Next

        Dts.TaskResult = ScriptResults.Success

End Sub

12.  Cierre la tarea Script de SSIS y haga clic en OK para confirmar las modificaciones en la tarea Script.

13.  Añadir una tarea Script en el contenedor Sequence bajo la tarea Script que acaba de crear. Llámela Reiniciar Variable y conéctelo de la tarea Script "Bucle sobre Archivos y Escribir a Tabla". Dele doble clic para editar esta segunda tarea Script, y añada la ReadWriteVariables CreateList y en ScripLanguage elija Microsoft Visual Basic 2012.

14.  Haga clic en Edit Script y reemplace el método Main con lo siguiente:

Public Sub Main()

    Dts.Variables("CreateList").Value = False

    Dts.TaskResult = ScriptResults.Success

End Sub

15.  Cierre la ventana Script Task de SSIS y haga clic en OK para confirmar sus modificaciones. La primera parte del paquete está ahora completa, y esta volverá a crear la lista de archivos para procesar, según sea necesario. El paquete SSIS debería ser similar a la figura.

 

16.  Ahora bien, para movernos al procesamiento paralelo actual. Agregue las siguientes variables a nivel de paquete:

 

17.  Agregar una tarea Execute SQL bajo el contenedor Sequence, conecte el contenedor a la nueva tarea, y renómbrelo a Preparar Tabla Destino. Dele doble clic para editarlo. Ajuste los siguientes elementos:

Connection Type: OLEDB

Connection: BDBlog_OLEDB

SQL Statement: TRUNCATE TABLE dbo.StockParalelo

18.  Haga clic en OK para confirmar.

19.  Añada un nuevo administrador de conexión Flat File,  nómbrelo Proceso1, y configúrelo para conectarse a cualquiera de los archivos de origen. En las propiedades de este administrador de conexión, establezca en Expressions para su ConnectionString a @[User::FileSource] + @[User::NombreArchivo_Lote_1].

20.  Añada una tarea Execute SQL bajo la tarea "Preparar Tabla Destino", conéctela a ésta nueva tarea, y renómbrela Obtener Lote 1. Dele doble clic para editarlo. Ajuste los siguientes elementos:

Connection Type: ADO.NET

Connection: BDBlog_ADONET

ResultSet: Full Result Set

SQL Statement:   SELECT FileName

FROM dbo.CargaParalelaSimple

WITH (NOLOCK)

WHERE ProcessNumber = 1

21.  En la pestaña Result Set, dar clic en Add, en la columna Result Name colocar 0 y en la columna Variable Name colocar User::Lote_1

22.  Haga clic en OK para confirmar.

23.  Añadir un contenedor Foreach Loop bajo la tarea "Obtener Lote 1", conéctela a la nueva tarea, y renómbrelo Cargar Archivos en Lote 1. Dele doble clic para editarlo. Ajuste los siguientes elementos:

Enumerator: Foreach ADO Enumerator

ADO Object Source Variable: User::Lote_1

Variable: User::NombreArchivo_Lote_1  (en Variable Mappings)

24.  Haga clic en OK para confirmar.

25.  Añadir una tarea Data Flow  y llámela Lote 1, dentro del contenedor Foreach Loop, y configúrelo para conectar el administrador de conexión Flat File Proceso1 a la tabla de destino (StockParalelo) utilizando el administrador de conexión BDBlog_OLEDB. Asegúrese de que TABLOCK está seleccionado.

26.  Repita los pasos 20 a 25 tres veces (una vez para cada carga paralela adicional). Configurar Obtener Lote 2 (con ProcessNumber=2, User:Lote_2), Obtener Lote 3 (ProcessNumber=3, User:Lote_3) y Obtener Lote 4 (ProcessNumber=0, User:Lote_4). Asegúrese de nombrar a los administradores de conexión Flat File Proceso2, Proceso3 y Proceso4 y configurarlos adecuadamente. Nombre los contenedores Foreach Loop Cargar Archivos en Lote 2, Cargar Archivos en Lote 3 y Cargar Archivos en Lote 4 y configurarlos adecuadamente. El paquete final debe ser similar a la figura.

 

27.  Ahora puede ejecutar el paquete.

Funcionamiento

Este ejercicio se basa en un escenario donde recibe muchos archivos planos, todos con el mismo formato, y todos necesitan ser cargados en la misma tabla. Claramente puede procesarlos secuencialmente, pero el procesamiento en paralelo debería demostrar ser más rápido en la mayoría de los casos. La ganancia de velocidad generalmente vale la pena el esfuerzo extra requerido para crear un paquete SSIS más complejo.

En este ejercicio, llevamos a cabo la operación de carga en dos partes bien diferenciadas:

·         En primer lugar, el ciclo a través de los archivos en un directorio y almacenar las rutas de archivos en una tabla de SQL Server.

·         En segundo lugar, carga los archivos usando esta tabla como el origen de los nombres de archivo, donde cada tarea carga archivos separados iterados a través de los archivos que han sido asignados independientemente de los otros archivos que serán cargados.

Es importante separar las dos partes, y primero atribuir los números de proceso para los archivos de origen así que cada rama del flujo del proceso sólo carga su propio conjunto de archivos, como cada proceso es separado de cualquier otro proceso. Puede pensar en él como dar "flags" a cada archivo para decirle cuál es el camino a seguir.

Se puede usar un recordset ADO.NET como parte de esta técnica, pero prefiero introducir otro método que consiste en utilizar una tabla de base de datos persistente. Esto parece preferible porque usando una tabla le permite almacenar la lista en el disco, y puede proporcionar una base para el proceso de registro y de lote.

Tenemos que tener claro sobre exactamente lo que este paquete logrará. Simplemente dividirá todos los archivos de origen en lotes (en este caso utilizará cuatro, cada uno de los cuales será un proceso paralelo aparte) y los procesara todos. No se intentará realizar cualquier balance de carga u ordenamiento de proceso. Por tanto, este enfoque funciona bien cuando todos los archivos con del mismo tamaño, y no se está buscando la secuenciación óptima. Es, sin embargo, atractivo en su simplicidad, tanto conceptual como práctica. La técnica utilizada para definir qué archivo es atribuido a que proceso es utilizar una columna Identity de SQL Server y, entonces, aplicar "% 4" para crear los números de 0 a 3 (el resto cuando se aplica el operador de módulo) como una columna ProcessNumber separada a cada archivo sucesivamente. En este ejemplo, la atribución del hilo del proceso es definido como una columna calculada, para evitar los pasos adicionales. La tabla de nombres de archivos y números de lote es entonces utilizada como la lista de orígenes para la carga en paralelo. Por supuesto, no necesita utilizar cuatro procesos paralelos, puede utilizar más o menos. ¿Cuántos procesos son necesarios para una carga óptima de datos podría requerir pruebas considerables de su parte en una configuración idéntica a su entorno de producción.

A modo de extender la funcionalidad, se agregará un ajuste para reutilizar o truncar, la lista de archivos para procesar, el cual permitirá reprocesar una lista definida de archivos, si los datos de origen cambian. Esto es para lo que la variable CreateList es utilizada. Cuando el paquete es cargado, esta variable es establecida a True, por lo que la lista inicial es truncada en la tabla SQL Server CargaParalelaSimple, luego, será repoblada. Después que la tabla es poblada, la variable es establecida a False, así la tabla persiste, a menos que necesite volver a crearla. Esto le permite pasarla en la variable de la línea de comandos o desde un job del SQL Server Agent, y controlar el reprocesamiento.

Si este proceso parece bastante complejo, entonces sugiero que eche un buen vistazo a la captura de pantalla de todo el trabajo que se encuentra en la figura anterior. Espero que esto haga que las cosas se vean un poco más claras.

·         Nota: Es esencial comprobar la casilla de verificación Tablock en la tarea de destino OLEDB, ya que esto permitirá que las cargas masivas en paralelo tengan lugar. El no hacer esto ralentizará la carga considerablemente. En general, la base de datos de destino no debe estar en modo de recuperación FULL, para cargas paralelas eficientes. Además, no debe haber ningún índice nonlustered definido.

Leer Mas

Importando Flat File de Ancho Fijo

Problema

Desea importar un archivo de texto de ancho fijo en SQL Server.

Solución

Crear un paquete SSIS y definir un Data Flow usando un administrador de conexión Flat File configurado para aceptar archivos de ancho fijo. A continuación se explica cómo se puede hacer esto.

1.    Crear un paquete SSIS, preparando un origen Flat File y un destino OLEDB.

2.    Crear un administrador de conexión OLEDB conectando a BDBlog_Staging y llámelo BDBlog_Staging_OLEDB.

3.    Crear un administrador de conexión para el archivo plano, dando clic derecho en la pestaña Connection Managers y seleccionando New Flat File Connection.

Archivo de Texto: StockAnchoFijo.txt

4.    Una vez que haya especificado el nombre de la conexión y el archivo de origen (C:\SSIS\Archivos\StockAnchoFijo.txt), especificar que el formato es de ancho fijo, como se muestra en la figura.

 

5.    Visualice el panel Columns haciendo clic en Columns en la izquierda de la caja de diálogo. Debería ver algo como en la figura.

6.    Introduzca el ancho de fila. Si no lo sabe, pruebe con un numero grande! El marcador de fila rojo saltará a la posición que ha introducido. Siempre puede afinar la longitud basado en los datos que pueden ver previamente en la cuadrícula Source Data Columns del panel Columns del Flat File Connection Manager Editor.

7.    Desplácese hacia la izquierda y la derecha a través de los datos. Agregue los delimitadores de columna que necesite haciendo clic en la regla. Debe terminar con algo como en la figura.

8.    Haga clic en OK para confirmar los ajustes de Connection Manager. Ahora puede agregar el administrador de conexión al origen Flat File (a menos que lo esté creando como parte de la definición del origen Flat File).

9.    Conecte el origen Flat File a la salida OLEDB.

10.  Configure la salida OLEDB para utilizar el administrador de conexión BDBlog_Staging_OLEDB.

11.  Añadir las asignaciones de columna y seleccione o cree una tabla de datos y ejecute el paquete de importación.

Funcionamiento

En ocasiones, puede que tenga que hacer frente a un archivo de ancho fijo que requiere importar a SQL Server. Esto es muy similar a un archivo delimitado, excepto que tiene que decirle a SSIS donde la columna se rompe. Todo lo que sabe es que el archivo de origen es completamente de ancho fijo, que tiene las siguientes características:

·         Todas las filas tienen el mismo ancho.

·         Todas las columnas son de un ancho fijo (que puede variar para cada columna, por supuesto).

·         No existe Retorno de Carro / Avance de Línea (CR/LF) al final de cada fila.

Esto es muy similar a importar un archivo delimitado. Como es el caso de los archivos delimitados, se puede establecer lo siguiente:

·         Sea o no que la primera fila contenga los nombres de columna

·         Las filas de cabecera para saltar

·         El delimitador de texto

·         Los nombres de columnas

·         Los tipos de datos de columnas

·         Anchos de columnas de salida

Puede haber momentos en que necesite importar un archivo plano donde todas las columnas son de ancho fijo a excepción de la última, que puede ser de longitud variable o donde cada fila termina con un CR/LF. Esto también se conoce como archivo de texto ragged right. Esto hace a un archivo desigual por la derecha esencialmente un archivo de texto de ancho fijo con una importante, y fundamental, diferencia. Cada fila tiene una CR/LF al final. En todos los demás aspectos, el proceso de importación es el mismo que para los orígenes de ancho fijo.

Leer Mas