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.