I am certain that most of you already know that you can use SSIS (SQL Server Integration Services) to read Microsoft Excel data and insert it into SQL Server tables. However, what some of you may not know is that there is a more direct way to access Excel files to unlock the data and make available for use in SQL Server.
Actually, there are several ways:
- SQL Server Data Transformation Services (DTS) or SSIS, we’ve already mentioned
- SQL Server linked servers
- ActiveX Data Objects (ADO) and the Microsoft OLE DB Provider for SQL Server
- ADO and the Microsoft OLE DB Provider for Jet 4.0
The one we are going to discuss in this article, however, is SQL Server distributed queries.
Suppose I have an Excel workbook with data I would like to view, manipulate or use for inserts or updates in SQL Server. It might look something like this:
(Note: Data has intentionally been truncated in these examples to prevent the exposure of personal data.)
In order to access these data directly from within SQL Server, I first need to be certain that I have activated ‘Ad Hoc Distributed Queries’ in Advanced Options for my SQL Server configuration.
The ‘sp_configure’ commands in the figure below do just that.
(Note: In order to activate ‘Ad Hoc Distributed Queries’, the account credentials under which the SQL Server service is running must have permissions in the Windows environment to ‘Lock pages in memory.’ This must be set in the Windows Group Policy management console.)
In the figure above, we are using a simple ‘SELECT *…’ statement, but this could just as well be a more specific select statement as part of an INSERT or UPDATE operation.
Note also the ‘Data Source=’ portion of the OPENDATASOURCE parameters. This is where you inform SQL Server the location of the Excel file to be opened. The ‘[Customers$]’ at the end of the statement represents an named range in the Excel workbook.
Here is what the result set looks like (excerpted):
In my opinion, fetching data from Excel using this method is much faster than plowing through the SSIS wizard and, of course, the command can be used within a stored procedure that can employ variables, and even run from a SQL Agent Job for recurring inserts or updates.
Some typical applications for this method
- Vendor price lists arriving in Excel format could be used to update costs
- Vendor data on very large shipments could be automatically uploaded to create receipts of goods transactions from an Excel workbook provided by the vendor
- Large customer orders arriving as Excel documents could be used to generate sales orders automatically
- Updating customer pricing
There are dozens of other potential applications for this method of easily using Microsoft Excel data for inserts or updates in SQL Server. What are your ideas?
Let us know your thoughts on this subject by leaving a comment here, or feel free to contact us directly.
