Use Powershell to hydrate objects and map if inital to elasticsearch using the MSSQL snappins.

ESMSSQLMigrator

Param(
  [bool]$firstRun,
  [Parameter(Mandatory=$true)]
  [string]$dbServer,
  [Parameter(Mandatory=$true)]
  [string]$userName,
  [Parameter(Mandatory=$true)]
  [string]$password,
  [Parameter(Mandatory=$true)]
  [string]$esServerInstance,
  [Parameter(Mandatory=$true)]
  [string]$Database
)


<#
    .Synopsis 
        creates a index maping using the default constrained, could be upgraded later to support a index mapping ES shell.

#>
function createIndexMappings() {
$mappings = '{ "Customer" : {
      
                                                   "properties" : {
        
                                                                              "fullName" : { 
                                                                                                  "type" : "string", 
                                                                                                  "index_analyzer" : "autocomplete", 
                                                                                                  "index" : "analyzed", 
                                                                                                  "search_analyzer" : "standard" 
                                                                                            },
       
                                                                                 "userName" : { 
                                                                                                       "type" : "string",
                                                                                                        "index_analyzer" : "autocomplete", 
                                                                                                         "index" : "analyzed", 
                                                                                                          "search_analyzer" : "standard" 
                                                                                                    }
    
,
       
                                                                                 "groupName" : { 
                                                                                                       "type" : "string",
                                                                                                        "index_analyzer" : "autocomplete", 
                                                                                                         "index" : "analyzed", 
                                                                                                          "search_analyzer" : "standard" 
                                                                                                    }
   
                                                                          }
    
                                        }
 }'


<#
    .Synopsis 
        Creates the analyzer that the index will use.

#>
Invoke-RestMethod http://$esServerInstance/customers/Customer/_mapping -Method Put -Body $mappings

}

function createAnalyzer {

$analyzer = ' {"analysis" : {
                                         "analyzer" : {
                                                    "autocomplete" : {
                                                                                        "tokenizer" : "whitespace",
                                                                                         "filter" : ["lowercase", "engram"]
                                                                                 }
                                                             },
                                            "filter" : {
                                                              "engram" : {
                                                                                           "type" : "edgeNGram",
                                                                                                        "min_gram" : 3,
                                                                                                        "max_gram" : 10
                                                                                 }
                                                         }
                                               }
}'

Invoke-RestMethod http://$esServerInstance/customers -Method Post -Body $analyzer


}





<#
    .Synopsis 
        Recreates the index from scratch

#>
function indexRecreate{

    try {
    # reset the index since this is a first time.
    write-host 'reinstalling index' 
    Invoke-RestMethod "http://$esServerInstance/customers" -Method Delete -ErrorAction Continue
  
    
    } Catch { Write-Host "Something went wrong!" } 

    createAnalyzer
    createIndexMappings
}





<#
    Description
        Program steps are as followed
            1. Goto database and get the view
            2. Convert the data into a JSON Object
            3. Send the data to a ElasticSearch server


#>

#
# Add the SQL Server provider.
#

$ErrorActionPreference = "Stop"

$sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"

if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
{
    throw "SQL Server Provider is not installed."
}
else
{
    $item = Get-ItemProperty $sqlpsreg
    $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)
}


#
# Set mandatory variables for the SQL Server rovider
#
Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

#
# Load the snapins, type data, format data
#
Push-Location
cd $sqlpsPath
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
Update-TypeData -PrependPath SQLProvider.Types.ps1xml 
update-FormatData -prependpath SQLProvider.Format.ps1xml 
Pop-Location

# ok lets define delete the index first and foremost
if ($firstRun -eq $true) {
    indexRecreate
    Write-Host "reseting last updated time stamp!"
    Invoke-sqlCmd "UPDATE UserData SET LastUpdatedOn = GETDATE()"  -ServerInstance "$dbServer" -Username "$userName" -Password "$password" -Database "$Database"
} 


$LastUpdatedOn=((get-date).AddMinutes(-1))
$query = "SELECT TOP 50 userId, UserName userName, FirstName firstName, LastName lastName, Cell cell, school schoolName, GroupName groupName from CustomerData where lastUpdatedOn=`$(LastUpdateOn)`;" 
$users = Invoke-sqlCmd "SELECT  firstName + ' ' + lastName fullName, userId, UserName userName, FirstName firstName, LastName lastName, Cell cell, school schoolName, GroupName groupName from CustomerData;"  -ServerInstance "$dbServer" -Username "$userName" -Password "$password" -Database "$Database" |select-object fullName, userId, userName, firstName, lastName, cell, schoolName, groupName





#iterate and process items
    $jsonUsers = ConvertTo-Json $users
    $counter = 0
    $usersCount = $users.Count

    $users | Foreach-Object {
      $objectToSend = ConvertTo-Json $_
      
      $userId = $_.userId
      $result = Invoke-RestMethod "http://$esServerInstance/customers/Customer/$userId" -Method Put -ContentType "application/json" -Body $objectToSend
      $counter++
      Write-Progress -Activity "Updating $($_.fullName) "  -PercentComplete (($counter/$usersCount)*100) -Status "$(($counter/$usersCount)*100)%"
      }