Processing Analysis Services 2005 Cube from SSIS 2008

I recently faced a particular problem; one of our server crashed and I had to move our SSAS 2008 cube to a 2005 instance. So, we redid the cube in BIDS 2005, which was easy since the cube is based on a star schema in our SQL Server database. The cube processing is made from a package in SSIS (2008).

Everything worked well when all components were in 2008. But now, with the cube sitting on a 2005 instance, we faced a particular issue: an error occurred when SSIS tried to process the dimensions of the cube. The error claimed that we did not use the right SSAS client when connecting to the cube. Fine, we changed the configuration of the SSAS connection manager to use the 2005 version of the client…only to find out that the 2005 client was not installed on the server. And, surely, we did not wanted to take the risk to install a previous version of the client on the server.

So, the solution I ended out using was to split the processes. My data warehouse would still be processed from my 2008R2 SSIS packages on my 2008R2 server but my cube would be processed from a SSIS 2005 package that sits on a 2005 server. The way I did it is to have an Execute SQL task that would call a job via msdb..sp_start_job on the 2005 server. Now, since msdb..sp_start_job starts jobs and exits, I called instead a stored procedure that run jobs synchronously. The job simply calls the SSIS 2005 package that processes the dimensions and partitions of my Analysis Services 2005 cube.

To sum up, I have been able to process my SSAS 2005 cube from a 2008 SSIS package that sits on a server without SSAS 2005 client installed by having SSIS 2008 firing a job on the 2005 instance. This simple solution saved from having the SSAS 2005 installed onto the 2008R2 Server and potentially messes up with the SSAS 2008 client already installed.

Christian