Garth Zaleschuk - Monday, February 26, 2018 12:55 PM

Help please!!  I am bruised and bloodied from mashing my head, for two weeks now, against stuff I do not know. 

Ultimately I am trying to get an Excel (.xls) directly into a SQL Server (2016) table. 

Here is what I have done so far using Goolge for assistance.

1)

EXEC sp_addlinkedserver
    @server = 'ExcelServer2',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'P:\Test\RPT-AR3-007.xls',
    @provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
--------
2)

SELECT * INTO Table_1
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0; Database=P:\Test\RPT-AR3-007.xls', [Sheet1$]);
GO
--------
I get:

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
------
3)

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
------

4)

Argh! Expletive!!

OK, that does not work… so I’ll use DTS (2016) to convert my Excel file to a CSV.  I know how to get a CSV directly into a table within SQL Server.

DTS pkg creation works and creates a proper CSV file.  I save the DTS to a pkg within the File System.  DTSX created… don’t ask about saving it to a SQL IIS cause no one at Corporate can help me with that one either.

5)

Reach out

So my question is : How do I execute the Pkg from within a Stored Procedure (preferred) or from the Agent.  I would help if the explanation is laid out so a two year old can understand (KISS principle method).