Analysis Services Execute DDL Task

DDL Means Data Definition Language. This Task runs DDL Statements that can create ,drop or alter the mining models and Multi dimensionals objects such as cubes and dimensions.

For Example, by using this task with proper DDL, we can create Partition in cube the (OLAP Databases).

DDL Statements are represented as statements in

• Analysis Services Scripting Language (ASSL) - It is used to define and describe an Instance of Analysis Services, Databases and Database Objects
  
• XML For Analysis (XMLA) Command - It is used to send action command such as alter, create, drop or process the OLAP Objects

We need Analysis Services connection to execute this task, also if we want to execute DDL which is stored in the separate file, then we need to create File connection to that file

Configuring the ActiveX Script Task

You can set properties programmatically or through SSIS Designer.

To Set the Properties by SSIS Designer:

• Place "Activex Script Task" on Control Flow Designer From "Contol Flow Items" Tool Box
• double Click on this task
• Activex Script Task Editor will display which contains 3 tabs namely General, Script and Expressions
• In General Tab, We can update the name and Description of the Task, As shown in the Following Figure

• In Script Tab,
  

• we can update following details about the script
  

1. language used in script, this will helps to the compiler for compile our code by using to proper language compiler
   
2. script, which will be executed by Activex script Task at run time
3. Entry Method, here we need provide a method name which is available in the script (script available in step 2), it is a entry point, from where the script will start executing. it is same as Main() function in C, C++, C#

Next is the Expression Tab, here we can assign the value of the property dynamically by executing some expression, calculation, etc.,

Example Script for Exercise:

Language: VB Script Language

Script:

Function Msg()
Msgbox("Hi")
End Function

Entry Method: Msg

Assign these values in script Tab of this task, then execute

you can the Message box with "Hi" Message.