Biml

https://docs.varigence.com/biml-reference/
Admin
Biml (Business Intelligence Markup Language) is an XML-based language for automating and modeling...
Tokens:39,876
Snippets:293
Trust Score:8.7
Update:2 months ago
Show doc for...
Tokens:
Raw
### Define Keys in Biml

Source: https://docs.varigence.com/biml-reference/snippets/tables-keys

Example showing the implementation of Identity, UniqueKey, and PrimaryKey within a Biml Table definition.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Tables>  

    <Table Name="SalesOrderDetail" ConnectionName="AdventureWorksLT2008" SchemaName="SalesLT" >  

      <Columns>  

        <TableReference ForeignKeyNameOverride="FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID" TableName="SalesLT.SalesOrderHeader" Name="SalesOrderID" />  

        <Column Name="SalesOrderDetailID" />  

        <Column Name="OrderQty" DataType="Int16" />  

        <Column Name="UnitPrice" DataType="Currency" />  

        <Column Name="UnitPriceDiscount" DataType="Currency" Default="((0.0))" />  

        <Column Name="LineTotal" DataType="Decimal" Precision="38" Scale="6" />  

        <Column Name="rowguid" DataType="Guid" Default="(newid())" />  

        <Column Name="ModifiedDate" DataType="DateTime" Default="(getdate())" />  

      </Columns>  

      <Keys>  

        <!-- This sample demonstrates using the three types of keys.  

        Identity Keys combine a Primary Key and Identity Together in one combination -->  

        <Identity Name="IK_SalesOrderDetail">  

          <Columns>  

            <Column ColumnName="SalesOrderDetailID" />  

          </Columns>  

        </Identity>  

        <UniqueKey Name="AK_SalesOrderDetail_rowguid">  

          <Columns>  

            <Column ColumnName="rowguid" />  

          </Columns>  

        </UniqueKey>  

        <PrimaryKey Name="PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID">  

          <Columns>  

            <Column ColumnName="SalesOrderID" />  

            <Column ColumnName="SalesOrderDetailID" />  

          </Columns>  

        </PrimaryKey>  

      </Keys>  

    </Table>  

  </Tables>  

</Biml>
```

--------------------------------

### Unpivot Transformation Biml Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-unpivot

Demonstrates the configuration of an Unpivot transformation following a Pivot transformation to restore original data structure.

```Biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

            <DirectInput>select * from SurveyResponses order by surveyid, attribute, response</DirectInput>  

          </OleDbSource>  

  

          <Pivot Name="Pivot Transformation">  

            <InputPath OutputPathName="SurveyResponses.Output" />  

            <Inputs />  

            <Column SourceColumn="Response" PivotUsage="Output" />  

            <Column SourceColumn="Attribute" PivotUsage="Pivot" />  

            <Column SourceColumn="SurveyId" PivotUsage="Key" />  

            </Inputs>  

  

            <Outputs>  

              <Column SourceColumn="Response" PivotKeyValue="Name" TargetColumn="Name" />  

              <Column SourceColumn="Response" PivotKeyValue="Weight" TargetColumn="Weight" />  

              <Column SourceColumn="Response" PivotKeyValue="Sport" TargetColumn="Sport" />  

            </Outputs>  

          </Pivot>  

  

          <!-- After pivoting, this code performs an unpivot, so that the resulting data will be the same as the input  

          to the pivot (although the order may have changed).  -->  

          <Unpivot Name="Univot Transformation" PivotKeyValueColumnDataType="String" PivotKeyValueColumnName="Attribute" PivotKeyValueColumnLength="10" AutoPassThrough="false">  

            <InputPath OutputPathName="Pivot Transformation.Output" />  

            <Columns>  

              <Column PivotKeyValue="Name" SourceColumn="Name" TargetColumn="Response" />  

              <Column PivotKeyValue="Weight" SourceColumn="Weight" TargetColumn="Response" />  

              <Column PivotKeyValue="Sport" SourceColumn="Sport" TargetColumn="Response" />  

            </Columns>  

          </Unpivot>  

  

          <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" Overwrite="true" />  

        </Transformations>  

      </Dataflow>  

    </Tasks>  

  </Package>  

</Packages>  

