Q

Can you import Excel data to SQL Server Reporting Services?

Importing data to SQL Server reporting Services is not an option. But here's how to create reports and use Excel spreadsheets as data sources.

Can you import Excel SS or anything besides Access.mdb into Reporting Services 2005?

Reporting Services does not import data. It only queries data in whatever format it is stored in their native storage system. In this question I will assume that you're asking whether you can create reports and use Excel spreadsheets as data sources. The answer is Yes, Reporting Services supports a wide variety of data sources, including Excel files. You'll get the best performance with the built-in native .NET providers but you should be able to connect to any ODBC or OLE-DB data source, whether it comes from Microsoft or a third-party company.

You can report against Excel data by using ODBC. First, create a new DSN in Windows Administrative Tools, select the Excel driver and point it to the Excel file you want to use. Then in your Reporting Services project create a new data source of the ODBC type, click on the Edit button next to the Connection String text box and select the DSN you had previously created. Once the data source is set up, you can create datasets in the query designer by running SQL statements against the Excel data. For example, let's say you have a file that contains a worksheet called Users. You can run the following query to retrieve your data:

SELECT * from [Users$]

Note: You need to append the dollar sign to the worksheet name.

 

This was first published in December 2007

Dig deeper on Microsoft SQL Server Reporting Services (SSRS)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close