BIDSHelper Bug: NoMatch Issue With Visual Studio 2013

BIDS Helper is a fantastic tool, but it’s not perfect.  If you’re using Visual Studio 2013, there is a bug in BIDS Helper that would prevent you from implementing lookups with NoMatch criteria like I used in BIML Part 6.

Here’s a quick-and-easy BIML package which fails:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <Connection Name="Instance" ConnectionString="Data Source=LOCALHOST;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
    </Connections>
    <Packages>
        <Package Name="NoMatchError" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
            <Connections>
                <Connection ConnectionName="Instance"></Connection>
            </Connections>
            <Tasks>
                <Container Name="Test NoMatch Error" ConstraintMode="Parallel">
                    <Tasks>
                        <Dataflow Name="NoMatch DataFlow">
                            <Transformations>
                                <OleDbSource Name="Source" ConnectionName="Instance">
                                    <DirectInput>
                                        SELECT name FROM master.sys.tables;
                                    </DirectInput>
                                </OleDbSource>
                                <Lookup Name="Lookup" OleDbConnectionName="Instance" CacheMode="Full" NoMatchBehavior="RedirectRowsToNoMatchOutput">
                                    <DirectInput>
                                        SELECT name FROM master.sys.tables;
                                    </DirectInput>
                                    <Inputs>
                                        <Column SourceColumn="name" TargetColumn="name"></Column>
                                    </Inputs>
                                    <Outputs></Outputs>
                                </Lookup>
                                <OleDbDestination Name="Destination" ConnectionName="Instance">
                                    <InputPath OutputPathName="Lookup.NoMatch"></InputPath>
                                    <ExternalTableOutput Table="sys.tables"></ExternalTableOutput>
                                </OleDbDestination>
                            </Transformations>
                        </Dataflow>
                    </Tasks>
                </Container>
            </Tasks>
        </Package>
    </Packages>
</Biml>

The error message that you get from this is Destination Input references output NoMatchError.Test NoMatch Error.NoMatch DataFlow.Lookup.NoMatch, which is not found in the existing outputs in the data flow.

To try to get around this, you might set to read . This lets you generate the BIML package, but then you get an error in the SSIS package itself which reads: “Error at NoMatch DataFlow [Lookup [19]]: The Lookup.Outputs[Lookup Match Output] has an invalid error or truncation row disposition.”

On the Advanced Editor, the Component Properties tab shows that NoMatch behavior is wrong: it reads “Treat rows with no matching entries as errors” rather than “Send rows with no matching entries to the no match output.” This is a sign that BIDS Helper is either not setting no-match behavior or is setting it to error behavior.

Incidentally, changing Lookup.NoMatch to Lookup.Error on line 39 and NoMatchBehavior="RedirectRowsToErrorOutput" on line 20 generates a package which redirects failed lookups to the Error output and successfully hooks that Error output to the destination.

Please vote on the issue and help stamp out this bug. If you need to do use this pattern in the meantime, I’ve confirmed that it works with Visual Studio 2012, and you can also use Mist if you’ve purchased a copy.

Advertisement