Issue XMLA queries via SSIS

Hello,

We are in a similar situation at my client... we wish to issue the CREATE and DELETE XMLA scripts for a specific cube via SSIS, however are unsure as to the control object to use...

More info regarding our processes:

OUR SSIS flow is designed to:

1.) backup an existing cube

2.) create a new cube leaving the original in place for users to use while the new one is building

3.) once the newly built cube has been validated, drop the orignal cube, and rename the newly built cube back to the original cube's name

We used DDL code files (DDL Task Objects within SSIS) which contain the XMLA query code for each CREATE, DELETE, ALTER statements used for each specific task:

The flow:

1.) BACKUP Original_Cube

2.) CREATE New_Cube

3.) PROCESS New Cube

4.) DELETE Original Cube

5.) ALTER New_Cube to Original Cube

In order to execute each DDL Task code set for a specific task, it is necessary to specify a connection to a given catalogue... however, in our case, and in the general case of using CREATE/DELETE/ALTER ddls, a given database & catalogue may or may not always be available...

it would be ideal for there to be a way to issue an XMLA script to CREATE a DUMMY CUBE (outline and model only) for all other DDLs to use as a basis (at the beginning of our SSIS Flow), and then issue another XMLA script to DELETE the DUMMY cube when it is no longer needed (at the end of our SSIS Flow)

Currently, the flow does work fine, however, the DUMMY CUBE must exist on the Server in order to provide a connection for each DDL Task... at this point, the creation of this DUMMY CUBE has to be a manual process, we are looking to automate this....

the "Script Task" object bumps you into a VB window asking you to provide a VB script... is there a way to have VB issue an XMLA command...? Or, is there an easier way around this...?

THANK YOU!

Michael

Hi Michael,

I'm hoping there is an easy answer to this. The "Execute DDL Task" should do all you need to do. Anything you can do with XMLA can be done with that task.

I've got alot of XMLA material here: http://blogs.conchango.com/jamiethomson/archive/tags/XMLA/default.aspx and alot of that talks about issuing XMLA from SSIS. Particularly this one:

Process SSAS dimensions and measure groups individually

(http://blogs.conchango.com/jamiethomson/archive/2006/07/18/SSIS_2F00_SSAS_3A00_-Process-SSAS-dimensions-and-measure-groups-individually.aspx)

-Jamie