¿Cómo puedo cargar un archivo plano grande en una tabla de base de datos usando SSIS?

No estoy seguro de cómo funciona, así que estoy buscando la solución adecuada. Creo que SSIS es el camino correcto pero nunca lo he usado

Guión:

Cada mañana, obtengo un archivo delimitado por tabuladores con 800K registros. Necesito cargarlo en mi base de datos:

  1. Obtener archivo de ftp o local
  2. Primero, necesito eliminar el que no existe en el nuevo archivo de la base de datos;
    • ¿Cómo puedo comparar datos en tsql?
    • ¿Dónde debo cargar los datos del archivo delimitado por tabuladores para compararlos con el archivo? ¿Debería usar una tabla temporal? ItemID es la columna única en la tabla.
  3. Segundo, necesito insertar solo los nuevos registros en la base de datos.
  4. Por supuesto, debe ser automatizado.
  5. Debe ser una forma eficiente sin sobrecalentar la base de datos SQL

No olvide que el archivo contiene 800K registros.

Muestra de datos de archivo sin formato:

 ID ItemID ItemName ItemType -- ------ -------- -------- 1 2345 Apple Fruit 2 4578 Banana Fruit 

¿Cómo puedo abordar este problema?

Sí, SSIS puede realizar los requisitos que ha especificado en la pregunta. El siguiente ejemplo debería darte una idea de cómo se puede hacer. El ejemplo usa SQL Server como el back-end. Algunos de los escenarios de prueba básicos realizados en el paquete se proporcionan a continuación. Perdón por la larga respuesta.

Proceso paso a paso:

  1. En la base de datos de SQL Server, cree dos tablas dbo.ItemInfo y dbo.Staging . Las consultas Crear tabla están disponibles en la sección Scripts . La estructura de estas tablas se muestra en la captura de pantalla n. ° 1 . ItemInfo contendrá los datos reales y la tabla de Staging contendrá los datos de etapas para comparar y actualizar los registros reales. Id columna Id en ambas tablas es una columna de identidad única generada automáticamente. IsProcessed columna IsProcessed en la tabla ItemInfo se usará para identificar y eliminar los registros que ya no son válidos.

  2. Cree un paquete SSIS y cree 5 variables como se muestra en la captura de pantalla # 2 . He utilizado la extensión .txt para los archivos delimitados por tabulaciones y, por lo tanto, el valor *.txt en la variable FileExtension . FilePath variable FilePath se asignará con valor durante el tiempo de ejecución. FolderLocation variable FolderLocation denota dónde se FolderLocation los archivos. SQLPostLoad variables SQLPostLoad y SQLPreLoad indican los procedimientos almacenados utilizados durante las operaciones de SQLPostLoad y SQLPreLoad . Los scripts para estos procedimientos almacenados se proporcionan en la sección Scripts .

  3. Cree una conexión OLE DB apuntando a la base de datos de SQL Server. Cree una conexión de archivo plana como se muestra en las capturas de pantalla # 3 y # 4 . La sección Columnas de conexión de archivo plano contiene información de nivel de columna. La captura de pantalla n. ° 5 muestra la vista previa de los datos de las columnas.

  4. Configure la Tarea de flujo de control como se muestra en la captura de pantalla n. ° 6 . Configure las tareas Pre Load , Post Load y Loop Files como se muestra en las capturas de pantalla # 7 – # 10 . IsProcessed truncará la tabla de etapas y establecerá el indicador IsProcessed en falso para todas las filas en la tabla ItemInfo. Post Load actualizará los cambios y eliminará las filas en la base de datos que no se encuentran en el archivo. Consulte los procedimientos almacenados utilizados en esas tareas para comprender qué se está haciendo en estas tareas de Execute SQL .

  5. Haga doble clic en la tarea Cargar flujo de datos de elementos y configúrela como se muestra en la captura de pantalla n. ° 11 . Read File es un archivo fuente plano configurado para usar la conexión de archivo plano. Row Count se deriva de la transformación de columnas y su configuración se muestra en screenshto # 12 . Check Exist es una transformación de búsqueda y sus configuraciones se muestran en capturas de pantalla # 13 – # 15 . Búsqueda sin coincidencia La salida se redirige a la Destination Split en el lado izquierdo. La Salida de coincidencia de búsqueda se redirige a Staging Split por Staging Split en el lado izquierdo. Destination Split y la Destination Split Staging Split tienen la misma configuración exacta que se muestra en la captura de pantalla n. ° 16 . El motivo de 9 destinos diferentes para el destino y la tabla de etapas es mejorar el rendimiento del paquete.

  6. Todas las tareas de destino 0 – 8 están configuradas para insertar datos en la tabla dbo.ItemInfo como se muestra en la captura de pantalla # 17 . Todas las tareas de etapas 0 a 8 están configuradas para insertar datos en dbo.Staging como se muestra en la captura de pantalla n. ° 18 .

  7. En el administrador de conexión de archivos planos, configure la propiedad ConnectionString para usar la variable FilePath como se muestra en la captura de pantalla # 19 . Esto permitirá que el paquete use el valor establecido en la variable a medida que recorre cada archivo en una carpeta.