</Biml>
```

--------------------------------

### Execute Multiple Packages with Precedence Constraints

Source: https://docs.varigence.com/biml-reference/snippets/ssis-tasks-execute-package

This example demonstrates how to define a series of ExecutePackage tasks that run in a specific order using PrecedenceConstraints.

```xml
<Packages>  

    <Package Name="Rebuild Warehouse Schema" ConstraintMode="Parallel">  

        <!-- Executes a series of four packages -->  

        <Tasks>  

            <ExecutePackage Name="Create Database">  

                <Package PackageName="Create Database" />  

            </ExecutePackage>  

            <ExecutePackage Name="Create Tables">  

                <PrecedenceConstraints>  

                    <Inputs />  

                        <Input OutputPathName="Create Database.Output" />  

                    </Inputs>  

                </PrecedenceConstraints>  

                <Package PackageName="Create Tables" />  

            </ExecutePackage>  

            <ExecutePackage Name="Create Scalar-valued Functions">  

                <Package PackageName="Create Scalar-valued Functions" />  

                <PrecedenceConstraints>  

                    <Inputs />  

                        <Input OutputPathName="Create Tables.Output" />  

                    </Inputs>  

                </PrecedenceConstraints>  

            </ExecutePackage>  

            <ExecutePackage Name="Create Database Triggers">  

                <Package PackageName="Create Database Triggers" />  

                <PrecedenceConstraints>  

                    <Inputs />  

                        <Input OutputPathName="Create Scalar-valued Functions.Output" />  

                    </Inputs>  

                </PrecedenceConstraints>  

            </ExecutePackage>  

        </Tasks>  

    </Package>  

</Packages>
```

--------------------------------

### Multicast Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-multicast

Demonstrates how to use the Multicast transformation to split an OLE DB source into two separate flat file destinations.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments">  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

  

          <!-- Distributes the input to two outputs, MultiOut0 and MultiOut1 -->  

            <Multicast Name="Multicast Transformation">  

              <OutputPaths>  

                <OutputPath Name="MultOut0" />  

                <OutputPath Name="MultOut1" />  

              </OutputPaths>  

            </Multicast>  

  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection">  

              <InputPath OutputPathName="Multicast Transformation.MultOut0" />  

  

            </FlatFileDestination>  

            <FlatFileDestination Name="OutputFile2" ConnectionName="FlatFileConnection2">  

              <InputPath OutputPathName="Multicast Transformation.MultOut1" />  

            </FlatFileDestination>  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Union All Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-union-all

Combines data from two OleDbSource components into a single FlatFileDestination.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

            <DirectInput>select * from SurveyResponses</DirectInput>  

          </OleDbSource>  

  

          <OleDbSource Name="JuniorSurveyResponses" ConnectionName="SportsData">  

          <DirectInput>select * from JuniorSurveyResponses</DirectInput>  

        </OleDbSource>  

  

        <!-- Does a union of the SurveyResponses and JuniorSurveyResponses to the flat file specified by FlatFileConnection. -->  

          <UnionAll Name="Union All Transformation">  

            <InputPaths />  

            <InputPath OutputPathName="SurveyResponses.Output" />  

            <InputPath OutputPathName="JuniorSurveyResponses.Output" />  

            </InputPaths>  

          </UnionAll>  

  

          <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" />  

  

        </Transformations>  

      </Dataflow>  

    </Tasks>  

  </Package>  

</Packages>  

</Biml>
```

--------------------------------

### Import Column Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-import-column

Demonstrates the use of an ImportColumn transformation to map a 'FileName' column from a Script Component source to a Flat File destination.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My New Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <ScriptComponentSource ProjectCoreName="SC_eb1debcd2374468ebccbbfad4fbe5976" Name="Script Component Source">  

              <ScriptComponentProjectReference ScriptComponentProjectName="Component Script Project2" />  

  

            </ScriptComponentSource>  

  

            <!-- Import column from the above Script Component and create the column named FileName -->  

              <ImportColumn Name="Import Column Transformation">  

                <InputPath OutputPathName="Script Component Source.Output0" />  

                <Columns>  

                  <Column SourceColumn="FileName" TargetColumn="FileName" />  

  

                </Columns>  

              </ImportColumn>  

              <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection">  

  

                <InputPath OutputPathName="Import Column Transformation.Output" />  

  

              </FlatFileDestination>  

            </Transformations>  

          </Dataflow>  

        </Tasks>  

      </Package>  

    </Packages>  

  </Biml>
