analysis-services-execute-ddl-task.md

title	Analysis Services Execute DDL Task
description	Analysis Services Execute DDL Task
author	chugugrace
ms.author	chugu
ms.date	03/14/2017
ms.service	sql
ms.subservice	integration-services
ms.topic	conceptual
f1_keywords	sql13.dts.designer.asexecuteddltask.f1 sql13.dts.designer.asexecuteddltask.general.f1 sql13.dts.designer.asexecuteddltask.ddl.f1
helpviewer_keywords	Analysis Services Execute DDL task DDL

Analysis Services Execute DDL Task

[!INCLUDEsqlserver-ssis]

The [!INCLUDEssASnoversion] 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 [!INCLUDEssAWDWsp], the sample [!INCLUDEssASnoversion] database included in [!INCLUDEssNoVersion].

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

[!INCLUDEssISnoversion] 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

DDL Statements

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

• ASSL is used to define and describe an instance of [!INCLUDEssASnoversion] and the databases and database objects it contains. For more information, see Analysis Services Scripting Language (ASSL for XMLA).

• XMLA is a command language that is used to send action commands, such as Create, Alter, or Process, to an instance of [!INCLUDEssASnoversion]. For more information, see XML for Analysis (XMLA) Reference.

If the DDL code is stored in a separate file, the [!INCLUDEssASnoversion] 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 [!INCLUDEssASnoversion] Execute DDL tasks should use the package protection level EncryptAllWithUserKey or EncryptAllWithPassword. For more information, see Integration Services (SSIS) Packages.

DDL Examples

The following three DDL statements were generated by scripting objects in the [!INCLUDEssAWDWsp], the [!INCLUDEssASnoversion] database included in [!INCLUDEssNoVersion].

The following DDL statement deletes the Promotion dimension.

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

The following DDL statement processes the [!INCLUDEssAWDWsp] cube.

<Batch xmlns="http://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 Multidimensional 2012</DatabaseID>  
      </Object>  
      <Type>ProcessFull</Type>  
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
    </Process>  
  </Parallel>  
</Batch>  
  

The following DDL statement creates the Forecasting mining model.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <ParentObject>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</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>  
  

The following three DDL statements were generated by scripting objects in the [!INCLUDEssAWDWsp], the [!INCLUDEssASnoversion] database included in [!INCLUDEssNoVersion].

The following DDL statement deletes the Promotion dimension.

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

The following DDL statement processes the [!INCLUDEssAWDWsp] cube.

<Batch xmlns="http://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 Multidimensional 2012</DatabaseID>  
      </Object>  
      <Type>ProcessFull</Type>  
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
    </Process>  
  </Parallel>  
</Batch>  
  

The following DDL statement creates the Forecasting mining model.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <ParentObject>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</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>  
  

Configuration of the Analysis Services Execute DDL Task

You can set properties through [!INCLUDEssIS] Designer or programmatically.

For more information about the properties that you can set in [!INCLUDEssIS] Designer, click the following topic:

• Expressions Page

For more information about setting these properties in [!INCLUDEssIS] Designer, click the following topic:

• Set the Properties of a Task or Container

Programmatic Configuration of the Analysis Services Execute DDL Task

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

• xref:Microsoft.DataTransformationServices.Tasks.DTSProcessingTask.ASExecuteDDLTask

Analysis Services Execute DDL Task Editor (General Page)

Use the General page of the Analysis Services Execute DDL Task Editor dialog box to name and describe the [!INCLUDEssASnoversion] Execute DDL task.

Options

Name
Provide a unique name for the [!INCLUDEssASnoversion] Execute DDL task. This name is used as the label in the task icon.

Note

Task names must be unique within a package.

Description
Type a description of the [!INCLUDEssASnoversion] Execute DDL task.

Analysis Services Execute DDL Task Editor (DDL Page)

Use the DDL page of the Analysis Services Execute DDL Task Editor dialog box to specify a connection to an [!INCLUDEssASnoversion] project or an [!INCLUDEssASnoversion] database and to provide information about the source of data definition language (DDL) statements.

Static Options

Connection
Select an [!INCLUDEssASnoversion] project or an [!INCLUDEssASnoversion] connection manager in the list, or click <New connection...> and use the Add Analysis Services Connection Manager dialog box to create a new connection.

Related Topics: Add Analysis Services Connection Manager Dialog Box UI Reference, Analysis Services Connection Manager

SourceType
Specify the source type of the DDL statements. This property has the options listed in the following table:

Value	Description
Direct Input	Set the source to the DDL statement stored in the SourceDirect text box. Selecting this value displays the dynamic options in the following section.
File Connection	Set the source to a file that contains the DDL statement. Selecting this value displays the dynamic options in the following section.
Variable	Set the source to a variable. Selecting this value displays the dynamic options in the following section.

Dynamic Options

SourceType = Direct Input

Source
Type the DDL statements or click the ellipsis (...) and then type the statements in the DDL Statements dialog box.

SourceType = File Connection

Source
Select a File connection in the list, or click <New connection...> and use the File Connection Manager dialog box to create a new connection.

Related Topics: File Connection Manager

SourceType = Variable

Source
Select a variable in the list, or click <New variable...> and use the Add Variable dialog box to create a new variable.

Related Topics: Integration Services (SSIS) Variables