How to check the existence of an OLAP database?

Question

Is there some way to check the existence of an OLAP db on AS server? I want to put such kind of script in my SSIS package to determine whether the SSIS package should recreate the OLAP db.

Thanks a lot!

Answer 1

Here are some places to start

http://www.ssas-info.com/tags/dmv

---

FJK

Answer 2

Hi,

In addition, you can use $SYSTEM.DBSCHEMA_CATALOGS to query the available SSAS databases:

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

Or check a certain database:

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

  where [CATALOG_NAME] = 'Adventure Works DW 2008'

Just create Execute SQL Task in your package. 

 

Hope this helps,

Raymond

---

Raymond Li - MSFT

Answer 3

You don't say what version of AS you are using. So, if the DMV is not available you can also add a script task and use AMO/DSO to connect to the server and determine whether or not the database exists. You can then set an SSIS variable accordingly and alter your control flow based on that variable to either create the database or not.

Based on there being no response here for a while I won't bother creating a sample AMO script etc. but can if required. However, the DMV route is by far the easiest if you are using AS2008.

Answer 4

You can also try the Analysis Services Stored Procedure Project:

http://asstoredprocedures.codeplex.com/wikipage?title=Discover&referringTitle=XmlaDiscover

Raymond

---

Raymond Li - MSFT

Answer 5

Hi Raymond,

>Just create Execute SQL Task in your package.

What ConnectionType should I use in the Execute SQL Task in order to run this check? I tried all, but I cannot see my OLAP connection manager in "Connection" field. (I use MS SQL 2012.)

Thanks,

Petr