CODEWITHSUNDEEP
sql-serverssisssasscript-taskssdt-bi
Andrea K
Andrea K

Reputation: 31

How to process SSAS 2012 Cube with SSIS 2014 and script task

I also posted this question here: https://social.msdn.microsoft.com/Forums/en-US/5aca721a-1148-451f-bac6-d1bee6b2aad7/how-to-process-ssas-2012-cube-with-ssis-2014-and-script-task?forum=sqlintegrationservices

For processing different SSAS databases on different servers, I use a similar approach as described here (#2, Using AMO (Analysis Management Objects)):

http://aniruddhathengadi.blogspot.in/2011/12/how-to-automate-cube-processing.html

This worked well for processing cubes in SQL Server 2008 R2 and 2012, but SSAS and SSIS were always the same version.

Now I have a different constellation: Integration Services run on one server, in version 2014, the Analysis Services instance to process the cube database on runs on another server, version 2012.

I tried several different combinations of SSIS version and Analysis Management Objects version, and got several errors while running the process package (e.g. object reference not set to an instance of an object, cannot find AnalyisServices.dll..)

Now before I do more trial and error I have some general questions on the right way to accomplish this - maybe someone already has done this successfully:

  1. Is this combination 2014/2012 possible at all?
  2. I assume the BIDS version has to be for SQL Server 2014, as I want to run SSIS packages on a 2014 server, is that correct? Does it matter at all, can I also deploy 2012 packages?
  3. Which version of Analysis Management Objects do I have to use? I assumed I have to use version 11.0 here, because I want to process a 2012 cube?
  4. If it is possible to use the "old" 11.0 version of AMO, do I have to do anything so that it can be found by the SSIS package running on the server (it was built on my local computer, there I have all SQL Server versions from 2005 to 2014 installed in parallel), or do I just have to copy it to the appropriate SQL Server folder?

Thanks in advance for every hint!

Upvotes: 3

Views: 853

Answers (1)

vvvv4d
vvvv4d

Reputation: 4095

To process an SSAS Cube with SSIS, I recommend not doing this in a Script Task and instead using an Execute SQL Server Agent Job Task.

First setup a SQL Server Agent job that uses a SQL Server Analysis Services Command Step to refresh your SSAS cube. Below is the JSON code you will need for this step. Update the database property to have your actual database name.

 {
   "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "YourDatabase"
      }
    ]
  }
}

Now in your SSIS package you can use an Execute SQL Server Agent Job task run the SQL Server Agent job you created to fresh your SSAS cube.

Upvotes: 1

Related Questions