Access with Microsoft SQL Server – Import Large Datasets Using SSIS

Dying with an Access Insert Query

If you’ve ever tried to insert a large amount of records into SQL Server using an Access insert query, (15,000 records or more), you’re in for a long wait, were talking coffee break time here, (from a few minutes to 15). It may even cause your database to hang if the dataset is too large.  So what options do you have?

XML is a great first attempt

One of the methods we generally use in our code is to work with XML data, I won’t go into too much detail about that here, but here is a link which a colleague of mine wrote.  It covers a 2 part series that discusses working with XML data with a SQL Server backend.

XML is a great tool to try, but even that may not be fast enough with datasets numbering in the 10,000’s.  However the advantage of XML over an SSIS job would be that an SSIS job requires a network folder where all Access users and the server can reach. That would not work well over the internet (the scenario the XML method was made for originally).

SSIS is super quick

What I want to share with you here is about my experience working with SQL Server Integration Services.  We had a client whose database was already using the XML method to upload the data to SQL server but because the data they were importing had over 700,000 rows of data it was taking 20mins to complete the process.  This was clearly taking too long for a busy company that needed to use this method daily.  Our solution was to remove Access from the upload process by creating a job that would read the CSV file directly from the file location and import the data to the SQL Server table using simple T-SQL script.

Access only kicks off the process

Users will select their data file in Access and input any other required information, such as a date and click the process button. The first step of the vba code would be to store the filename and path to a table in 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;
This stored procedure will run a simple job with the following design.

 Get Parameters – This is a simple T-SQL script that selects the file name and path from the sql table and assigns the values to respective parameters. SELECT SSISDataImportFile FROM tblApplicationSettings; The file name would differ from day to day so using a parameter was the way to go, the file name is entered into an unbound object on the access form then with VBA coding is saved to a SQL table, allowing the package to read this from a SQL table (see code above).

Truncate Today’s Input – A simple T-SQL script to remove any existing data in the temporary import table, run through the records to make changes or updates.  You may need to import the data first to a temporary table in case you need to verify the data or make further changes to the data before storing in the permanent table.

Data Flow Task – Explained in the following section.

 Flat File Source – Using the parameters from the first step the job is able to access the text file.
The file needs to be stored in a network drive or a folder which is accessible by the server.

OLE DB Destination – This final step of the job is the process which imports the data to the SQL Server table.  Here you identify the database connection and table name.  It also is where you will map the fields from the text file to the destination fields in the table.

Finally I created the stored procedure to return the executionID. The purpose here is that the stored procedure will not exit until the job has completed, preventing the Microsoft Access VBA code from continuing until the job is completed.   The idea of the job is just to get the data into a SQL Server table and once there you can do any modifications to the data via Access and finally store the data in the permanent table.

From 20 minutes down to 3!

Our client was super happy with the results, being able to leverage the awesome technology of SQL Server in conjunction with Access is allowing me to make great strides in efficiency at my job, can’t wait to try this again soon!

Join Susan Pyne next Tuesday February 12th as she discusses how to encrypt columns in SQL Server such as credit cards and social security numbers, and how to decrypt them for display in Access, great for safeguarding valuable data! To learn more please visit https://buff.ly/2I7BPii #MSAccess @MSAccess #Encryption