How to write sql table output at a specific range or cell.

Question

I need to export Sql server data into Excel at a specific range. Below is the query I am using:-

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Documents\Discrepancy Report.xlsx;','SELECT * FROM [Sheet1$A22:C37]')
select * from [Test]

It is throwing error as
Cannot process the object "SELECT * FROM [Sheet1$A22:C37]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

How can i do this

Answer 1

Go to the "SQL Server (MSSQLSERVER)" service and change the service account from NT Service\MSSQLSERVER to Local System and then restart the service. If you want to export the specific table columns to the excel, you need to have the column headers in the excel sheet. And then run the following query:

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Documents\Discrepancy Report.xlsx;','SELECT * FROM [Sheet1$]')
SELECT Col1, Col2, ... FROM [Test]

You need to specify the names of the columns you want to export data from .

Answer 2

Hi saritakumari,

We can use OLEDB Source and Excel Destination in SSIS package.

When configuring Excel Destination, we can use the following two methods to get specific range:
Method1: Set AccessMode as SQL Command

Method2: Set AccessMode as OpenRowset

Please refer to How to read data from an Excel file starting from the nth row with SQL Server Integration Services.

Best Regards,
Mona

----------

If the response is helpful, please click "Accept Answer" and upvote it.