Creating a FuzzyLookup object using BIML with BIDs Helper

Question

I'm trying to create a FuzzyLookup object using BIML with BIDs and Visual Studio 2008.

The following code erros and does not compile with the error "Could not resolve reference to dbo.JuniorSurveyResponses". The object dbo.JuniorSurveyResponses exists and i have the correct permissions to it.

If i remove the FuzzyLookup create the remainder of the code compiles without error. This code is actually from https://www.varigence.com/Documentation/Samples/Biml/Fuzzy+Lookup.

Any ideas about the error?

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
        <Connections>
         <OleDbConnection Name="SportsData" ConnectionString="Provider=SQLNCLI10;Server=myServer;Initial Catalog=myCatalog;Integrated Security=SSPI;" DelayValidation="true" />
        </Connections>
        <Packages>
            <Package Name="My Package" ConstraintMode="Linear">
                <Tasks>    
                    <Dataflow Name="My Dataflow Task">
                        <Transformations>
                            <OleDbSource Name="SurveyResponses" ConnectionName="SportsData">
                                <DirectInput>select * from SurveyResponses</DirectInput>
                            </OleDbSource>
                            <!-- Performs a fuzzy lookup on the Attribute column against the JuniorSurveyResponse DB, and outputs the corresponding Response column to NewResponse. -->
                            <FuzzyLookup Name="Fuzzy Lookup Transformation" ConnectionName="SportsData" Exhaustive="true" >
                                <ReferenceTableInput TableName="dbo.JuniorSurveyResponses" />
                                <Inputs>
                                    <Column SourceColumn="Attribute" TargetColumn="Attribute"  />
                                </Inputs>
                                <Outputs>
                                    <Column SourceColumn="Response" TargetColumn="NewReponse"  />
                                </Outputs>
                                <InputPath OutputPathName="SurveyResponses.Output" />
                            </FuzzyLookup>
                           <FlatFileDestination Name="OutputFile" ConnectionName="FlatFileConnection" Overwrite="true" />
                        </Transformations>
                    </Dataflow>
                </Tasks>
            </Package>
        </Packages>
        </Biml>

Are you sure that the object name in your database doesn't have a space or something in it? — Ben Thul
I can confirm that I get the same error recreating their approach. Will update if I find a solution — billinkc

billinkc billinkc · Accepted Answer · 2014-01-31T18:05:08

I think the problem with the referenced example from varigence.com is that it is using a ReferenceTableInput. I believe this means it expects a <Tables> collection defined within your project and all of that good stuff.

Instead, I think you're looking for a ExternalReferenceTableInput with syntax like

<ExternalReferenceTableInput Table="dbo.JuniorSurveyResponses" />

Using that as my source, I get the following fuzzy lookup created.

enter image description here

If that's not how the Fuzzy Lookup should look, let me know. I never use the transformation despite my heavy SSIS usage.

For those following along at home, I created two tables in my source system as

CREATE TABLE dbo.JuniorSurveyResponses
(
    Attribute varchar(50)
,   Response varchar(50)
);

CREATE TABLE dbo.SurveyResponses
(
    Attribute varchar(50)
,   Response varchar(50)
);

Creating a FuzzyLookup object using BIML with BIDs Helper

1 Answers