Not all data will come from a SQL Server database when you are creating reports in SSRS. There will be times you will need to create a data source to .CSV files, Excel spreadsheets, and MS Access databases.
Recently, I had a customer request setting up sample data sources to these types of files to help the developers complete a migration project.
Items Needed to be Installed on the SSRS Server
The first thing you will need are executables from Microsoft to enable OLEDB and ODBC connections to the source data. SSRS will not be able to use ODBC or OLEDB until the specific drivers for these types of files are installed on the server.
You will need to download and install the following on your SSRS server. While these look to be just for MS Access, they include the drivers for connecting to Flat files and MS Excel as well.
- AccessDatabaseEngine_x64.exe
- AccessDatabaseEngine.exe
Located at: http://www.microsoft.com/en-us/download/details.aspx?id=13255 (at the time this was written)
NOTE: I would recommend installing the 64 bit version first, and then the 32 bit version to help save some time troubleshooting the issue of trying to install a 64 bit version once the 32 bit version is already installed.
For Testing
The system is ready to set up the data sources once the drivers are installed on the server. For testing purposes, I have a folder containing sample files for the different types of data sources I would like to connect to:
How to Create a Data Source for MS Excel Files (.xls, .xlsx)
We will need to create an ODBC connection on the server to the Excel file before creating a data source in Report Manager.
1. Create a system DNS using the Microsoft Excel Driver (*.xls)
2. Create a meaningful name for your data source. The name will be used later in SSRS when creating the connection string.
3. Make sure to select the correct version of Excel you are wanting to connect and then select Workbook button:
4. Navigate to the location of the document and select the file. Complete the process of creating the system DSN.
You will now be able to create and test your data source in SSRS.
Creating the Data Source in SSRS
1. Open the Report Manager and navigate to the folder structure of where you would like to save your data source. Select “New Data Source”.
2. Give your data source a descriptive name. Create a good description of the data source for documentation purposes.
3. Select ODBC as your Data Source Type from the drop down menu.
4. Your connection string is “Dns=xxx”, where xxx is the name you gave your system DSN.
5. Make sure to test your connection, and then select OK.
When writing your query for the report, you will reference your sheet in the excel file which has the data you are wishing to display on the report.
Example: “select * from [Sheet1$]”
Creating a Data source to .CSV file
For this process, you will use OLEDB to connect to the data source. The main item to note is the data source is defined to look a directory instead of a physical file. If you use a central location for all the .csv files to be located, you will only need to create one data source in SSRS. The queries inside your report will dictate which file the report is pulled from. If you are worried about security, you could set up multiple locations for the files and create individual data sources for the structure.
Unlike connecting to a MS Excel file, you will not need to create a SYSTEM DNS before creating your data source in SSRS.
Navigate to the location you would like your data source to be stored in Report Manager and select new data source.
Give your data source a meaningful name and don’t forget to provide a description for documentation purposes.
For your ‘Data Source Type’ select OLEDB. You will be using the Microsoft.ACE.OLEDB.12.0 provider which was installed with the executables during the system set up listed above.
For your connection string, your provider is Microsoft.ACE.OLEDB.12.0 and the source is the path to the folder which contains your file. For this example, we are assuming the file does not contain a header row. (HDR=No in the properties)
Your connection string will look something like this.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:SampleDataSourceFilesSampleDataSourceSampleFiles;Extended Properties=”text;HDR=No;FMT=Delimited”
Once your Data source connection has been created, you can test it by creating a simple report. Below is an example of a query you would use in Report Builder to display the information from a file called “samplecsv.csv”.
Example: select * from samplecsv.csv
To Create a Data Source to an MS Access database (*.accdb files)
To create a data source to an MS Access database, you will use OLEDB Data source type. The sample shown below is a connection to the MS Access 2010 version of the Adventureworks database.
For your connection string, your provider will be “Microsoft.ACE.OLEDB.12.0” and your source will be the physical location of the .accdb file.
After the Data sources have been setup, I always like to create a couple of quick same reports to insure they are working as expected.
This process was testing on the following architecture:
Windows 2012R2, SQL Server 2014, Reporting Services 2014
Windows 2008r2 SP1, SQL Server 2012, Reporting Services 2012
Thanks for taking the time to read this post. Feedback is always welcome.
If you have questions on getting Data Sources set up in SSRS or need assistance with configuring SSRS for performance or SQL Server in general, reach out to us! XTIVIA can assist you with your Reporting Service needs.