Sunday, September 23, 2012

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

2 comments:

  1. How can we get the column name in Input0Buffer dynamically without mentioning hard code

    ReplyDelete
  2. Hi ,

    I am working on BIML Script component, where I am taking data from OLEDB Source.

    In Script Component I want all the input columns to be checked by default as input.
    I have no idea how to proceed for the same.

    Below is my code :-









    -->
















    using System.Reflection;
    using System.Runtime.CompilerServices;
    [assembly: AssemblyTitle("SC_Example.csproj")]
    [assembly: AssemblyDescription("")]
    [assembly: AssemblyConfiguration("")]
    [assembly: AssemblyCompany("Ciber Nederland")]
    [assembly: AssemblyProduct("SC_Example.csproj")]
    [assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
    [assembly: AssemblyTrademark("")]
    [assembly: AssemblyCulture("")]
    [assembly: AssemblyVersion("1.0.*")]


    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.Windows.Forms;
    using System.IO;
    using System.Reflection;
    [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
    public class ScriptMain : UserComponent
    {
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
    string strColumnsValue = string.Empty;
    Type rowType = Row.GetType();
    PropertyInfo columnProperty;
    MD5 md5 = new MD5CryptoServiceProvider();
    Encoder enc = System.Text.Encoding.Unicode.GetEncoder();

    foreach(IDTSInputColumn100 Rw in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
    {
    columnProperty = rowType.GetProperty(Rw.Name);
    strColumnsValue += Convert.ToString(columnProperty.GetValue(Row,null));
    }
    byte[] bbb = new byte[strColumnsValue.Length * 3];
    bbb = UnicodeEncoding.Unicode.GetBytes(strColumnsValue);
    byte[] hash = md5.ComputeHash(bbb);
    String strHash = Convert.ToBase64String(hash);
    Row.RowChecksum = strHash;
    }
    }





























    ReplyDelete