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

Shell 21 Reputation points
2021-09-21T00:03:02.8+00:00

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
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,849 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-09-21T05:05:58.01+00:00

    Hi @Shell

    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.