Escenarios de prueba:

 Test results may vary from machine to machine. In this scenario, file was located locally on the machine. Files on network might perform slower. This is provided just to give you an idea. So, please take these results with grain of salt. 
  1. El paquete se ejecutó en una máquina de 64 bits con CPU de un solo núcleo Xeon de 2,5 GHz y 3,00 GB de RAM.

  2. Cargó un archivo plano con 1 million rows . El paquete se ejecutó en aproximadamente 2 minutos y 47 segundos . Refiere capturas de pantalla # 20 y # 21 .

  3. Usé las consultas provistas en la sección de consultas de prueba para modificar los datos y simular la actualización, eliminación y creación de nuevos registros durante la segunda ejecución del paquete.

  4. Cargó el mismo archivo que contiene 1 million rows después de que se ejecutaron las siguientes consultas en la base de datos. Paquete ejecutado en aproximadamente 1 minuto 35 segundos . Refiere capturas de pantalla # 22 y # 23 . Tenga en cuenta la cantidad de filas redirigidas a destino y la tabla de etapas en la captura de pantalla # 22 .

Espero que ayude.

Consultas de prueba:.

 --These records will be deleted during next run --because item ids won't match with file data. --(111111 row(s) affected) UPDATE dbo.ItemInfo SET ItemId = 'DEL_' + ItemId WHERE Id % 9 IN (3) --These records will be modified to their original item type of 'General' --because that is the data present in the file. --(222222 row(s) affected) UPDATE dbo.ItemInfo SET ItemType = 'Testing' + ItemId WHERE Id % 9 IN (2,6) --These records will be reloaded into the table from the file. --(111111 row(s) affected) DELETE FROM dbo.ItemInfo WHERE Id % 9 IN (5,9) 

Columnas de conexión plana de archivos .

 Name InputColumnWidth DataType OutputColumnWidth ---------- ---------------- --------------- ----------------- Id 8 string [DT_STR] 8 ItemId 11 string [DT_STR] 11 ItemName 21 string [DT_STR] 21 ItemType 9 string [DT_STR] 9 

Scripts: (para crear tablas y procedimientos almacenados) .

 CREATE TABLE [dbo].[ItemInfo]( [Id] [int] IDENTITY(1,1) NOT NULL, [ItemId] [varchar](255) NOT NULL, [ItemName] [varchar](255) NOT NULL, [ItemType] [varchar](255) NOT NULL, [IsProcessed] [bit] NULL, CONSTRAINT [PK_ItemInfo] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [UK_ItemInfo_ItemId] UNIQUE NONCLUSTERED ([ItemId] ASC)) ON [PRIMARY] GO CREATE TABLE [dbo].[Staging]( [Id] [int] IDENTITY(1,1) NOT NULL, [ItemId] [varchar](255) NOT NULL, [ItemName] [varchar](255) NOT NULL, [ItemType] [varchar](255) NOT NULL, CONSTRAINT [PK_Staging] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY] GO CREATE PROCEDURE [dbo].[PostLoad] AS BEGIN SET NOCOUNT ON; UPDATE ITM SET ITM.ItemName = STG.ItemName , ITM.ItemType = STG.ItemType , ITM.IsProcessed = 1 FROM dbo.ItemInfo ITM INNER JOIN dbo.Staging STG ON ITM.ItemId = STG.ItemId; DELETE FROM dbo.ItemInfo WHERE IsProcessed = 0; END GO CREATE PROCEDURE [dbo].[PreLoad] AS BEGIN SET NOCOUNT ON; TRUNCATE TABLE dbo.Staging; UPDATE dbo.ItemInfo SET IsProcessed = 0; END GO 

