Monday, September 17, 2012

BIML WebService get a stock quote

How to execute a webservice to get a stock quote using BIML:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <HttpConnection Name="HTTPConnection"
                        ServerUrl="HTTP://www.webservicex.net/stockquote.asmx?wsdl"></HttpConnection>
        <FileConnection Name="FCOutputXmL"
                        FilePath="c:\\Output.XML"
                        FileUsageType="CreateFile"></FileConnection>
    </Connections>
    <Annotations>
        <Annotation>
            File: WebServiceTask.GetXML.biml
            Demo biml using the Web Service Task to
            get a stock quote
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <Packages>       
        <Package Name="WebServiceTask.GetXML" ConstraintMode="Linear">
            <Variables>
                <Variable Name ="StockSymbol"
                          DataType="String"
                          EvaluateAsExpression="false">"CBR"</Variable>
                <!--Stock symbol has to be enclosed in double quotes-->
            </Variables>
            <Tasks>
                <WebService Name="WST Get Stock Quote"
                            ServiceName="StockQuote"
                            OverwriteWsdlFile ="true"
                            ConnectionName="HTTPConnection" 
                            WebMethod="GetQuote">
                    <ExternalWsdlFile ExternalFilePath="c:\\qoute.wsdl"
                                      FileUsageType="ExistingFile" ></ExternalWsdlFile>
                    <FileOutput ConnectionName="FCOutputXmL"></FileOutput>
                    <Parameters>
                        <Parameter  Name="symbol"
                                    DataType="String"
                                    ParameterType="Variable"
                                    VariableName="User.StockSymbol"></Parameter>
                    </Parameters>
                </WebService>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Gives you:
image
Till Next Time

Sunday, September 16, 2012

BIML WmiEventWatcher wait for file to arrive

How to execute a sequence container after a file has arrived, using BIML
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <WmiConnection
            Name="WmiConnection"
            ServerName="\\localhost"
            UseWindowsAuthentication="true"
            Namespace="\root\cimv2" />
    </Connections>
    <Annotations>
        <Annotation>
            File: WmiEventWatcher.WaitForFile.biml
            Demo biml using the WmiEventWatcher to
            wait for a file to arrive before execution
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <Packages>       
        <Package Name="WmiEventWatcher.WaitForFile" ConstraintMode="Linear">
            <Variables>
                <Variable
                    Name="DirectoryToWatch"
                    DataType ="String">C:\\\\FileRecieve</Variable>
                <!--Note the really should be 4 backslashes -->
            </Variables>
            <Tasks>
                <FileSystem
                    Name="FT Create Watch Dir"
                    Operation="CreateDirectory"
                    OverwriteDestination="true">
                        <VariableInput VariableName ="User.DirectoryToWatch"></VariableInput>
                </FileSystem>
                <WmiEventWatcher
                    Name="WMI CheckForNewFile"
                    NumberOfEvents="1"
                    ConnectionName="WmiConnection">
                    <DirectInput>
                        SELECT * FROM __InstanceCreationEvent WITHIN 10
                        WHERE TargetInstance ISA "CIM_DirectoryContainsFile"
                        and TargetInstance.GroupComponent= "Win32_Directory.Name=\"C:\\\\test1\""                                           
                    </DirectInput>
                    <Expressions>
                        <Expression
                            PropertyName="WqlQuerySource">"SELECT * FROM __InstanceCreationEvent
                            WITHIN 10 WHERE TargetInstance ISA \"CIM_DirectoryContainsFile\"
                            and TargetInstance.GroupComponent= \"Win32_Directory.Name=\\\""
                            + @[User::DirectoryToWatch] + "\\\"\""
                        </Expression>
                    </Expressions>
                </WmiEventWatcher>
                <Container
                    Name="SEQ Stuff to do when file has arived"
                    ConstraintMode="Linear">                   
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Gives you:
image
Till Next Time

BIML Precedence Constraints “Parallel”