```

--------------------------------

### Row Sampling Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-row-sampling

Demonstrates the use of the RowSampling element within a Dataflow to extract a random sample of 25 rows.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear" >  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments">  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

          <!-- Gets a random sample of 25 rows from DimInstrument -->  

            <RowSampling Name="Percentage Sampling Transformation" NumberOfRows="25" />  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" >  

  

            </FlatFileDestination>  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Sort Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-sort

Demonstrates sorting rows by a specific column and eliminating duplicates within a Biml dataflow.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear" >  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments">  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

          <!-- Sorts the rows based using the Category column as the sort key -->  

            <Sort Name="Sort Transformation" EliminateDuplicates="true" >  

              <Columns>  

                <Column Sort="true" SourceColumn="Category" />  

              </Columns>  

            </Sort>  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" >  

            </FlatFileDestination>  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Percentage Sampling Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-percentage-sampling

Configures a PercentageSampling transformation to output 20% of rows randomly from an OLE DB source to a flat file destination.

```Biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear" >  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments">  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

          <!-- Outputs 20% of the rows, randomly, from the input -->  

            <PercentageSampling Name="Percentage Sampling Transformation" PercentageOfRows="20" />  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" >  

  

            </FlatFileDestination>  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Copy Column Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-copy-column

Demonstrates the Copy Column transformation within a Biml package to duplicate the 'Category' column into 'SubCategory'.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear" >  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments">  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

          <!-- Copy the Category column to a new column called SubCategory -->  

            <CopyColumn Name="Copy Column Transformation">  

              <Columns>  

                <Column SourceColumn="Category" TargetColumn="SubCategory" />  

  

              </Columns>  

            </CopyColumn>  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" >  

  

            </FlatFileDestination>  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Implement Row Count Transformation

Source: https://docs.varigence.com/biml-reference/snippets/transformations-row-count

This example demonstrates how to count rows from an OLE DB source and store the result in a package variable named User.RowCount.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

            <DirectInput>select * from SurveyResponses</DirectInput>  

          </OleDbSource>  

  

          <!-- Outputs the row count to the User.RowCount variable -->  

            <RowCount Name="Row Count Transformation" VariableName="User.RowCount">  

              <InputPath OutputPathName="SurveyResponses.Output" />  

            </RowCount>  

  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" Overwrite="true" />  

  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

      <Variables>  

      <Variable Name="RowCount" DataType="Int32">0</Variable>  

    </Variables>  

  </Package>  

</Packages>  

</Biml>
```

--------------------------------

### Merge Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-merge

Demonstrates merging two OLE DB sources into a single output using the Merge transformation. Ensure both input sources are sorted by the specified columns.

```Biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

              <Columns>  

                <Column SourceColumn="SurveyId" SortKeyPosition="1" />  

                <Column SourceColumn="Attribute" SortKeyPosition="2" />  

                <Column SourceColumn="Response" SortKeyPosition="3" />  

              </Columns>  

            <DirectInput>select * from SurveyResponses order by surveyid, attribute, response</DirectInput>  

          </OleDbSource>  

  

          <OleDbSource Name="JuniorSurveyResponses" ConnectionName="SportsData">  

            <Columns>  

              <Column SourceColumn="SurveyId" SortKeyPosition="1" />  

              <Column SourceColumn="Attribute" SortKeyPosition="2" />  

              <Column SourceColumn="Response" SortKeyPosition="3" />  

            </Columns>  

          <DirectInput>select * from JuniorSurveyResponses order by surveyid, attribute, response</DirectInput>  

        </OleDbSource>  

  

        <!-- Merges SurveyResponses and JuniorSurveyResponses outputs -->  

          <Merge Name="Merge Transformation">  

            <LeftInputPath OutputPathName="SurveyResponses.Output" />  

            <RightInputPath OutputPathName="JuniorSurveyResponses.Output" />  

          </Merge>  

          <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" />  

        </Transformations>  

      </Dataflow>  

    </Tasks>  

  </Package>  

</Packages>  