Captura de pantalla n. ° 1:

1

Captura de pantalla n. ° 2:

2

Captura de pantalla n. ° 3:

3

Captura de pantalla n. ° 4:

4

Captura de pantalla n. ° 5:

5

Captura de pantalla n.º 6:

6

Captura de pantalla n. ° 7:

7

Captura de pantalla n. ° 8:

8

Captura de pantalla n. ° 9:

9

Captura de pantalla n. ° 10:

10

Captura de pantalla n. ° 11:

11

Captura de pantalla n.º 12:

12

Captura de pantalla n.º 13:

13

Captura de pantalla n.º 14:

14

Captura de pantalla n.º 15:

15

Captura de pantalla n.º 16:

dieciséis

Captura de pantalla n.º 17:

17

Captura de pantalla n.º 18:

18

Captura de pantalla n. ° 19:

19

Captura de pantalla n. ° 20:

20

Captura de pantalla n.º 21:

21

Captura de pantalla n.º 22:

22

Captura de pantalla n.º 23:

23

Suponiendo que está usando el Agente SQL (o planificador similar)

Reqs 1/4) Tendría un paso previo para manejar los pasos de FTP y / o archivo. No me gusta desordenar mis paquetes con la manipulación de archivos si puedo evitarlo.

Reqs 2/3) En el nivel de flujo de control, el diseño del paquete se verá como una tarea de ejecución de SQL conectada a un flujo de datos conectado a otra tarea de ejecución de SQL. Como indicó @AllenG, lo mejor sería cargarlo en una tabla de etapas mediante la tarea de flujo de datos. La primera tarea Ejecutar SQL purgará todas las filas de la tabla de etapas (TRUNCATE TABLE dbo.DAILY_STAGE)

El diseño aproximado de la mesa se ve así. La tabla MICHAEL_BORN es su tabla existente y DAILY_STAGE es donde su flujo de datos aterrizará.

 CREATE TABLE DBO.MICHAEL_BORN ( ID int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED , ItemID int NOT NULL , ItemName varchar(20) NOT NULL , ItemType varchar(20) NOT NULL ) CREATE TABLE dbo.DAILY_STAGE ( ItemID int NOT NULL PRIMARY KEY CLUSTERED , ItemName varchar(20) NOT NULL , ItemType varchar(20) NOT NULL ) 

Para demostraciones, cargaré las tablas anteriores con datos de muestra a través de TSQL

 -- Original data INSERT INTO dbo.MICHAEL_BORN VALUES (2345,'Apple','Fruit') , (4578, 'Bannana','Fruit') -- Daily load runs -- Adds a new fruit (pear), corrects misspelling of banana, eliminates apple INSERT INTO dbo.DAILY_STAGE VALUES (7721,'Pear','Fruit') , (4578, 'Banana','Fruit') 

La tarea Ejecutar SQL aprovechará la instrucción MERGE disponible en las ediciones 2008+ de SQL Server. Tenga en cuenta que el punto y coma al final es parte de la instrucción MERGE. Si no se incluye, se producirá un error de “Una sentencia MERGE debe terminar con un punto y coma (;)”.

 -- MERGE statement -- http://technet.microsoft.com/en-us/library/bb510625.aspx -- Given the above scenario, this script will -- 1) Update the matched (4578 bannana/banana) row -- 2) Add the new (pear) row -- 3) Remove the unmatched (apple) row MERGE dbo.[MICHAEL_BORN] AS T USING ( SELECT ItemID , ItemName , ItemType FROM dbo.DAILY_STAGE ) AS S ON T.ItemID = S.ItemID WHEN MATCHED THEN UPDATE SET T.ItemName = S.ItemName , T.ItemType = S.ItemType WHEN NOT MATCHED THEN INSERT ( ItemID , ItemName , ItemType ) VALUES ( ItemID , ItemName , ItemType ) WHEN NOT MATCHED BY SOURCE THEN DELETE ; 

