Analysis Services Execute DDL Task

The Analysis Services Execute DDL task runs data definition language (DDL) statements that can create, drop, or alter mining models and multidimensional objects such as cubes and dimensions. For example, a DDL statement can create a partition in the Adventure Works cube, or delete a dimension in Adventure Works DW, the sample Analysis Services database included in SQL Server 2005.

The DDL statements are represented as statements in Analysis Services Scripting Language (ASSL), and framed in an XML for Analysis (XMLA) command.

• ASSL is used to define and describe an instance of Analysis Services and the databases and database objects it contains. For more information, see Analysis Services Scripting Language Reference.
• XMLA is a command language that is used to send action commands, such as Create, Alter, or Process, to an instance of Analysis Services. For more information, see XML for Analysis Reference (XMLA).

The Analysis Services Execute DDL task uses an Analysis Services connection manager to connect to an instance of Analysis Services or an Analysis Services project. For more information, see Analysis Services Connection Manager.

If the DDL code is stored in a separate file, the Analysis Services Execute DDL task uses a File connection manager to specify the path of the file. For more information, see File Connection Manager.

Because DDL statements can contain passwords and other sensitive information, a package that contains one or more Analysis Services Execute DDL tasks should use the package protection level EncryptAllWithUserKey or EncryptAllWithPassword. For more information, see Integration Services Packages.

DDL Examples

The following three DDL statements were generated by scripting objects in the Adventure Works DW, the Analysis Services database included in SQL Server 2005.

The following DDL statement deletes the Promotion dimension.

<Delete xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
    <Object>
        <DatabaseID>Adventure Works DW</DatabaseID>
        <DimensionID>Dim Promotion</DimensionID>
    </Object>
</Delete>

The following DDL statement processes the Adventure Works DW cube.

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
  <Parallel>
    <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <Object>
        <DatabaseID>Adventure Works DW</DatabaseID>
      </Object>
      <Type>ProcessFull</Type>
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
    </Process>
  </Parallel>
</Batch>

The following DDL statement creates the Forecasting mining model.

<Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>Adventure Works DW</DatabaseID>
        <MiningStructureID>Forecasting</MiningStructureID>
    </ParentObject>
    <ObjectDefinition>
        <MiningModel xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <ID>Forecasting</ID>
            <Name>Forecasting</Name>
            <Algorithm>Microsoft_Time_Series</Algorithm>
            <AlgorithmParameters>
                <AlgorithmParameter>
                    <Name>PERIODICITY_HINT</Name>
                    <Value xsi:type="xsd:string">{12}</Value>
                </AlgorithmParameter>
            </AlgorithmParameters>
            <Columns>
                <Column>
                    <ID>Amount</ID>
                    <Name>Amount</Name>
                    <SourceColumnID>Amount</SourceColumnID>
                    <Usage>Predict</Usage>
                </Column>
                <Column>
                    <ID>Model Region</ID>
                    <Name>Model Region</Name>
                    <SourceColumnID>Model Region</SourceColumnID>
                    <Usage>Key</Usage>
                </Column>
                <Column>
                    <ID>Quantity</ID>
                    <Name>Quantity</Name>
                    <SourceColumnID>Quantity</SourceColumnID>
                    <Usage>Predict</Usage>
                </Column>
                <Column>
                    <ID>Time Index</ID>
                    <Name>Time Index</Name>
                    <SourceColumnID>Time Index</SourceColumnID>
                    <Usage>Key</Usage>
                </Column>
            </Columns>
            <Collation>Latin1_General_CS_AS_KS</Collation>
        </MiningModel>
    </ObjectDefinition>
</Create>

Configuring the Analysis Services Execute DDL Task

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, click one of the following topics:

• Analysis Services Execute DDL Task Editor (General Page)
• Analysis Services Execute DDL Task Editor (DDL Page)
• Expressions Page

For more information about setting these properties in SSIS Designer, click the following topic:

• How to: Set Task Properties

Configuring the Analysis Services Execute DDL Task Programmatically

For more information about programmatically setting these properties, click the following topic:

• ASExecuteDDLTask

Other Related Tasks

Integration Services includes a number of tasks that perform business intelligence operations, such as processing analytic objects and running data mining prediction queries.

For more information about related business intelligence tasks, click one of the following topics:

• Analysis Services Processing Task
• Data Mining Query Task

See Also

Concepts

Integration Services Tasks
Creating Package Control Flow

Other Resources

Analysis Services Scripting Language (ASSL)
XML for Analysis (XMLA)
Building Analysis Services Projects
Deploying an Analysis Services Database into the Production Environment

Help and Information

Getting SQL Server 2005 Assistance