Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3588
  • Last Modified:

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

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
fmsdev
Asked:
fmsdev
1 Solution
 
fmsdevAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now