Text file as a data source in SSRS

Question

I need to use text files as a data source in SSRS. I tried to access this using the OLEDB provider to connect Microsoft directory services. But I could not. The request is below.

Also let me know how to request data

Answer 1

I know this thread is out of date, but since it appeared in my search results, it can help other people.

There are two “types” 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, and then in the connection string line:

Provider = Microsoft.Jet.OLEDB.4.0; = xxxx; = "; HDR = ; FMT = "

, .txt -. xxxx, FOLDER - C:\Temp - , .

, , , - , - , - .

Answer 2

, SQL SSRS. SQL txt:

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

Answer 3

,

, Reporting Services. " ODBC", ODBC AFAIK. , ..

? , , HTML...

Answer 4

BULK INSERT SSRS, :

CREATE TABLE #FlatFile
(
    Field1 int,
    Field2 varchar(10),
    Field3 varchar(15),
    Field4 varchar(20),
    Field5 varchar(50)
)

BEGIN TRY
BULK INSERT #FlatFile
   FROM 'C:\My_Path\My_File.txt'
   WITH   
      (  
         FIELDTERMINATOR ='\t',   -- TAB delimited
         ROWTERMINATOR ='\n',     -- or '0x0a' (whatever works)
         FIRSTROW = 2,            -- has 1 header row
         ERRORFILE = 'C:\My_Path\My_Error_File.txt',
         TABLOCK
      );  
END TRY
BEGIN CATCH
  -- do nothing (prevent the query from aborting on errors...)
END CATCH

SELECT * FROM #FlatFile

Answer 5

I don’t think you can, but a workaround for this, if your text files are CSV or the like, is to create an SSIS package that transfers this data to a table on SQL Server, which you can then ask if not tomorrow. With SSIS it works easily with files.

You can even automate this by right-clicking the database in SSMS by doing Tasks-> Import Data. Go through the wizard, and then you can save the package at the end.