Using .CSV as datasouce in SSRS w/ ODBC: Dataset can query columns, but report can't connect to datasource when run

0
votes

Application: Report Builder Connection Type: ODBC

I am attempting to connect to, and query a csv using SSRS. I've added a system DSN, and am able to successfully connect when I click the "Test Connection" button on the "Connection Properties" window. However when I click the same button within the "Data Source Properties" window, I get the error message below.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

What's strange is that I created a dataset with a simple SELECT * query FROM [csvfilename], and it successfully displays all the fields from my csv. I therefore know it can access it. When I try to run the report, however, I get the error below.

Cannot create a connection to data source '[DataSource Name]' (rsErrorOpeningConnection)

Here is the Connection String I'm using within my DataSource:

Dsn=WIPEXPORT;Driver=Microsoft Text Driver (*.txt;dbq=I:\\HARVEST\\DATA BROWSERS\\WIPEXPORT;defaultdir=I:\\HARVEST\\DATA BROWSERS\\WIPEXPORT;driverid=27;fil=text;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;threads=3;usercommitsync=Yes

I apologize if this has already been answered. I couldn't seem to find anyone with my specific problem.

1
csvreporting-servicesodbcreportbuilder3.0
Are you experiencing these errors when doing a preview in Report Builder, or when you are trying to run the report after you deploy the RDL to the SSRS server? – R. Richards
I attempted both just to be sure. I added in a table to display the columns that the dataset was able to query, and tried saving it and running it within the report server itself. Did not work, same error. – Emmet Vassar
I tried to repro this using VS2015, but I could not. Try removing some of the extra stuff in the connection string. When I did this, the connection string I ended up with is this: Dsn=WIPEXPORT;trusted_connection=Yes. – R. Richards
@R.Richards wasn't able to get ReportBuilder to work, but I switched to VS based on your success, and it works. Appreciate your efforts to reproduce. – Emmet Vassar

1 Answers

0
votes

So after trying just about every connection string configuration within ReportBuilder, I ended up switching to VisualStudio where it worked immediately. I even copied the successful connection string from VS back to RB and RB was still broken. Don't know if it's my RB installation or what, but at least it works now.