Simply tick a checkbox in a Fuzzy Lookup Transformation object using SSIS BIML

Question

How do i tick a checkbox in a Fuzzy Lookup Transformation (FLT) object in SQL Server 2008 using BIDs and BIML. I'm assuming it adds an output column to an output path, i don't know? I would like my output to be

Lookup column = Attribute
Lookup column = AddThisColumn

Output Alias = Attribute2
Output Alias = AddThisColumn

Below is the BIML script along with 2x screen shots, 1)checkbox AddThisColumn unticked (current status) 2)checkbox AddThisColumn ticked (what i would like)

            <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" 
                                                 MatchIndexName="dbo.JuniorSurveyResponsesIndex" DropExistingIndex="false" 
                                                 CopyReferenceTable="true" WarmCaches="false" MatchIndexOptions="ReuseExistingIndex" ValidateExternalMetadata="false" > 
                                        <ExternalReferenceTableInput Table="dbo.JuniorSurveyResponses" />
                                        <Inputs> 
                                            <Column SourceColumn="Attribute" TargetColumn="Attribute"   />
                                        </Inputs>
                                        <Outputs> 
                                            <Column SourceColumn="Attribute" TargetColumn="Attribute2"  />

                                        </Outputs>
                                        <InputPath OutputPathName="SurveyResponses.Output" />
                                    </FuzzyLookup>

                                </Transformations>
                            </Dataflow>
                        </Tasks>
                    </Package>
                </Packages>
                </Biml>

        <#@ template language="C#" hostspecific="true"#>
        <#@ import namespace="System.Data" #>
        <#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>

        <!--

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

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

        -->

Below should be an image of the output where the column called AddThisColumn is unchecked.

Below should be an image of a output where the column called AddThisColumn is check. How do i script this? addThisColumn is checked

billinkc billinkc · Accepted Answer · 2014-02-13T16:11:20

Clicking the checkbox on the righthand side signals you are adding a column to your output, right? To express this idea in biml, it'll simply be a matter of adding another Column in the Outputs collection for a given transformation.

<Outputs>
    <Column SourceColumn="Attribute" TargetColumn="Attribute2"  />
    <Column SourceColumn="AddThisColum" TargetColumn="AddThisColumn" />
</Outputs>

I used the biml from your earlier question which results in my screenshot adding Response to the output collection instead of adding Attribute renamed as Attribute2, which is done in the above snippet.

Moar columns

Simply tick a checkbox in a Fuzzy Lookup Transformation object using SSIS BIML

1 Answers