Using Analysis Services Execute DDL Task in SSIS

SQL Server Integration Services (SSIS) is a Business Intelligence tool used to perform Extract, Transform & Load (ETL) operations. There are few tasks available in SSIS control flow to create, drop, update, and process CUBE objects. There are different types of Analysis Services tasks available in SSIS. For example:

• Analysis Services Execute DDL Task
• Analysis Services Processing Task
• Data Mining Query Tasks

 This post will explain you how to use Analysis Services Execute DDL Task.

Analysis Services Execute DDL Task

SQL Server Analysis Services Execute DDL Task can be used to create, modify, and delete Analysis Services objects. The Analysis Services Execute DDL Task is similar to the Execute SQL Task, the difference is that using the Analysis Services Execute DDL Task we can issue DDL statements against an Analysis Services. The DDL statements can be used to create cubes, dimensions, KPI’s, Calculation, Cube Partitions Roles or any other OLAP objects.

 The Analysis Services Processing Task can be used to process analysis services objects such as cubes, and dimensions.

How to use Analysis Services Execute DDL Tasks
In this example, I will be using Analysis Services Execute DDL Task to create a database in Analysis Services. To begin, suppose you have "Sales" database in Analysis Services in Development environment and you want to create the same database in new environment (e.g. Test, Production). Below are the steps to achieve this goal:
Step 1: Connect to Analysis Services in development environment and generate XMLA script of "Sales: database. Save this file with Sales.xmla name to physical location e.g. D:\Test\Sales.xmla

Stpe 2: Create new SSIS package. Add new Analysis Services connection manager. Give required Server name and leave Initial Catalog blank. Click on Test Connection to make sure it is connected and finally click OK to close Connection Manager wizard.

Stpe 3: You can add a package variable "ServerName" to assign SSAS server name. Map this variable with ServerName in Expression of connection manager properties as shown below. Make sure this variable is configured in config file.

Step 4: Create new file connection manager for xmla file. This Connection Manager will be renamed with "Sales.xmla".

Step 5: Drag and Drop Analysis Services Execute DDL Task. Rename this with "Analysis Services Execute DDL Task - Create Sales Cube". Now Double click on the task to open Analysis Services Execute DDL task Editor. Set Connection as Name "localhost.SSAS", Source Type as "File Connection", and Source as "Sales.xmla". Click OK to complete.

Step 6: Save this package. Now you can move this package along with sales.xmla file and config file to any environment and execute. It will create Sales cube in that box. Make sure that the path for xmla file is same as development environment othewise you need to add the path in config file to make it independent of environment.

Note: Before executing this package, change the value of ServerName variable with current environment in config file. Click here to see how to create config file in SSIS.