Morir con una consulta Insert de Access

Si alguna vez ha intentado insertar una gran cantidad de registros en SQL Server utilizando una consulta de inserción de Access (15,000 registros o más), tendrá una larga espera, aquí estamos hablando de un descanso para tomar café (desde unos minutos a 15). Incluso puede hacer que su base de datos se bloquee si el conjunto de datos es demasiado grande. Entonces, ¿qué opciones tienes?

XML es un estupendo primer intento

Uno de los métodos que usamos generalmente en nuestro código es trabajar con datos XML, no voy a entrar en demasiados detalles al respecto aquí, pero aquí hay un enlace que escribió un colega mío. Cubre una serie de 2 partes que analiza el trabajo con datos XML con un servidor SQL.

XML es una gran herramienta para probar, pero incluso eso puede no ser lo suficientemente rápido con la numeración de conjuntos de datos en los 10,000. Sin embargo, la ventaja de XML sobre un trabajo de SSIS sería que un trabajo de SSIS requiere una carpeta de red a la que puedan acceder todos los usuarios de Access y el servidor. Eso no funcionaría bien en Internet (el escenario para el que se creó originalmente el método XML).

SSIS is super quick

Lo que quiero compartir con ustedes aquí es sobre mi experiencia trabajando con SQL Server Integration Services. Teníamos un cliente cuya base de datos ya estaba usando el método XML para cargar los datos en el servidor SQL, pero debido a que los datos que estaban importando tenían más de 700,000 filas de datos, se tardaban 20 minutos en completar el proceso. Esto claramente estaba tomando demasiado tiempo para una empresa ocupada que necesitaba usar este método diariamente. Nuestra solución fue eliminar Access del proceso de carga mediante la creación de un trabajo que leería el archivo CSV directamente desde la ubicación del archivo e importaría los datos a la tabla de SQL Server usando un simple script T-SQL.

Access solo inicia el proceso

Los usuarios seleccionarán su archivo de datos en Access e ingresarán cualquier otra información requerida, como una fecha y harán clic en el botón de proceso. El primer paso del código vba sería almacenar el nombre de archivo y la ruta a una tabla en SQL Server.

'Add import file name to Application ExecuteMyCommand "UPDATE Application SET SSISDataImportFile = " & PrepareSQLString(Me.txtFileInput)

Here is the vba code used to then execute the SSIS job.

Public Function ImportData()
  On Error GoTo ImportData_Err
  Dim rs As ADODB.Recordset
  Dim strSQL As String
 
  'Add Code to activate SSIS Package
  strSQL = "EXEC dbo.uspSSISFileDataImport"
  OpenMyRecordset rs, strSQL, rrOpenForwardOnly, rrLockReadOnly, True
 
  'The following loops a procedure to check if job has completed.
  strSQL = "EXEC dbo.uspSSISFileDataImportProcess"
  OpenMyRecordset rs, strSQL, rrOpenForwardOnly, rrLockReadOnly, True
 
  Do Until rs.Fields(0) = 4 And Not IsNull(rs.Fields(3))
    strSQL = "EXEC dbo.uspSSISFileDataImportProcess"
    OpenMyRecordset rs, strSQL, rrOpenForwardOnly, rrLockReadOnly, True
  Loop
 
ImportData_Exit:
  Set rs = Nothing
  Exit Function
 
ImportData_Err:
  MsgBox Err.Description
  Resume ImportData_Exit
  Resume 'for debugging
 
End Function
CREATE PROCEDURE [dbo].[uspFileDataImport] 
AS 
BEGIN 
  SET NOCOUNT ON; 
 
  EXECUTE msdb.dbo.sp_start_job @Job_name = N'SSISDataImport'; 
 
END;
CREATE PROCEDURE [dbo].[uspSSISFileDataImportProcess]
AS
BEGIN 
  SET NOCOUNT ON;
  DECLARE 
    @execution_id INT,
    @JobStatusID INT,
    @JobStatus Varchar(250),
    @StartTime DATETIME2,
    @EndTime DATETIME2;
 
  WAITFOR DELAY '00:00:03';
 
  SELECT @execution_id=MAX ([execution_id]) 
  FROM [SSISDB].[internal].[executions];
 
  SELECT 
    @JobStatusID = e.executable_id,
    @JobStatus = e.executable_name,
    @StartTime = s.start_time,
    @EndTime = s.end_time
  FROM SSISDB.internal.executables AS e
  LEFT JOIN SSISDB.internal.executable_statistics AS s 
    ON e.executable_id = s.executable_id AND s.execution_id = @execution_id;
 
  SELECT @JobStatusID, @JobStatus, @StartTime, @EndTime;
END;

Este procedimiento almacenado ejecutará un job simple con el siguiente diseño.

Obtener parámetros: este es un script simple de T-SQL que selecciona el nombre del archivo y la ruta de la tabla sql y asigna los valores a los parámetros respectivos. SELECT SSISDataImportFile FROM tblApplicationSettings; El nombre del archivo difería de un día a otro, por lo que usar un parámetro era el camino a seguir, el nombre del archivo se ingresa en un objeto no vinculado en el formulario de acceso y luego con la codificación VBA se guarda en una tabla SQL, lo que permite que el paquete lea esto una tabla SQL (ver código arriba).
Truncar la entrada de hoy: un simple script T-SQL para eliminar cualquier dato existente en la tabla de importación temporal, ejecutar a través de los registros para realizar cambios o actualizaciones. Es posible que deba importar los datos primero a una tabla temporal en caso de que necesite verificar los datos o realizar más cambios antes de almacenarlos en la tabla permanente.
Tarea de flujo de datos: se explica en la siguiente sección.
Fuente de archivo plano: con los parámetros del primer paso, el job puede acceder al archivo de texto.
El archivo debe almacenarse en una unidad de red o en una carpeta a la que pueda acceder el servidor.
Destino OLE DB: este paso final del trabajo es el proceso que importa los datos a la tabla de SQL Server. Aquí identifica la conexión de la base de datos y el nombre de la tabla. También es donde asignará los campos del archivo de texto a los campos de destino en la tabla.
Finalmente creé el procedimiento almacenado para devolver el executionID. El propósito aquí es que el procedimiento almacenado no saldrá hasta que el trabajo se haya completado, evitando que el código de Access VBA continúe hasta que se complete el trabajo. La idea del trabajo es simplemente obtener los datos en una tabla de SQL Server y una vez allí, puede hacer cualquier modificación a los datos a través de Access y finalmente almacenar los datos en la tabla permanente.

¡Desde 20 minutos hasta 3!

Nuestro cliente estaba muy contento con los resultados, poder aprovechar la increíble tecnología de SQL Server junto con Access me permite dar grandes pasos en la eficiencia en mi trabajo, ¡no puedo esperar para volver a intentarlo pronto!