Text file as data source in SSRS

Question

I need to use text files as data source in SSRS. I tried accessing this with ‘OLEDB provider for Microsoft directory services’ connection. But I could not. The query is given below.

Also let me know how to query the data

Answer 1

I know this thread is old, but as it came up in my search results this may help other people.

There are two 'sort of' workarounds for this. See the following: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130650

So basically you should use OLEDB as the data source, then in the connection string type:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxxx;Extended Properties="text;HDR=No;FMT=Delimited"

Then make sure your file is saved in .txt format, with comma delimiters. Where I've put xxxx you need to put the FOLDER directory - so C:\Temp - don't go down to the individual file level, just the folder it's in.

In the query you write for the dataset, you specify the file name as though it were a table - essentially your folder is your database, and the files in it are tables.

Thanks

Answer 2

I have had great success creating linked servers in SQL to link to disparate text files for creating SSRS reports. Below is sample SQL to link to your txt files:

EXEC master.dbo.sp_addlinkedserver @server = N'', @srvproduct=N'', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'', @provstr=N'text'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'YourLinkedServerName',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL