SSRS and CSV format

Using SQL Server 2005 Reporting Services, I created a timed subscription to save a report as a CSV file to a Windows file share. However, the user was unable to import the file into their salesforce.com application.

Turns out that the default export format for CSV uses Unicode encoding, so if you open the file with Excel, each row of the data ends up in the first column.

Luckily SSRS has a file rsreportserver.config where you can add or modify the export formats. The config file is an xml file which in my case was located at

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer

(note: the wysiwyg on blogger has issues with angle brackets, so I am showing square brackets below)

Locate the section [Render] and you will find entries for all the formats; find the line that begins with [Extension Name = "CSV" and add the Configuration, Device Info, and Encoding:

[Render]
[Extension Name="CSV" Type=" ..."]
[Configuration]
[DeviceInfo]
[Encoding]ASCII[/Encoding]
[/DeviceInfo]
[/Configuration]
[/Extension]
[/Render]

Note: for the "Extension Name" entry, the default config file has a closing slash "/" that has to be removed for this to work.

After making this change, just to be safe I stopped and re-started IIS. After which it worked just fine, delivering an ASCII-encoded file that could be opening with Excel 2003 and imported into Salesforce.