Tuesday, September 25, 2012

BIML Export to FlatFile

Demo BIML on creating an export to flat file package based on the SQL-Server metadata views:
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
<#@ import namespace="System.IO"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Export.DataBase.Schema.biml
            Demo make a export to file package
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
<#
    string CnSrcDB = "Server=.\\MSSQL2012;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11.1";
    string SrcTableCatalog = "AdventureWorks2012";
    string SrcTableSchema ="Sales";
    string TableCatalog="";
    string TableSchema ="";
    string TableName ="";
    string ColumnName ="";
    string DataType ="";
    string CharacterMaximumLength ="";
    string FlatFileFormatName ="";
    string MaxOrdinalPosition ="";
    string ColumnDelimeter =";";
   
    DataTable tFFFCol;
#>
<FileFormats>
    <# DataTable tFFF = ExternalDataAccess.GetDataTable(CnSrcDB,
    "SELECT t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME,  max(t1.ORDINAL_POSITION) as MAX_ORDINAL_POSITION FROM  INFORMATION_SCHEMA.COLUMNS AS t1 INNER JOIN INFORMATION_SCHEMA.TABLES  AS t2 ON t1.TABLE_CATALOG = t2.TABLE_CATALOG  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME  WHERE        (t1.TABLE_CATALOG = '" +SrcTableCatalog + "') AND (t2.TABLE_TYPE = 'BASE TABLE')  and t2.TABLE_SCHEMA = '" + SrcTableSchema +"' group by t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME");
    foreach (DataRow tFFFr in tFFF.Rows){
    TableCatalog =tFFFr[0].ToString();
    TableSchema = tFFFr[1].ToString();
    TableName = tFFFr[2].ToString();
    MaxOrdinalPosition = tFFFr[3].ToString();
    #>
    <FlatFileFormat FlatFileType="Delimited"     ColumnNamesInFirstDataRow="true"     RowDelimiter="CRLF"  Name="FFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>">
        <Columns>
            <# tFFFCol = ExternalDataAccess.GetDataTable(CnSrcDB,
            "SELECT t1.COLUMN_NAME, t1.DATA_TYPE, t1.CHARACTER_MAXIMUM_LENGTH, T1.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS AS t1 where t1.TABLE_CATALOG = '"+TableCatalog+"' and t1.TABLE_SCHEMA = '"+TableSchema+"' and t1.TABLE_NAME = '"+TableName+"'");
            foreach (DataRow tFFFRow in tFFFCol.Rows){
            ColumnName =tFFFRow[0].ToString();
            DataType = tFFFRow[1].ToString();
            CharacterMaximumLength = tFFFRow[2].ToString();   
                ColumnDelimeter = ";";
                if (MaxOrdinalPosition == tFFFRow[3].ToString()){ ColumnDelimeter = "CRLF"; } ;
                DataType = "String";                               
            #>
                <Column ColumnType="Delimited" DataType= "<#=DataType#>" Delimiter="<#=ColumnDelimeter#>" Length="4000" Name="<#=ColumnName#>"></Column>
            <# } #>
        </Columns>
    </FlatFileFormat>
    <#}  #>
</FileFormats>
    <Connections>
        <OleDbConnection
            Name="CnOleDBAdventureWorks2012"
            ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </OleDbConnection>
        <# DataTable tFF = ExternalDataAccess.GetDataTable(CnSrcDB,
        "SELECT t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME FROM  INFORMATION_SCHEMA.COLUMNS AS t1 INNER JOIN INFORMATION_SCHEMA.TABLES  AS t2 ON t1.TABLE_CATALOG = t2.TABLE_CATALOG  AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA AND t1.TABLE_NAME = t2.TABLE_NAME  WHERE        (t1.TABLE_CATALOG = '"
        +SrcTableCatalog+"') AND (t2.TABLE_TYPE = 'BASE TABLE') and t2.TABLE_SCHEMA = '"
        +SrcTableSchema+"' group by t1.TABLE_CATALOG, t1.TABLE_SCHEMA, t1.TABLE_NAME");
        foreach (DataRow tFFFr in tFFF.Rows){
        TableCatalog =tFFFr[0].ToString();
        TableSchema = tFFFr[1].ToString();
        TableName = tFFFr[2].ToString(); #>
            <FlatFileConnection Name="FF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                FileFormat="FFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                FilePath="C:\\<#=TableCatalog#>.<#=TableSchema#>.<#=TableName#>.dat"></FlatFileConnection>           
            <# }#>
    </Connections>
    <Packages>
        <Package Name="Extract.All" ConstraintMode="Linear" FailParentOnFailure="false">
            <Tasks>
                <#    
DataTable t1 = ExternalDataAccess.GetDataTable(CnSrcDB,
"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES AS t2 WHERE (TABLE_CATALOG = '"
+SrcTableCatalog +"') AND (TABLE_TYPE = 'BASE TABLE') and t2.TABLE_SCHEMA = '"+SrcTableSchema+"' ");
foreach (DataRow row1 in t1.Rows){
    TableCatalog =row1[0].ToString();
    TableSchema = row1[1].ToString();
    TableName = row1[2].ToString(); #>
                <Dataflow  Name="DFT <#=TableCatalog#> <#=TableSchema#> <#=TableName#>">
                    <Transformations>
                        <OleDbSource
                            Name="ODS <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                            ConnectionName="CnOleDBAdventureWorks2012">
                            <ExternalTableInput Table ="[<#=TableCatalog#>].[<#=TableSchema#>].[<#=TableName#>]">
                            </ExternalTableInput>
                        </OleDbSource>
                        <FlatFileDestination Name="DFF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"
                            ConnectionName="FF <#=TableCatalog#> <#=TableSchema#> <#=TableName#>"></FlatFileDestination>                       
                    </Transformations>
                </Dataflow>
                <# } #>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Gives you:
image
Till Next Time

Sunday, September 23, 2012

BIML Include

On of the big risks is that BIML file have a tendency to become “LARGE” and thus become unreadable. Luckily you can use the Include directive to cut you BIML into readable blocks. For me a rule of thumb is a 100 line or less. Based on the script I did here: http://biml101.blogspot.nl/2012/09/biml-add-row-number-to-file-export.html I brought the Flat File definitions and the scripcompent C# part to external txt files. This also increases the reusability of code.!
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Add.RowNumber.Include.biml
            Demo on using the Include file
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <FileFormats>
        <#@ include file="C:\BIML\FFF\FFF AdventureWorks2012 Person Address.txt" #>
    </FileFormats>
    <Connections>
        <#@ include file="C:\BIML\CN\CnOleDBAdventureWorks2012.txt" #>           
        <FlatFileConnection Name ="CnFFAdventureWorks2012PersonAddress"
                            FileFormat ="FFF AdventureWorks2012 Person Address"
                            FilePath="C:\AdventureWorks2012.Person.Address.dat"></FlatFileConnection>
    </Connections>
    <Packages>
        <Package Name="Add.RowNumber.Include" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DFT Add Rownumber">
                    <Transformations>
                        <OleDbSource Name ="ODS AdventureWorks2012 Person Address"
                                     ConnectionName ="CnOleDBAdventureWorks2012">
                            <DirectInput>
                                SELECT [AddressID]
                                ,[AddressLine1]
                                ,[AddressLine2]
                                ,[City]
                                ,[StateProvinceID]
                                ,[PostalCode]
                                ,[rowguid]
                                ,[ModifiedDate]
                                FROM [AdventureWorks2012].[Person].[Address]
                            </DirectInput>                       
                        </OleDbSource>
                        <DerivedColumns Name="DC ADD RowNumber Column">
                            <Columns>
                                <Column Name="RowNumber" DataType ="Int64">0</Column>
                            </Columns>
                        </DerivedColumns>
                        <ScriptComponentTransformation Name ="SC Add RowNumber">
                            <ScriptComponentProject>
                                <#@ include file="C:\BIML\SC\SCP AddRowNumber.txt" #>
                            </ScriptComponentProject>
                        </ScriptComponentTransformation>
                       
                        <FlatFileDestination Name ="FFD AdventureWorks2012 Person Address"
                                             ConnectionName ="CnFFAdventureWorks2012PersonAddress"
                                             Overwrite ="true">
                       
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Till Next Time

BIML Add Row number to File Export

I like to add a row number to the flat file exports. This helps a lot in the communication with business when a problem arrives. This is an example on how to add a row number:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Annotations>
        <Annotation>
            File: Add.RowNumber.biml
            Demo biml adding a rownumber to FileExport
            Enviroment:
            DB: MS-SQL2012
            BIML: 1.6 VS2010 BIDS Helper
            (c) John Minkjan biml101.blogspot.com
        </Annotation>
    </Annotations>
    <FileFormats>
        <FlatFileFormat
            Name ="FFF AdventureWorks2012 Person Address"
            ColumnNamesInFirstDataRow="true"
            FlatFileType="Delimited"
            HeaderRowDelimiter=";"
            RowDelimiter="CRLF"
            TextQualifer="|">
            <Columns>
                <Column Name="RowNumber" DataType ="String" Length="38" Delimiter =";"></Column>
                <Column Name="AddressID" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="AddressLine1" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="AddressLine2" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="City" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="StateProvinceID" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="PostalCode" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="rowguid" DataType ="String" Length="128" Delimiter =";"></Column>
                <Column Name="ModifiedDate" DataType ="String" Length="128" Delimiter ="CRLF"></Column>
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Connections>
        <OleDbConnection
            Name="CnOleDBAdventureWorks2012"
            ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
            RetainSameConnection="true">
        </OleDbConnection>
        <FlatFileConnection Name ="FF AdventureWorks2012 Person Address"
                            FileFormat ="FFF AdventureWorks2012 Person Address"
                            FilePath="C:\AdventureWorks2012.Person.Address.dat"></FlatFileConnection>
    </Connections>
    <Packages>
        <Package Name="Add.RowNumber" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DFT Add Rownumber">
                    <Transformations>
                        <OleDbSource Name ="ODS AdventureWorks2012 Person Address"
                                     ConnectionName ="CnOleDBAdventureWorks2012">
                            <DirectInput>
                                SELECT [AddressID]
                                ,[AddressLine1]
                                ,[AddressLine2]
                                ,[City]
                                ,[StateProvinceID]
                                ,[PostalCode]
                                ,[rowguid]
                                ,[ModifiedDate]
                                FROM [AdventureWorks2012].[Person].[Address]
                            </DirectInput>                       
                        </OleDbSource>
                        <DerivedColumns Name="DC ADD RowNumber Column">
                            <Columns>
                                <Column Name="RowNumber" DataType ="Int64">0</Column>
                            </Columns>
                        </DerivedColumns>
                        <ScriptComponentTransformation Name ="SC Add RowNumber">
                            <ScriptComponentProject>
                                <ScriptComponentProject Name="SC_AddRowNumber">
                                    <AssemblyReferences>
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
                                        <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
                                        <AssemblyReference AssemblyPath="System.dll" />
                                        <AssemblyReference AssemblyPath="System.AddIn.dll" />
                                        <AssemblyReference AssemblyPath="System.Data.dll" />
                                        <AssemblyReference AssemblyPath="System.Xml.dll" />
                                    </AssemblyReferences>
                                    <InputBuffer Name="Input0">
                                        <Columns>
                                            <Column Name="RowNumber" DataType ="Int64" UsageType ="ReadWrite"></Column>
                                        </Columns>
                                    </InputBuffer>
                                    <OutputBuffers>
                                        <OutputBuffer Name="Output0"></OutputBuffer>
                                    </OutputBuffers>
                                    <Files>
                                        <File Path ="AssemblyInfo.cs">
                                            using System.Reflection;
                                            using System.Runtime.CompilerServices;
                                            [assembly: AssemblyTitle("SC_AddRowNumber")]
                                            [assembly: AssemblyDescription("")]
                                            [assembly: AssemblyConfiguration("")]
                                            [assembly: AssemblyCompany("")]
                                            [assembly: AssemblyProduct("SC_AddRowNumber")]
                                            [assembly: AssemblyCopyright("Copyright @  2012")]
                                            [assembly: AssemblyTrademark("")]
                                            [assembly: AssemblyCulture("")]
                                            [assembly: AssemblyVersion("1.0.*")]
                                        </File>
                                        <File Path ="main.cs">
                                            using System;
                                            using System.Data;
                                            using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
                                            using Microsoft.SqlServer.Dts.Runtime.Wrapper;
                                            [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
                                            public class ScriptMain : UserComponent
                                            {
                                                Int64 lRownumber = 0;
                                                public override void PreExecute()
                                                {
                                                base.PreExecute();
                                                }
                                                public override void PostExecute()
                                                {
                                                base.PostExecute();
                                                }
                                                public override void Input0_ProcessInputRow(Input0Buffer Row)
                                                {
                                                lRownumber = lRownumber + 1;
                                                Row.RowNumber =   lRownumber ;
                                                }
                                            }
                                        </File>
                                    </Files>
                                </ScriptComponentProject>
                            </ScriptComponentProject>
                           
                        </ScriptComponentTransformation>
                       
                        <FlatFileDestination Name ="FFD AdventureWorks2012 Person Address"
                                             ConnectionName ="FF AdventureWorks2012 Person Address"
                                             Overwrite ="true">
                       
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
This will get you:
image

Till Next Time

Monday, September 17, 2012

BIML MD5 ScriptComponent Hash

Demo biml using the ScriptComponent to create an MD5 hash, very handy if you are not allowed to use open source altenatives like ==> http://ssismhash.codeplex.com/
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
<#@ import namespace="System.IO"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
        <Annotation>
            File: MD5.ScriptComponent.biml
            Demo biml using the ScriptComponent to
            create an MD5 hash.
            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>
   
<ScriptProjects>                       
    <ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SC Add MD5">
        <AssemblyReferences>
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
            <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
            <AssemblyReference AssemblyPath="System.dll" />
            <AssemblyReference AssemblyPath="System.AddIn.dll" />
            <AssemblyReference AssemblyPath="System.Data.dll" />
            <AssemblyReference AssemblyPath="System.Xml.dll" />
        </AssemblyReferences>
        <Files>
            <File Path="AssemblyInfo.cs">
            using System.Reflection;
            using System.Runtime.CompilerServices;
            [assembly: AssemblyTitle("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
            [assembly: AssemblyDescription("")]
            [assembly: AssemblyConfiguration("")]
            [assembly: AssemblyCompany("Ciber Nederland")]
            [assembly: AssemblyProduct("SC_c253bef215bf4d6b85dbe3919c35c167.csproj")]
            [assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
            [assembly: AssemblyTrademark("")]
            [assembly: AssemblyCulture("")]
            [assembly: AssemblyVersion("1.0.*")]
            </File>
            <File Path="ScriptMain.cs">
            using System;
            using System.Data;
            using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
            using Microsoft.SqlServer.Dts.Runtime.Wrapper;
            using System.Security.Cryptography;
            using System.Text;
            using System.IO;
            [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
            public class ScriptMain : UserComponent
            {
            static public string GetMd5Sum(string str)
            {
            Encoder enc = System.Text.Encoding.Unicode.GetEncoder();
            byte[] unicodeText = new byte[str.Length * 2];
            enc.GetBytes(str.ToCharArray(), 0, str.Length, unicodeText, 0, true);
            MD5 md5 = new MD5CryptoServiceProvider();
            byte[] result = md5.ComputeHash(unicodeText);
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i != result.Length; i++)
            {
            sb.Append(result[i].ToString("X2"));
            }
            return sb.ToString();
            }
            public override void PreExecute()
            {
            base.PreExecute();
            }
            public override void PostExecute()
            {
            base.PostExecute();
            }
            public override void Input0_ProcessInputRow(Input0Buffer Row)
            {
            Row.MD5AllColumns = GetMd5Sum(Row.FirstName + Row.LastName);
            }
            }
            </File>
        </Files>
<InputBuffer Name="Input0">
    <Columns>
        <Column Name="MD5AllColumns" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
        <Column Name="FirstName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
        <Column Name="LastName" DataType="AnsiString" Length="50" UsageType="ReadWrite"></Column>
    </Columns>
</InputBuffer>
    <OutputBuffers>
        <OutputBuffer Name="Output0">
    </OutputBuffer>
</OutputBuffers>
</ScriptComponentProject>
</ScriptProjects>
    <Packages>
        <Package  ConstraintMode="Parallel" Name="MD5.ScriptComponent">
        <Variables>
                <Variable Name ="RowCount"
                          DataType="Int32"
                          EvaluateAsExpression="false">0</Variable>
                <!--Stock symbol has to be enclosed in double quotes-->
            </Variables>
            <Tasks>
                <Container ConstraintMode ="Parallel" Name="SEQ Main">
                    <Tasks>
                        <Dataflow Name="DFT Main">
                            <Transformations>
                                <OleDbSource Name="ODS Adventureworks Person" ConnectionName="CnOleDBAdventureWorks2012">
                                    <ExternalTableInput Table = "[Person].[Person]"></ExternalTableInput>
                                </OleDbSource>
                                <DerivedColumns Name="DC MD5">
                                <InputPath OutputPathName="ODS Adventureworks Person.Output"></InputPath>
                                <Columns>
                                    <Column Name="MD5AllColumns" DataType="AnsiString" Length="50">"--"</Column>                                   
                                </Columns>
                            </DerivedColumns>
                                <ScriptComponentTransformation Name="SC Add MD5">
                                <InputPath OutputPathName="DC MD5.Output"></InputPath>
                                <ScriptComponentProjectReference ScriptComponentProjectName="SC Add MD5" />                               
                            </ScriptComponentTransformation>
                            <!-- Dummy Rowcount to show results in dataviewer -->
                            <RowCount VariableName="User.RowCount" Name="RC Input">
                                <InputPath OutputPathName="SC Add MD5.Output0"></InputPath>
                            </RowCount>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Will get you:
image
Till Next Time

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