</Biml>
```

--------------------------------

### Define a ScriptTaskProject in Biml

Source: https://docs.varigence.com/biml-reference/snippets/script-projects-script-task-project

This example demonstrates the XML structure for a ScriptTaskProject, including assembly references, file content for AssemblyInfo.cs and ScriptMain.cs, and variable declarations.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <ScriptProjects>  

    <ScriptTaskProject ProjectCoreName="ST_19338c2d3c5d43d1b77d01b996ae1485" Name="Simple Task Script Project">  

      <AssemblyReferences>  

        <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />  

        <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />  

        <AssemblyReference AssemblyPath="System.dll" />  

        <AssemblyReference AssemblyPath="System.AddIn.dll" />  

        <AssemblyReference AssemblyPath="System.Data.dll" />  

        <AssemblyReference AssemblyPath="System.Windows.Forms.dll" />  

        <AssemblyReference AssemblyPath="System.Xml.dll" />  

      </AssemblyReferences>  

      <Files>  

        <File Path="AssemblyInfo.cs">using System.Reflection;  

          using System.Runtime.CompilerServices;  

  

          [assembly: AssemblyTitle("ST_19338c2d3c5d43d1b77d01b996ae1485.csproj")]  

          [assembly: AssemblyDescription("")]  

          [assembly: AssemblyConfiguration("")]  

          [assembly: AssemblyCompany("Varigence")]  

          [assembly: AssemblyProduct("ST_19338c2d3c5d43d1b77d01b996ae1485.csproj")]  

          [assembly: AssemblyCopyright("Copyright @ Varigence 2011")]  

          [assembly: AssemblyTrademark("")]  

          [assembly: AssemblyCulture("")]  

  

        [assembly: AssemblyVersion("1.0.*")]</File>  

        <File Path="ScriptMain.cs">  

  

          using System;  

          using System.Data;  

          using Microsoft.SqlServer.Dts.Runtime;  

          using System.Windows.Forms;  

  

          [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]  

          public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase  

          {  

          enum ScriptResults  

          {  

          Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,  

          Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure  

          };  

  

          public void Main()  

          {  

          if (Dts.Variables["User::TestVariable"].ReadOnly)  

          {  

          Dts.Events.FireError(0, "Simple Task Script Project", "Variable is readonly", string.Empty, 0);  

          }  

  

          Dts.TaskResult = (int)ScriptResults.Success;  

          }  

          }  

        </File>  

      </Files>  

      <ReadOnlyVariables>  

        <Variable Namespace="User" DataType="Int32" VariableName="TestVariable" />  

      </ReadOnlyVariables>  

  

      <ReadWriteVariables />  

    </ScriptTaskProject>  

  </ScriptProjects>  

</Biml>
```

--------------------------------

### Define a Cube Perspective in Biml

Source: https://docs.varigence.com/biml-reference/snippets/cubes-perspective

