SSRS: Using SSIS as Data Source - a gotcha

Although using SSIS as a data source for SSRS isn't a supported configuration in SQL Server and has indeed been deprecated from 2008R2, i've been trying to make use of it for a particular scenario (using SQL2008). Its not the most straight forward of processes as it requires changes to config files, careful use of dataset names and plenty of security jiggery pokery but i'm not going to touch on that here.

The issue I came across was due to me having SQL2005 on my machine previous to upgrading to 2008. When I developed my report with SSIS as the datasource in BIDS, everything worked perfectly and it was only when it was deployed to the report server that I got the error message:


Initially, I thought it was a permissions problem (as thats what the majority of issues appear to be using this configuration) but after exhausting all other possibilities and actually READING the error message it became apparant that the issue was a little more fundamental than that. Logging had shown that the package wasn't even being executed and the message bears that out. The issue seemed to be more that the command being passed to the SSIS engine wasn't correct. It was then that i'd remembered that I'd upgraded and there was a good chance that it was trying to call my SQL2005 version of DTEXEC. Fortunately, i'd seen another post from Jens which suggested how the problem might be fixed.

Hey presto, changing the version of the SSIS extension element in rsreportserver.config did the trick (with a restart of the SSRS service).



TO:



So I may still have plenty of security issues to resolve and as the feature is no longer supported in SQL2008R2 and above, I doubt whether I'll go ahead and use it in Production but hopefully someone may find this useful.