Req 5) La eficiencia se basa totalmente en sus datos y en la amplitud de sus filas, pero no debe ser terrible.

 -- Performance testing -- Assumes you have a similar fast row number generator function -- http://billfellows.blogspot.com/2009/11/fast-number-generator.html TRUNCATE TABLE dbo.MICHAEL_BORN TRUNCATE TABLE dbo.DAILY_STAGE -- load initial rows -- 20ish seconds INSERT INTO dbo.MICHAEL_BORN SELECT N.number AS ItemID , 'Spam & eggs ' + CAST(N.number AS varchar(10)) AS ItemName , 'SPAM' AS ItemType --, CASE N.number % 2 WHEN 0 THEN N.number + 1000000 ELSE N.number END AS UpTheEvens FROM dbo.GenerateNumbers(1000000) N -- Load staging table -- Odds get item type switched out -- Evens get delete and new ones created -- 20ish seconds INSERT INTO dbo.DAILY_STAGE SELECT CASE N.number % 2 WHEN 0 THEN N.number + 1000000 ELSE N.number END AS ItemID , 'Spam & eggs ' + CAST(N.number AS varchar(10)) AS ItemName , CASE N.number % 2 WHEN 0 THEN 'SPAM' ELSE 'Not much spam' END AS ItemType FROM dbo.GenerateNumbers(1000000) N -- Run MERGE statement, 32 seconds 1.5M rows upserted -- Probably fast enough for you 

Solo quiero dar mi idea para el próximo tipo que pueda pasar por esta pregunta. Así que voy a sugerir mi idea para cada escenario.
1. Getfile desde FTP o local.
Te sugiero que utilices Dropbox, Google Drive o cualquier otro servicio en la nube de sincronización de tu elección, mira este enlace para más detalles.
2. Yo sugeriría que cargue todos los datos de archivo plano a la tabla de etapas como sugirió. Luego, comparar los datos se haría fácilmente usando MERGE entre su tabla de etapas y la tabla de objectives en su columna única (ID). Puede ver este enlace sobre cómo usar el script de fusión. Los escenarios segundo y tercero se resolverán si está utilizando MERGE Script.
Para los dos últimos escenarios, sugiero que use SQL JOB para ejecutar automáticamente el paquete y progtwigrlo fuera de horario u horario cuando el servidor no esté ocupado. Consulte el enlace para obtener detalles sobre cómo ejecutar un paquete utilizando un SQL. Server Agent Job simplemente escríbalo en tu motor de búsqueda favorito y encontrarás miles de blogs que muestran cómo se hace.

SSIS Suena como el camino a seguir. La forma en que he visto manejar su tipo de problema anteriormente es con una tabla de etapas. El nuevo documento se carga en la tabla de etapas; luego se comparan etapas y producción; los registros obsoletos se archivan (no solo se eliminan) de producción; las filas existentes con algunos cambios se actualizan (nuevamente, los datos originales se archivan en alguna parte) y se insertan nuevas filas.

Nota: su definición de “obsoleto” debe ser muy, muy precisa. Por ejemplo: ¿algo debe archivarse solo porque una fila coincidente no existe en su archivo más reciente? ¿Debería permanecer durante X cantidad de tiempo en caso de que aparezca en un archivo posterior? Estas y otras preguntas deben ser consideradas.

Casi cualquier tutorial estándar de SSIS debe indicarle la ruta correcta de cómo hacer cada uno de estos pasos.

Le daría una oportunidad a Merge. Asegúrese de tener índices en ItemID en ambas tablas.

 Merge [dbo].[ItemInfo] as target using ( SELECT stg.ItemID, stg.ItemName, stg.ItemType FROM [dbo].[ItemInfo_Staging] stg LEFT OUTER JOIN [dbo].[ItemInfo] final on stg.ItemId = final.ItemId ) as SOURCE ON SOURCE.ItemID = target.ItemID WHEN MATCHED THEN Update SET target.ItemID = SOURCE.ItemID , target.ItemName = SOURCE.ItemName , target.ItemType = SOURCE.ItemType WHEN NOT MATCHED BY TARGET THEN INSERT (ItemID, ItemName, ItemType ) VALUES (SOURCE.ItemID, SOURCE.ItemName, SOURCE.ItemType ) WHEN NOT MATCHED BY SOURCE THEN DELETE ;