Solved

how do I use a text file as a data source in SSRS

Posted on 2008-10-13
1
3,063 Views
Last Modified: 2013-11-05
Does anyone have an example on how to use a text file as a SSRS data source?  In this case it is a CSV file.  I know that I could use SSIS to import and then report off of it, but I would rather directly access the file from within SSRS.

Thank you
0
Comment
Question by:fmsdev
1 Comment
 

Accepted Solution

by:
fmsdev earned 0 total points
ID: 22725585
found this at: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=955207&SiteID=1

RS supports ODBC providers, so you can specify that you want to use the CSV/text provider, and set your connection string accordingly. (The designer may have issues with regard to removing the '{' and '}' characters from the connection string, if you use the data source dialog for setting the connection string. So, after choosing the data source type, ODBC, and setting the credentials information in the data source dialog, be sure to insert the connection string directly in the RDL.)

Here is the connection string to use (be sure to change the path from "c:\" to location of your text files):

Driver={Microsoft Text Driver (*.txt; *.csv)};dbq=C:\CSVFiles\;defaultdir=C:\CSVFiles\;driverid=27;fil=text;maxbuffersize=2048;pagetimeout=5

As an example, create a file that contains the following, named flatfile.csv. Place this file in the location specified in your connection string.

File contents:
    Col1,Col2,Col3
    Hello,there,123

Now you can query this using this query string:

    Select * from "flatfile.csv"
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question