If the Constraint Mode of an element is set to Parallel all elements need to be connected using the PrecedenceConstraints Tag:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: PrecedenceConstraints.Parallel.biml
            Demo biml file on PrecedenceConstraints
            Type: Parellel
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <Packages>
        <Package
            Name="PrecedenceConstraints.Parallel"
            ConstraintMode="Parallel">
            <!--
                If the Constraint Mode of an element is set to
                Parallel all elements need to be connected using
                the PrecedenceConstraints Tag -->
            <Tasks>               
                <Container Name ="SEQ 1" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ 2" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ 3" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ 4" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ 5" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ 6" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ Last" ConstraintMode="Linear">
                    <PrecedenceConstraints  LogicalType="And">
                        <Inputs>
                            <Input
                                EvaluationOperation="Constraint"
                                OutputPathName="SEQ 1.Output"
                                EvaluationValue="Success">                               
                            </Input>
                            <Input
                                EvaluationOperation="Constraint"
                                OutputPathName="SEQ 2.Output"
                                EvaluationValue="Failure">                               
                            </Input>
                            <Input
                                EvaluationOperation="Constraint"
                                OutputPathName="SEQ 3.Output"
                                EvaluationValue="Completion">                               
                            </Input>
                            <Input
                                EvaluationOperation="Expression"
                                OutputPathName="SEQ 4.Output"
                                Expression= "@[System::MachineName]==&quot;Laptop&quot;"
                                >
                            </Input>
                            <Input
                                EvaluationOperation="ExpressionAndConstraint"
                                OutputPathName="SEQ 5.Output"
                                EvaluationValue="Failure"
                                Expression= "@[System::MachineName]==&quot;Laptop&quot;"                               
                                >
                            </Input>
                            <Input
                                EvaluationOperation="ExpressionOrConstraint"
                                OutputPathName="SEQ 6.Output"
                                EvaluationValue="Success"
                                Expression ="@[System::MachineName]==&quot;Laptop&quot;"
                                >
                            </Input>
                        </Inputs>
                       
                    </PrecedenceConstraints>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Will Get you:
image
Till Next Time

BIML Precedence Constraints “Linear”


If the Constraint Mode of an element is set to Linear all element are conected to thier precedence with the connection type "SuccesFull"
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: PrecedenceConstraints.Linear.biml
            Demo biml file on PrecedenceConstraints
            Type: Linear
            Enviroment:
                DB: MS-SQL2012
                BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <Packages>
        <Package
            Name="PrecedenceConstraints.Linear"
            ConstraintMode="Linear">
            <!--
                If the Constraint Mode of an element is set to
                Linear all element are conected to thier precedence
                with the connection type "SuccesFull"-->
            <Tasks>               
                <Container Name ="SEQ 1" ConstraintMode="Linear"></Container>
                <Container Name ="SEQ 2" ConstraintMode="Linear"></Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

gives you:
image
Till Next Time

Saturday, September 15, 2012

BIML Schema’s and Tables

If you find a BIML on Tables sample on the internet looking like this one: http://agilebi.com/jwelch/2011/05/26/creating-tables-using-biml-and-bimlscript/ you might run into trouble getting it to work in BIDS 1.6.1.
This is caused because the BIML xsd has undergone some changes. At this moment (September 2012) the good people of Varigence haven’t had the time to update the documentation on their site.
With the help of Scott Currie  of Varigence I got the sample to work.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Tables.biml
            Demo biml file on how to create tables
                using BIML from the 1.6.1 BIDS Helper
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <Connections>
            <OleDbConnection
                Name="CnOleDBAdventureWorks2012"
                ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"/>
    </Connections>
    <Databases>
        <Database Name="AdventureWorks2012"
                  ConnectionName="CnOleDBAdventureWorks2012" />
    </Databases>
    <Schemas>
        <Schema Name ="Test" DatabaseName="AdventureWorks2012"
                Owner="dbo"></Schema>
    </Schemas>
    <Tables>
        <Table Name="DimAccount_Test" SchemaName="AdventureWorks2012.Test"
               CompressionType="Page" >
            <!--Attn:    Schema requires DatabaseName.SchemaName
                        If you haven't got a schema defined use
                        DatabaseName.[default]-->
            <Columns>
                <Column Name="AccountKey" />
                <Column Name="ParentAccountKey"
                        IsNullable="true" />
                <Column Name="AccountCodeAlternateKey"
                        IsNullable="true" />
                <Column Name="ParentAccountCodeAlternateKey"
                        IsNullable="true" />
                <!--Attn:    If you don't define a DataType the column will
                            be defined as int  -->
               
                <Column Name="AccountDescription"
                        DataType="String" Length="50"
                        IsNullable="true" />
                <Column Name="AccountType"
                        DataType="String" Length="50" IsNullable="true" />
                <Column Name="Operator"
                        DataType="String" Length="50" IsNullable="true" />
                <Column Name="CustomMembers"
                        DataType="String" Length="300" IsNullable="true" />
                <Column Name="ValueType"
                        DataType="String" Length="50" IsNullable="true" />
                <Column Name="CustomMemberOptions"
                        DataType="String" Length="200" IsNullable="true" />
            </Columns>
        </Table>
    </Tables>
