SSIS as a data source needed ? Watch out for configuration traps

  • Article

 

Someone pinged me according the article I wrote about SSIS as a data source in Reporting Services. In general this is easy to implement and configure. One problem though occurs if you already had SQL Server 2005 on the machine first and updated to SQL Server 2008.

You will receive the following error:

The data processing extension used for this report is not available. It has either been uninstalled, or it is not configured correctly.

According to the configuration file RSReportServcer.config, you will see the following entry, so what is wrong? After configuring the server (un-commenting the SSIS entry and restarting the Report Server) you will receive that while selecting the data source in the WebUI or executing the report that references the data source.

The un-commented entry for SSIS looks like the following:

<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Visible="true"/>

The referenced assembly is located in the folder of SQL Server 100/DTS/Binn. If you look at the properties of the file, you will see that the file version is the version of SQL Server (depends on your service pack) and that the assembly version is e.g. 10.0.0.0. So a wrong assembly is used in the reference. Change the assembly version to reflect (Your settings may vary) the located assembly version like:

<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Visible="true"/>

And voilá, everything works after restarting the Report Server !

-Jens