BCS Models - Part 2: Initial BCS External Content Types

This post is part of an eight part series describing the process I have followed to be able create a BCS model capable of indexing well over 10 million items.

Series Index:
BCS Models - Part 1: Target Database
BCS Models - Part 2: Initial BCS External Content Types  <-- You are here
BCS Models - Part 3: Crawl Results
BCS Models - Part 4: Bigger Database
BCS Models - Part 5: The Bigger Database
BCS Models - Part 6: How to eat this elephant?
BCS Models - Part 7: Changes to the BCS Model to support segmented crawl
BCS Models - Part 8: Crawl Results

BCS Models - Part 9: Crawl-Time Security 

This post will discuss the construction of the initial BCS model to enable SharePoint Search to crawl the extract of SharePoint.StackExchange.com from March 2015.  The DB was created in the post BCS Models - Part 1: Target Database.

Eric White's blog post does a great job of illustrating how to create the BCS entities.  The model I created uses the change log methodology for the incremental crawl.

As Eric states on his blog, the ChangedIdEnumerator and DeletedIdEnumerator methods and method instances have to be added to the model by hand.  The ECTs that were created via SharePoint designer are exported to a BCS Model (bdcm) file by selecting all of them together and clicking on the Export BDC Model button in the ribbon bar.

Here's a sample ChangedIdEnumerator and DeletedIdEnumerator:

           
             
            <Method IsStatic="false" Name="usp_getChangedQuestions">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">usp_getChangedQuestions</Property>
                <Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine</Property>
                <Property Name="RdbCommandText" Type="System.String">[dbo].[usp_getChangedQuestions]</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure</Property>
                <Property Name="Schema" Type="System.String">dbo</Property>
              </Properties>
<FilterDescriptors>
<FilterDescriptor Name="LastRunDate" Type="InputOutput">
 <Properties>
<Property Name="SynchronizationCookie" Type="System.String">ChangedItemCookie</Property>
 </Properties>
</FilterDescriptor>
<FilterDescriptor Name="FilterDescriptor" Type="Timestamp" />
 </FilterDescriptors>
 <Parameters>
<Parameter Name="@LastRunDate" Direction="InOut">
 <TypeDescriptor Name="LastRunDateTypeDescriptor" TypeName="System.DateTime" AssociatedFilter="LastRunDate">
<Interpretation>
 <NormalizeDateTime LobDateTimeMode="Local" />
</Interpretation>
 </TypeDescriptor>
</Parameter>
                <Parameter Direction="Return" Name="usp_getChangedQuestions">
                  <TypeDescriptor IsCollection="true" Name="usp_getChangedQuestions Collection" TypeName="System.Data.IDataReader, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="usp_getChangedQuestionElement">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" ReadOnly="true" IdentifierName="ID" Name="ID" />
                        </TypeDescriptors>
                     </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Default="true" Name="usp_getChangedQuestions Instance" Type="ChangedIdEnumerator" ReturnParameterName="usp_getChangedQuestions" >
                  <Properties>
                    <Property Name="LastDesignedOfficeItemType" Type="System.String">None</Property>
                  </Properties>
                </MethodInstance>
              </MethodInstances>
            </Method>
            <Method IsStatic="false" Name="usp_getDeletedQuestions">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">usp_getDeletedQuestions</Property>
                <Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine</Property>
                <Property Name="RdbCommandText" Type="System.String">[dbo].[usp_getDeletedQuestions]</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure</Property>
                <Property Name="Schema" Type="System.String">dbo</Property>
              </Properties>
<FilterDescriptors>
<FilterDescriptor Name="LastRunDate" Type="InputOutput">
 <Properties>
<Property Name="SynchronizationCookie" Type="System.String">DeletedItemCookie</Property>
 </Properties>
</FilterDescriptor>
<FilterDescriptor Name="FilterDescriptor" Type="Timestamp" />
 </FilterDescriptors>
 <Parameters>
<Parameter Name="@LastRunDate" Direction="InOut">
 <TypeDescriptor Name="LastRunDateTypeDescriptor" TypeName="System.DateTime" AssociatedFilter="LastRunDate">
<Interpretation>
 <NormalizeDateTime LobDateTimeMode="Local" />
</Interpretation>
 </TypeDescriptor>
</Parameter>
                <Parameter Direction="Return" Name="usp_getDeletedQuestions">
                  <TypeDescriptor IsCollection="true" Name="usp_getDeletedQuestions Collection" TypeName="System.Data.IDataReader, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
                    <TypeDescriptors>
                      <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="usp_getDeletedQuestionsElement">
                        <TypeDescriptors>
                          <TypeDescriptor TypeName="System.Int32" ReadOnly="true" IdentifierName="ID" Name="ID" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Default="true" Name="usp_getDeletedQuestions Instance" Type="DeletedIdEnumerator" ReturnParameterName="usp_getDeletedQuestions" >
                  <Properties>
                    <Property Name="LastDesignedOfficeItemType" Type="System.String">None</Property>
                  </Properties>
                </MethodInstance>
              </MethodInstances>
            </Method>
                 
               
             
           
The complete BDCM file is here.
5/7/2017: the link has been updated to a github repository