</Biml>
The package create block looks like this:
<#@ template language="C#" hostspecific="True" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Create Tables" AutoCreateConfigurationsType="None" ConstraintMode="Linear">
            <Tasks>
                <# foreach(var table in RootNode.Tables) {#>
                <ExecuteSQL Name="Create <#=table.Name#>" ConnectionName="<#=table.Connection.Name#>">
                    <DirectInput>
                        <#=table.GetTableSql()#>
                    </DirectInput>
                </ExecuteSQL>
                <# } #>                   
            </Tasks>
        </Package>
    </Packages>
</Biml>
Till Next Time

Friday, September 14, 2012

BIML Shrink Database

Example file for creating a shrink database package:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection
            Name ="CnAdoAdventureWorks2012"
            Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
            ConnectionString ="Data Source=.\MSSQL2012;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </AdoNetConnection>
        <OleDbConnection
            Name="CnOleDBAdventureWorks2012"
            ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </OleDbConnection>
    </Connections>
    <Packages>
        <Package Name ="Maintenance.ShrinkDatabase"
                 ConstraintMode="Linear"
                >
            <Annotations>
                <Annotation>
                    File: Maintenance.ShrinkDatabase.biml
                    Demo Creating a shrink database package
                    Enviroment:
                    DB: MS-SQL2012
                    BIML: 1.6 VS2010 BIDS Helper
                    (c) John Minkjan biml101.blogspot.com
                </Annotation>
            </Annotations>
            <Tasks>
                <ShrinkDatabase Name ="SDB AdventureWorks 2012"
                                ConnectionName="CnAdoAdventureWorks2012"
                                DatabaseSelectionMode="Specific">
                    <Databases>
                        <Database>AdventureWorks2012</Database>
                    </Databases>
                </ShrinkDatabase>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Till Next Time

BIML WMI Reader

Example file for creating a WMI Reader Package:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <AdoNetConnection
            Name ="CnAdoAdventureWorks2012"
            Provider="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
            ConnectionString ="Data Source=.\MSSQL2012;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </AdoNetConnection>
        <OleDbConnection
            Name="CnOleDBAdventureWorks2012"
            ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </OleDbConnection>
        <WmiConnection
            Name="WmiConnection"
            ServerName="\\localhost"
            UseWindowsAuthentication="true"
            Namespace="\root\cimv2" />
        <FileConnection
            Name="MyWMILoging"
            FilePath ="C:\WMILog.csv"
            FileUsageType="CreateFile">
        </FileConnection>
    </Connections>
    <Packages>
        <Package Name ="Config.WMIReader"
                 ConstraintMode="Linear"
                >
            <Annotations>
                <Annotation>
                    File: Config.WMIReader.biml
                    Demo biml for setting a WMI export to CSV
                    Enviroment:
                    DB: MS-SQL2012
                    BIML: 1.6 VS2010 BIDS Helper
                    (c) John Minkjan biml101.blogspot.com
                </Annotation>
            </Annotations>
            <Tasks>
                <WmiDataReader    Name="WMIReader" ConnectionName ="WmiConnection" >
                    <DirectInput>select * from WIN32_Process</DirectInput>
                    <FileOutput ConnectionName ="MyWMILoging"></FileOutput>
                </WmiDataReader>               
            </Tasks>
        </Package>
    </Packages>
</Biml>
Till Next Time