Creating a lookup SSIS with BIML script

Question

I am creating a lookup in BIMLscript to create an ssis package with a lookup. I am essentially just trying to demo the functionality of the lookup, so I am looking the table up against itself. When i create the package it seems to build fine. However when I open it in VS2013, i get a multitude of errors like the following:

Error 1 Validation error. char1: char1: The LKP_char1.Inputs[Extract_Output_LKP_char1].Columns[chr_1] has an invalid error or truncation row disposition. char.dtsx 0 0

char1 is the name of the table, LKP_char1 is the name of the lookup, and chr_1 is the name of the first column. Does anybody have any ideas of how to try and debug this.

I have my NoMatchBehavior attribute of my LookUp tag set to "IgnoreFailure"

<Dataflow Name="<#=table.SsisSafeName#>">
    <Transformations>
     <OleDbSource Name="Extract" ConnectionName="Source">
        <DirectInput>SELECT * FROM <#=table.SchemaQualifiedName#>
        </DirectInput>
    </OleDbSource>
    <Lookup Name="LKP_<#=table.Name#>" 
        NoMatchBehavior="IgnoreFailure" 
        OleDbConnectionName="Source">
            <ExternalTableInput Table="<#=table.SchemaQualifiedName#>">
            </ExternalTableInput>
            <Inputs>
            <# foreach (var column in table.Columns) { #>
                <Column SourceColumn="<#=column.Name#>" TargetColumn="<#=column.Name#>" />
            <# } #>
            </Inputs>
        <Outputs>
            <# foreach (var column in table.Columns) { #>
            <Column SourceColumn="<#=column.Name#>" TargetColumn="<#=column.Name#>" />
            <# } #>
        </Outputs>
    </Lookup>
    <# } #>
        <OleDbDestination Name="Load" ConnectionName="Source" UseFastLoadIfAvailable="false">
            <InputPath OutputPathName="LKP_<#=table.Name#>.Match" />
            <ExternalTableOutput Table="<#=table.SchemaQualifiedName#>" />
        </OleDbDestination>
    </Transformations>
</Dataflow>

Looking at your code, you have a table that you are using to drive the creation of your package biml. That table is the source of the data flow. You then have a lookup against the same table and compare all the same columns for a match (Inputs) and then double up all the existing columns into the DFT. Logically, that seems... off — billinkc
yea i know, but in theory shouldnt that mean that they are all matches and the package would behave like a normal data flow? Instead the lookup is triggering errors. — Jacob Alley
logically is my thinking correct at least? i appreciate all your help btw — Jacob Alley

billinkc billinkc · Accepted Answer · 2016-08-20T02:04:04

You didn't provide your biml so it's hard to diagnose the problem but here's a working example with two lookups

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection Name="AdventureWorks" ConnectionString="Provider=SQLOLEDB;Data Source=localhost\dev2014;Integrated Security=SSPI;Initial Catalog=AdventureWorks2014" />
    </Connections>
    <Packages>
        <Package Name="LookupDemo" ConstraintMode="Linear">
            <Tasks>
                <Dataflow Name="DFT Sample">
                    <Transformations>
                        <OleDbSource Name="OLE_SRC AW" ConnectionName="AdventureWorks" >
                            <DirectInput>SELECT * FROM (VALUES (N'TRL', 'Turkish Lira'), ('LEI', 'Romania'))D(Code, CurrencyName) cross apply
                                (VALUES (N'XRQ - TRUCK GROUND'),('ZY - EXPRESS')) D1(ShipMethod);</DirectInput>
                        </OleDbSource>

                        <Lookup Name="LKP Purchasing_ShipMethod" NoMatchBehavior="IgnoreFailure" OleDbConnectionName="AdventureWorks">
                          <DirectInput>SELECT
                            SM.ShipMethodID
                        ,   SM.Name
                        ,   SM.ShipBase
                        ,   SM.ShipRate
                        FROM
                            Purchasing.ShipMethod AS SM</DirectInput>
                          <Inputs>
                            <Column SourceColumn="ShipMethod" TargetColumn="Name" />
                          </Inputs>
                          <Outputs>
                            <Column SourceColumn="" TargetColumn="" />
                          </Outputs>
                        </Lookup>
                        <Lookup Name="LKP Sales_Currency" NoMatchBehavior="IgnoreFailure" OleDbConnectionName="AdventureWorks">
                          <DirectInput>SELECT
                            SC.CurrencyCode
                        ,   SC.Name
                        FROM
                            Sales.Currency AS SC;</DirectInput>
                          <Inputs>
                            <Column SourceColumn="Code" TargetColumn="CurrencyCode" />
                          </Inputs>
                          <Outputs>
                            <Column SourceColumn="CurrencyCode" TargetColumn="lkp_CurrencyCode" />
                            <Column SourceColumn="Name" TargetColumn="lkp_Name" />
                          </Outputs>
                        </Lookup>
                        <DerivedColumns Name="DER Placeholder for data viewer" />                       
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Creating a lookup SSIS with BIML script

1 Answers