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
+1
A:
I don't think you can
Data Sources Supported by Reporting Services. In the table, your only chance would be "Generic ODBC data source", however a text file is not ODBC compliant AFAIK. No types, no structure etc.
Why not just display the text files? It seems a bit strange to query text files to bloat them into formatted HTML...
gbn
2009-08-04 11:22:09
They could be CSV or tab-delimited text files, which would be rather ugly to display, especially if you wanted aggregates or other SQL functions applied.
Eric
2009-08-04 11:26:36
@Eric: true, but what structure could you aggregate on?
gbn
2009-08-04 11:32:38
A:
I'm not of the mind that you can, but a workaround for this, if your text files are CSVs or the like, is to create an SSIS package which brings that data into a table in SQL Server, which you can then query like there's no tomorrow. SSIS does Flat File Sources with ease.
You can even automate this by right clicking the database in SSMS, doing Tasks->Import Data. Walk through the wizard, and you can then save off the package at the end.
Eric
2009-08-04 11:25:35
A:
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