• Article

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.

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

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.

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:

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

Configuring the Analysis Services Execute DDL Task Programmatically

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

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:

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.