question

Shell-4399 avatar image
0 Votes"
Shell-4399 asked Shell-4399 commented

Create an SSRS Report using a CSV Data Source (overcome x86 vs x64 issues)

Hi

We are currently using SSRS Report Server 2012. Users have Windows 2010 64 bit PCs and our servers are mostly Windows Server 2008 R2 SP1 servers and Report Builder v3. This is what I have to work with, this can't be changed.

I have a CSV file on a 2008 server that I want to create a RAP Report on, however I am having no luck. The CSV file is built using a powershell script that extracts certain data from a heap of xml files. I've copied the CSV to my PC to test with first.

So far, errors have consisted of at least the following, probably more:
1) The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified. (rsInvalidDataSourceCredentialSetting)
2) Cannot create a connection to data source 'dsOleDbCsvFile'. (rsErrorOpeningConnection) The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
3) ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
4) Unable to connect to data source

How do I create an SSRS Report with a CSV data source (without the x86 vs x64 issues) that actually works please?

Thanks in advance.

sql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

No answer so far unfortunately

0 Votes 0 ·

1 Answer

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Shell-4399 commented

Hi @Shell-4399

You could use an ODBC connection to connect to a CSV file. You just need to point the ODBC connection at the folder the files are in:

 Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\\\SERVER_NAME\\FOLDER_NAME\\SUB_FOLDER_NAME";Extended Properties="text;HDR=YES;FMT=Delimited"

(Note the double backslashes and header property HDR)

Then select from the filename in your query:

 SELECT * FROM FILENAME.TXT

Attach a video link here: How to use CSV file as Data Source in SSRS Report-SSRS Interview Questions and Answers.
Hope this helps.
Best Regards,
Joy


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The explanation in the video is very detailed.

0 Votes 0 ·

1) When you say "use an ODBC connection to connect to a CSV file" ... can you elaborate on exactly what you mean please?
a) create an ODBC 32 bit DSN (System or File or User type?)? OR
b) create an ODBC 64 bit DSN (System or File or User type?)? OR
c) create an SSRS Report Data Source, connection type = ODBC, (similar) connection string = Provider=Microsoft.Jet.OLEDB.4.0;Data Source="\\\\SERVER_NAME\\FOLDER_NAME\\SUB_FOLDER_NAME";Extended Properties="text;HDR=YES;FMT=Delimited"
when I click 'Test Connection', this errors with: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
So I'm unable to get this to work.

2) As per the YouTube video you linked to which shows to create a 32 bit ODBC System DSN then create an SSRS Report with an 'ODBC' Data Source and select the System DSN, so the connection string will just show "DSN=MyDsnName", not the build string you supplied earlier in your post.
Unfortunately this does not work for me. When I create the dataset in the new SSRS Report & click 'Refresh Fields', the fields are called F1, F2, etc instead of per Line 1 in the CSV.
Then when I try to run the new report in ReportServer, it errors with "An error has occurred during report processing. (rsProcessingAborted) Cannot create a connection to data source 'blah'. (rsErrorOpeningConnection) ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

0 Votes 0 ·

1 cont) If I select "OLE DB" instead of "ODBC" and use the connection string then click Test Connection, it then errors with "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."

0 Votes 0 ·