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.
@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;'
SELECT * INTO Table_1
'Excel 12.0; Database=P:\Test\RPT-AR3-007.xls', [Sheet1$]);
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)".
sp_configure 'show advanced options', 1;
sp_configure 'Ad Hoc Distributed Queries', 1;
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.
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.
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).