This example shows the structure for a cube perspective, including its associated actions, calculations, KPIs, measure groups, and dimensions.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Cubes>  

    <Cube Name="Adventure Works" ConnectionName="AdvWorksCube">  

      <Perspectives>  

        <!--  

        This is a sample perspective for this Adventure Works cube. With Biml, it's easy to reference cube objects within the perspective.  

        -->  

        <Perspective Name="Direct Sales" DefaultMeasureName="FactInternetSales.Internet Sales Amount">  

          <Actions>  

            <Action ActionName="Internet Details" />  

          </Actions>  

          <Calculations>  

            <Calculation CalculationName="[Internet Gross Profit]" />  

          </Calculations>  

          <Kpis>  

            <Kpi KpiName="Growth in Customer Base" />  

          </Kpis>  

          <MeasureGroups>  

            <MeasureGroup CubeMeasureGroupName="Fact Internet Sales 1">  

              <Measures>  

                <Measure MeasureName="FactInternetSales.Internet Order Quantity" />  

                <Measure MeasureName="FactInternetSales.Internet Tax Amount" />  

                <Measure MeasureName="FactInternetSales.Internet Freight Cost" />  

                <Measure MeasureName="FactInternetSales.Internet Standard Product Cost" />  

                <Measure MeasureName="FactInternetSales.Internet Transaction Count" />  

                <Measure MeasureName="FactInternetSales.Internet Extended Amount" />  

              </Measures>  

            </MeasureGroup>  

          </MeasureGroups>  

          <Dimensions>  

            <Dimension CubeDimensionName="Sales Territory">  

              <Attributes>  

                <Attribute AttributeName="DimSalesTerritory.Sales Territory Region" />  

                <Attribute AttributeName="DimSalesTerritory.Sales Territory Country" />  

              </Attributes>  

              <Hierarchies>  

                <Hierarchy HierarchyName="DimSalesTerritory.Sales Territory" />  

              </Hierarchies>  

            </Dimension>  

          </Dimensions>  

        </Perspective>  

      </Perspectives>  

      <MdxScripts>  

        <MdxScript Name="MdxScript1" Default="true">  

          <MdxScriptItems>  

            <CalculatedMember Name="[Internet Gross Profit]" FormatString="Currency" ParentDimensionName="Measures" AssociatedMeasureGroupName="Fact Internet Sales 1">  

            <MdxScript>[Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]</MdxScript>  

            <NonemptyBehaviors>  

              <NonemptyBehavior MeasureName="FactInternetSales.Internet Sales Amount" />  

              <NonemptyBehavior MeasureName="FactInternetSales.Internet Total Product Cost" />  

            </NonemptyBehaviors>  

          </CalculatedMember>  

        </MdxScriptItems>  

      </MdxScript>  

    </MdxScripts>  

    <CubeMeasureGroups>  

      <CubeMeasureGroup Name="Fact Internet Sales 1" FactName="FactInternetSales">  

        <Measures>  

          <Measure MeasureName="FactInternetSales.Internet Sales Amount" />  

          <Measure MeasureName="FactInternetSales.Internet Order Quantity" />  

          <Measure MeasureName="FactInternetSales.Internet Extended Amount" />  

          <Measure MeasureName="FactInternetSales.Internet Tax Amount" />  

          <Measure MeasureName="FactInternetSales.Internet Freight Cost" />  

          <Measure MeasureName="FactInternetSales.Internet Unit Price" />  

          <Measure MeasureName="FactInternetSales.Internet Total Product Cost" />  

          <Measure MeasureName="FactInternetSales.Internet Standard Product Cost" />  

          <Measure MeasureName="FactInternetSales.Internet Transaction Count" />  

        </Measures>  

      </CubeMeasureGroup>  

    </CubeMeasureGroups>  

    <CubeDimensions>  

      <CubeDimension Name="Sales Territory" DimensionName="DimSalesTerritory">  

        <Attributes>  

          <Attribute AttributeName="DimSalesTerritory.Sales Territory Region" />  

          <Attribute AttributeName="DimSalesTerritory.Sales Territory Country" />  

          <Attribute AttributeName="DimSalesTerritory.Sales Territory Group" />  

        </Attributes>  

        <Hierarchies>  

          <Hierarchy HierarchyName="DimSalesTerritory.Sales Territory" />  

        </Hierarchies>  

      </CubeDimension>  

    </CubeDimensions>  

    <Actions>  

      <DrillThrough Name="Internet Details" Caption="Drillthrough..." Default="false" Target="Adventure Works.Fact Internet Sales 1" TargetType="Cells"/>  

      <DrillThrough Name="Finance Details" Target="Adventure Works.Fact Finance" Caption="Drillthrough..." Default="true" CaptionIsMdx="false" Invocation="Interactive" TargetType="Cells"/>  

    </Actions>  

    <Kpis>  

      <Kpi Name="Growth in Customer Base" AssociatedMeasureGroupName="Fact Internet Sales 1" StatusGraphic="RoadSigns"/>  

    </Kpis>  

  </Cube>  

</Cubes>  

</Biml>
```

--------------------------------

### Aggregate Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-aggregate

Demonstrates the use of an Aggregate transformation to calculate a distinct count of the DisplayName column from an OLE DB source.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="Package" ConstraintMode="Parallel">  

      <Tasks>  

        <Dataflow Name="Dataflow Task 1">  

          <Transformations>  

            <!-- Outputs distinct count information for the DisplayName column  

            using the Aggregate transformation. -->  

            <Aggregate Name="Aggregate Transform">  

              <InputPath OutputPathName="FedReserve.Output" />  

              <OutputPaths>  

                <OutputPath Name="Aggregate Transform Output">  

                  <Columns>  

                    <Column SourceColumn="DisplayName" Operation="CountDistinct" />  

                  </Columns>  

                </OutputPath>  

              </OutputPaths>  

            </Aggregate>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments">  

              <Columns>  

                <Column SourceColumn="DisplayName" TargetColumn="DisplayName" />  

              </Columns>  

            <DirectInput>select * from DimFrequency</DirectInput>  

          </OleDbSource>  

        </Transformations>  

      </Dataflow>  

    </Tasks>  

  </Package>  

</Packages>  

</Biml>
```

--------------------------------

### Data Conversion Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-data-conversion

Demonstrates how to use the DataConversion element to change the data type of a specific source column to StringFixedLength.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Parallel">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments" >  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

  

          <!-- Converts the datatype of the Category column to StringFixedLength -->  

            <DataConversion Name="Data Conversion Transformation">  

              <Columns>  

                <Column SourceColumn="Category" DataType="StringFixedLength" />  

              </Columns>  

            </DataConversion>  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Merge Join Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-merge-join

Configures a Merge Join transformation with an Inner Join type, specifying left and right input paths and join keys.

```Biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

            <DirectInput>select * from SurveyResponses order by response, attribute, surveyid</DirectInput>  

          </OleDbSource>  

  

          <OleDbSource Name="SportDescription" ConnectionName="SportsData">  

          <DirectInput>select * from SportDescription order by sport</DirectInput>  

        </OleDbSource>  

  

        <!-- Performs a merge join on SurveyResponses and JuniorSurveyResponses, using Response and Sport as the Left/Right join keys respectively. -->  

          <MergeJoin Name="Merge Join Transformation" JoinType="InnerJoin" TreatNullsAsEqual="false" MaxBuffersPerInput="10" >  

            <LeftInputPath OutputPathName="SurveyResponses.Output"   >  

              <Columns>  

                <Column SourceColumn="Response" SortKeyPosition="1" TargetColumn="Response" />  

                <Column SourceColumn="Attribute" SortKeyPosition="2" />  

                <Column SourceColumn="SurveyId" SortKeyPosition="3" />  

              </Columns>  

            </LeftInputPath>  

            <RightInputPath OutputPathName="SportDescription.Output" />  

            <JoinKeys>  

              <JoinKey LeftColumn="Response" RightColumn="Sport" />  

            </JoinKeys>  

          </MergeJoin>  

  

          <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" Overwrite="true" />  

        </Transformations>  

      </Dataflow>  

    </Tasks>  

  </Package>  

</Packages>  

</Biml>
```

--------------------------------

### Conditional Split Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-conditional-split

Demonstrates routing rows from an OLE DB source to a specific output path when the first three characters of the 'Category' column match 'PRE'.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Parallel">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="FedReserve" ConnectionName="FederalReserveInstruments" >  

            <DirectInput>select * from DimInstrument</DirectInput>  

          </OleDbSource>  

  

          <!-- Outputs the row to the CategoryOut output if the first three letters from the  

          Category row are "PRE" -->  

          <ConditionalSplit Name="Conditional Split Transformation">  

            <InputPath OutputPathName="FedReserve.Output" />  

            <OutputPaths>  

              <OutputPath Name="CategoryOut">  

              <Expression>SUBSTRING(Category, 1, 3) == "PRE"</Expression>  

            </OutputPath>  

          </OutputPaths>  

        </ConditionalSplit>  

      </Transformations>  

    </Dataflow>  

  </Tasks>  

</Package>  

</Packages>  

</Biml>
```

--------------------------------

### Configure WMI Event Watcher for Disk Space Monitoring

Source: https://docs.varigence.com/biml-reference/snippets/ssis-tasks-wmi-event-watcher

This example demonstrates using a WMI Event Watcher task to trigger when the free space on the C: drive drops below a specified threshold defined in an SSIS variable.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

    <Packages>  

        <Package Name="Package1" ConstraintMode="Parallel">  

            <Tasks>  

                <!-- This WMI Event Watcher task listens for when the freespace on C:\ falls beneath 100 MB. -->  

                <WmiEventWatcher Name="WMI Event Watcher Task 1" ConnectionName="WmiConnection1">  

                    <Expressions>  

                        <Expression PropertyName="WqlQuerySource">"SELECT * FROM __InstanceModificationEvent WITHIN 5 WHERE TargetInstance ISA \"Win32_LogicalDisk\" AND TargetInstance.Name=\"C:\" AND TargetInstance.FreeSpace &lt; " + (DT_WSTR, 50)@[User::FreeSpaceThreshold]</Expression>  

                    </Expressions>  

                    <DirectInput> </DirectInput>  

                </WmiEventWatcher>  

            </Tasks>  

            <Variables>  

                <Variable Name="FreeSpaceThreshold" DataType="Int64">104857600</Variable>  

            </Variables>  

        </Package>  

    </Packages>  

</Biml>
```

--------------------------------

### Fuzzy Grouping Transformation in Biml

Source: https://docs.varigence.com/biml-reference/snippets/transformations-fuzzy-grouping

This example demonstrates how to configure a Fuzzy Grouping transformation within a Biml Dataflow task to group similar values in a 'Response' column.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

            <DirectInput>select * from SurveyResponses</DirectInput>  

          </OleDbSource>  

  

          <!-- Does fuzzy grouping of the Response column.  -->  

            <FuzzyGrouping Name="Fuzzy Grouping Transformation" ConnectionName="SportsData"  Exhaustive="true" SimilarityScoreColumnName="Similarity" OutputKeyColumnName="OutputKey" InputKeyColumnName="InputKey" >  

              <Columns>  

                <Column MatchType="Fuzzy" SourceColumn="Response" GroupOutputAlias="GroupedOutput" TargetColumn="Response" SimilarityOutputAlias="SimilarityOutput" />  

              </Columns>  

            </FuzzyGrouping>  

  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" Overwrite="true" />  

  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Export Column Biml Configuration

