subramani.rudrappa 78855 (11/16/2012)

---

Hey Hi,

I know we can use SSIS to get data from excel and load in to SQL table from where i can achieve requirements.

I thought of using only SQL to get data from excel and achieve the requirements so that we need not to go for SSIS.

a couple of prerequisites:

install the AccessDatabaseEngine_x64.exe from microsoft:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

make sure you open an Administrative command prompt window, and run it with the c:\Downloads\AccessDatabaseEngine_x64.exe /passive
command line flag;

this will force the install of the drivers, even if you have 32 bit office installed;
otherwise you get some error about 32 bit Office preventing the install.
After that is isntalled:

--Required settings for the provider to work correctly as a linked server
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

and then the code for the linked server:

--#################################################################################################
--Linked server Syntax for Excel with ACE 64 driver
--#################################################################################################
GO
DECLARE 
        @srvproduct nvarchar(256),
        @provider   nvarchar(256),
        @location   nvarchar(100),
        @provstr    nvarchar(100),
        @catalog    sysname,
        @sql        varchar(1000)
--add an excel spreadsheet as a linked server.
SET @server = 'MyExcelACE'
SET @srvproduct = ''
SET @provider   = 'Microsoft.ACE.OLEDB.12.0'
set @provstr    = 'Excel 12.0'
SET @datasrc ='C:\Data\BlockGroups_2010\AKblockgroup.xls'
EXEC sp_addlinkedserver  @server,@srvproduct,@provider,@datasrc,NULL,@provstr
EXEC dbo.sp_AddLinkedSrvLogin @server, FALSE, NULL, Admin, NULL
--what spreadsheets (table equivilents are available?
EXEC sp_tables_ex 'MyExcelACE'
--you MUST know the name of the spreadsheet; 
--spreadsheet name has a dollar sign at the end of it!
--I've personally never gor a spreadsheet that has a space in it
--for example "Activity Data" =ActivityData$ never got [Activity Data$] or [Activity Data]$ to work
--to work, so I end up editing the spreadsheet to remove spaces if that happens.
SELECT * FROM MyExcelACE...ActivityData$;
SELECT * FROM MyExcelACE...Sheet1$;

subramani.rudrappa 78855 (11/16/2012)

---

Hey Hi,

I know we can use SSIS to get data from excel and load in to SQL table from where i can achieve requirements.

I thought of using only SQL to get data from excel and achieve the requirements so that we need not to go for SSIS.