Access con SQL Server: importe grandes conjuntos de datos con un simple trabajo SSIS.

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.

¡Desde 20 minutos hasta 3!