Source: https://docs.varigence.com/biml-reference/snippets/transformations-export-column

Example of how to configure the ExportColumn transformation within a Biml Dataflow task to export data to a file.

```APIDOC
## ExportColumn Transformation

### Description
The ExportColumn transformation is used to export the contents of a specific column to a file path defined by another column in the data stream.

### Configuration
- **Name**: The name of the transformation.
- **Columns**:
  - **FilePathColumn**: The name of the column containing the destination file path.
  - **ExtractColumn**: The name of the column containing the data to be exported.
  - **AllowAppend**: Boolean indicating if the data should be appended to the file if it already exists.
- **InputPath**: The OutputPathName from the preceding transformation.

### Example
```xml
<ExportColumn Name="Export Column Transformation">
  <Columns>
    <Column FilePathColumn="FileLocation" ExtractColumn="Url" AllowAppend="true" />
  </Columns>
  <InputPath OutputPathName="Derived Columns.Output" />
</ExportColumn>
```
```

--------------------------------

### Biml Foreach File Loop Implementation

Source: https://docs.varigence.com/biml-reference/snippets/ssis-tasks-foreach-file-loop

Configures a Foreach File Loop to iterate through files in a specified directory and perform a File System move operation.

```Biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

    <Packages>  

        <Package Name="Parent" ConstraintMode="Parallel">  

            <Tasks>  

                <!-- This ForEachFileLoop task uses a variable to communicate fully qualified paths to its FileSystem task. -->  

                <ForEachFileLoop Name="Foreach File Loop 1" ConstraintMode="Parallel" Folder="C:\Orders" FileSpecification="*.*" ProcessSubfolders="true">  

                    <Tasks>  

                        <FileSystem Name="File System Task 1" Operation="MoveFile" OverwriteDestination="true">  

                            <VariableInput VariableName="User.ForeachFilePath" />  

                            <FileOutput ConnectionName="ArchiveConnection" />  

                        </FileSystem>  

                    </Tasks>  

                    <Variables>  

                        <Variable Name="ForeachFilePath" DataType="String"></Variable>  

                    </Variables>  

                    <VariableMappings>  

                        <VariableMapping Name="Mapping" VariableName="User.ForeachFilePath" />  

                    </VariableMappings>  

                </ForEachFileLoop>  

            </Tasks>  

        </Package>  

    </Packages>  

</Biml>
```

--------------------------------

### Term Extraction Transformation Example

Source: https://docs.varigence.com/biml-reference/snippets/transformations-term-extraction

Configures a Term Extraction transformation to identify terms occurring at least three times in the 'Response' column.

```Biml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Packages>  

    <Package Name="My Package" ConstraintMode="Linear">  

      <Tasks>  

        <Dataflow Name="My Dataflow Task">  

          <Transformations>  

            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">  

            <DirectInput>select * from SurveyResponses</DirectInput>  

          </OleDbSource>  

  

          <!-- Extracts terms (noun/noun phrases) from the Response column that occur at least thrice.  Outputs the terms found for each row to the new ExtractedTerms column. -->  

            <TermExtraction Name="Term Extraction Transformation" TermFrequencyThreshold="3" CaseSensitiveTermExtraction="false" TermExtractionColumn="Response" TermOutputColumn="ExtractedTerms" >  

              <InputPath OutputPathName="SurveyResponses.Output" />  

            </TermExtraction>  

  

            <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" Overwrite="true" />  

  

          </Transformations>  

        </Dataflow>  

      </Tasks>  

    </Package>  

  </Packages>  

</Biml>
```

--------------------------------

### Generate Extraction Packages for All Tables

Source: https://docs.varigence.com/biml-reference/snippets/bimlscripts-extract-all-tables

Uses a C# template to iterate over RootNode.Tables, creating a data flow package for each table and a master driver package to execute them.

