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.
