Search code examples
ssisssasolap

Get last cube processed date in SSIS

I need to get last processed date of SSAS cube in SSIS and save it into a variable.

I've tried a "Execute SQL task":

SELECT LAST_DATA_UPDATE as LAST_DT FROM $system.mdschema_cubes
WHERE CUBE_NAME = 'CubeName'

It works ok in MSSQL management studio MDX query window but in SSIS it says: Unsupported data type on result set binding.

Then I've tried:

WITH MEMBER [Measures].[LastProcessed] AS ASSP.GetCubeLastProcessedDate() SELECT [Measures].[LastProcessed] ON 0 FROM [CubeName]

And it says '[ASSP].[GetCubeLastProcessedDate]' function does not exist.

Any ideas how to do this? Thank you

Solution

  • A linked server might be your best option;

    Create the linked server with the following, changing as appropriate: 

    EXEC master.dbo.sp_addlinkedserver 
@server = N'LINKED_SERVER_OLAP_TEST', --Change to a suitable name
@srvproduct='', --Creates the productname as blank
@provider=N'MSOLAP', --Analysis Services
@datasrc=N'localhost', --Change to your datasource
@catalog=N'TESTCUBE' --Change to set the default cube

    Change the data source of your Execute SQL Task to make sure it is pointing to any of the databases where the linked server is hosted, I.E. don't use an analysis service datasource use a standard OLE DB. Then have the following in your execute SQL task (Changing as appropriate).

    SELECT * 
FROM OpenQuery(LINKED_SERVER_OLAP_TEST,'SELECT LAST_DATA_UPDATE as LAST_DT FROM $system.mdschema_cubes
WHERE CUBE_NAME = ''CUBENAME''')

    Set the variable to be DATETIME and the result set to be single row.

    There may well be other ways to do this, however I have always found this method the most straight forward.