```Biml
<#@ template language="C#" hostspecific="true"#>  

  <#@ import namespace="System.Data" #>  

  

    <Biml xmlns="http://schemas.varigence.com/biml.xsd">  

      <Packages>  

        <!-- Creates a new package for each table in the project -->  

          <# foreach (var table in RootNode.Tables) { #>  

            <Package Name="Extract <#=table.Name#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None">  

              <Tasks>  

                <Dataflow Name="Copy Data">  

                  <Transformations>  

                    <OleDbSource Name="Retrieve Data" ConnectionName="Source">  

                    <DirectInput>SELECT * FROM <#=table.Name#></DirectInput>  

                  </OleDbSource>  

                  <OleDbDestination Name="Insert Data" ConnectionName="Target">  

                    <ExternalTableOutput Table="<#=table.Name#>"/>  

                      </OleDbDestination>  

                      </Transformations>  

                      </Dataflow>  

                      </Tasks>  

                      </Package>  

                      <# } #>  

  

                        <!-- Creates a new package that executes each of the packages created above -->  

                          <Package Name="Driver - Extract All" ConstraintMode="Linear" AutoCreateConfigurationsType="None">  

                            <Tasks>  

                              <# foreach (var table in RootNode.Tables) { #>  

                                <ExecutePackage Name="Extract <#=table.Name#>">  

                                  <Package PackageName="Extract <#=table.Name#>" />  

                                    </ExecutePackage>  

                                    <# } #>  

                                      </Tasks>  

                                    </Package>  

                                    </Packages>  

                                    </Biml>  

                                      

  

                                  </ExecutePackage>  

                                </Tasks>  

                              </Package>  

                            </ExternalTableOutput>  

                          </OleDbDestination>  

                        </Transformations>  

                      </Dataflow>  

                    </Tasks>  

                  </Package>  

                </Packages>  

              </Biml>
```

--------------------------------

### Clone a Table in Biml

Source: https://docs.varigence.com/biml-reference/snippets/tables-clone-table

Demonstrates how to use the CloneTable element to create a new table named SampleTableClone based on SampleTable, while adding an extra column.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

  <Tables>  

    <Table Name="SampleTable" ConnectionName="TableConnection">  

      <Columns>  

        <Column Name="Simple" DataType="Int32" IsNullable="false"/>  

      </Columns>  

    </Table>  

    <!-- This CloneTable copies the SampleTable table above and includes an additional column. -->  

      <CloneTable  

      Name="SampleTableClone"  

      CloneIndexes="false"  

      CloneKeys="false"  

      CloneStaticSources="false"  

      NullClonedColumns="true"  

      TableName="SampleTable"  

      ConnectionName="TableConnection"  

      >  

      <Columns>  

        <Column Name="ExtraColumn" DataType="Int32" />  

      </Columns>  

      </CloneTable>  

    </Tables>  

  </Biml>
```

--------------------------------

### Define a Calculated Member in Biml

Source: https://docs.varigence.com/biml-reference/snippets/cubes-calculated-member

This example shows the structure for a CalculatedMember element within an MdxScript, including the MDX expression and associated non-empty behaviors.

```xml
<Biml xmlns="http://schemas.varigence.com/biml.xsd">  

    <Cubes>  

        <Cube Name="Adventure Works" ConnectionName="AdvWorksCube">  

            <MdxScripts>  

                <MdxScript Name="MdxScript1" Default="true">  

                    <MdxScriptItems>  

                        <!--  

                        This calculated member specifies a MdxScript and references a cube measure group.  

                        -->  

                        <CalculatedMember Name="[Internet Gross Profit]" FormatString="Currency" ParentDimensionName="Measures" AssociatedMeasureGroupName="Fact Internet Sales 1">  

                            <MdxScript>[Measures].[Internet Sales Amount] - [Measures].[Internet Total Product Cost]</MdxScript>  

                            <NonemptyBehaviors>  

                                <NonemptyBehavior MeasureName="FactInternetSales.Internet Sales Amount" />  

                                <NonemptyBehavior MeasureName="FactInternetSales.Internet Total Product Cost" />  

                            </NonemptyBehaviors>  

                        </CalculatedMember>  

                    </MdxScriptItems>  

                </MdxScript>  

            </MdxScripts>  

            <CubeMeasureGroups>  

                <CubeMeasureGroup Name="Fact Internet Sales 1" FactName="FactInternetSales">  

                    <Measures>  

                        <Measure MeasureName="FactInternetSales.Internet Sales Amount" />  

                        <Measure MeasureName="FactInternetSales.Internet Order Quantity" />  

                        <Measure MeasureName="FactInternetSales.Internet Extended Amount" />  

                        <Measure MeasureName="FactInternetSales.Internet Total Product Cost" />  

                    </Measures>  

                </CubeMeasureGroup>  

            </CubeMeasureGroups>  

        </Cube>  

    </Cubes>  

</Biml>
```
Tokens:
Raw