TransitionManager

2.3.0.1

lib/ETLScripts.ps1

## TM ETL Scripts
Function Get-TMETLScript {
    [CmdletBinding(DefaultParameterSetName = 'Default')]
    param(
        [Parameter(Mandatory = $false)]
        [String]$TMSession = "Default",
        
        [Parameter(Mandatory = $false)]
        [String]$Name,

        [Parameter(Mandatory = $false)]
        [String[]]$ProviderName,
        
        [Parameter(Mandatory = $false)]
        [String]$Server = $global:TMSessions[$TMSession].TMServer,
        
        [Parameter(Mandatory = $false)]
        [Bool]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        
        [Parameter(Mandatory = $false)]
        [Switch]$ResetIDs,

        [Parameter(Mandatory = $false, ParameterSetName = 'SaveCode')]
        [String]$SaveCodePath,
        
        [Parameter(Mandatory = $false, ParameterSetName = 'SaveCode')]
        [Switch]$Passthru
    )

    ## Get Session Configuration
    $TMSessionConfig = $global:TMSessions[$TMSession]
    if (-not $TMSessionConfig) {
        Write-Host 'TMSession: [' -NoNewline
        Write-Host $TMSession -ForegroundColor Cyan
        Write-Host '] was not Found. Please use the New-TMSession command.'
        Throw "TM Session Not Found. Use New-TMSession command before using features."
    }

    #Honor SSL Settings
    $TMCertSettings = $TMSessionConfig.AllowInsecureSSL ? @{SkipCertificateCheck = $true } : @{SkipCertificateCheck = $false }

    # Format the uri
    $instance = $Server.Replace('/tdstm', '').Replace('https://', '').Replace('http://', '')
    
    $uri = "https://$instance/tdstm/ws/dataingestion/datascript/list"

    try {
        $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
    }
     catch {
        return $_
    }

    if ($response.StatusCode -in @(200, 204)) {
        $Result = ($response.Content | ConvertFrom-Json).data
        if ($Result.Count -eq 0) { return $false }
    }
     else {
        return "Unable to collect ETL Scripts."
    }
    
    ## Get each ETL Script's Source Code in the list
    for ($i = 0; $i -lt $Result.Count; $i++) {
        
        $uri = "https://"
        $uri += $instance
        $uri += '/tdstm/ws/dataingestion/datascript/' + $Result[$i].id
        try {
            $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
        }
        catch {
            return $_
        }

        if ($response.StatusCode -eq 200) {
            $Result[$i] = ($response.Content | ConvertFrom-Json).data.datascript
        }
        else {
            return "Unable to collect ETL Scripts."
        }
    }

    if ($ResetIDs) {
        for ($i = 0; $i -lt $Result.Count; $i++) {
            $Result[$i].id = $null
            $Result[$i].provider.id = $null
        }
    }

    ## Return the details -- Filter based on passed parameters
    if ($ProviderName) {
        $Result = $Result | Where-Object { $_.provider.name -in $ProviderName }
    }
    elseif ($Name) {
        $Result = $Result | Where-Object { $_.name -eq $Name }
    } 
    
    ## Save the Code Files to a folder
    if ($SaveCodePath) {

        ## Save Each of the Script Source Data
        foreach ($Item in $Result) {

            ## Get a FileName safe version of the Provider Name
            $SafeProviderName = Get-FilenameSafeString -String $Item.provider.name
            $SafeScriptName = Get-FilenameSafeString -String $Item.name

            ## Create the Provider Action Folder path
            $ProviderPath = Join-Path $SaveCodePath  $SafeProviderName
            Test-FolderPath -FolderPath $ProviderPath
        
            ## Create a File ame for the Action
            $ProviderScriptPath = Join-Path $ProviderPath  ($SafeScriptName + '.TMETL.groovy')

            ## Build a config of the important References
            $TMConfig = [PSCustomObject]@{
                EtlScriptName = $Item.name
                Description   = $Item.description
                ProviderName  = $Item.provider.name
                IsAutoProcess = $Item.isAutoProcess
                Target        = $Item.target
                Mode          = $Item.mode
            } | ConvertTo-Json | Out-String

            ## Create a Script String output
            $ScriptOutput = [System.Text.StringBuilder]::new()
            [void]$ScriptOutput.AppendLine("/*********TransitionManager-ETL-Script*********")
            [void]$ScriptOutput.AppendLine()
            [void]$ScriptOutput.AppendLine($TMConfig)
            [void]$ScriptOutput.AppendLine()
        
            [void]$ScriptOutput.AppendLine('*********TransitionManager-ETL-Script*********/')
    
            [void]$ScriptOutput.AppendLine()
    
            ## Write the Script to the Configuration
            [void]$ScriptOutput.AppendLine($Item.etlSourceCode)
            [void]$ScriptOutput.AppendLine()

            ## Start Writing the Content of the Script (Force to overwrite any existing files)
            Set-Content -Path $ProviderScriptPath -Force -Value $ScriptOutput.toString()
        }
    } 

    if ($Passthru -or !$SaveCodePath) {
        return $Result
    }
}


Function New-TMETLScript {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][PSObject]$ETLScript,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        [Parameter(Mandatory = $false)][switch]$Update

    )    
    if ($global:TMSessions[$TMSession].TMVersion -like '4.6.*') {
        New-TMETLScript46 @PSBoundParameters
    }
     else {
        New-TMETLScript47 @PSBoundParameters
    }
}
Function New-TMETLScript46 {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][PSObject]$ETLScript,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL
    )
    
    ## Get Session Configuration
    $TMSessionConfig = $global:TMSessions[$TMSession]
    if (-not $TMSessionConfig) {
        Write-Host 'TMSession: [' -NoNewline
        Write-Host $TMSession -ForegroundColor Cyan
        Write-Host '] was not Found. Please use the New-TMSession command.'
        Throw "TM Session Not Found. Use New-TMSession command before using features."
    }

    #Honor SSL Settings
    if ($TMSessionConfig.AllowInsecureSSL) {
        $TMCertSettings = @{SkipCertificateCheck = $true }
    }
     else { 
        $TMCertSettings = @{SkipCertificateCheck = $false }
    }

    $ETLScriptName = $ETLScript.name
    $ExistingETLScript = Get-TMETLScript -Name $ETLScriptName -TMSession $TMSession
    if ($ExistingETLScript) {
        # Write-Host "ETL Script Exists: "$ETLScriptName
        if ($PassThru) { return $ExistingETLScript } else { return }
    } 

    ## Action 0 (Renumber) - Confirm the name is unique
    $uri = "https://"
    $uri += $Server
    $uri += '/tdstm/ws/dataingestion/datascript/validateUnique'

    $PostBodyJSON = @{
        name       = $ETLScriptName
        providerId = (Get-TMProvider -Name $ETLScript.provider.name -TMSession $TMSession).id
    } | ConvertTo-Json -Depth 100

    try {
        $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBodyJSON @TMCertSettings 
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                $isUnique = $responseContent.data.isUnique
                if ($isUnique -ne $true) {
                    $thisETLScript = Get-TMETLScript -Name $ETLScriptName -TMSession $TMSession
                }
            }    
        }
    }
     catch {
        Write-Host "Unable to determine if ETL Script Name is unique."
        return $_
    }

    
    ## Action 2, Create the ETL Script so the Script code can be saved into it.
    $uri = "https://"
    $uri += $Server
    $uri += '/tdstm/ws/dataingestion/datascript'

    ## If there is an existing ETL script, update it
    if ($thisETLScript) {
        $ETLScript.id = $thisETLScript.id
        $uri += '/' + $ETLScript.id
    }
     else {
        $ETLScript.PSObject.properties.Remove('id')
    }
    
    ## Lookup Cross References
    $ProviderID = (Get-TMProvider -TMSession $TMSession | Where-Object { $_.name -eq $ETLScript.provider.name }).id

    $PostBodyJSON = @{
        name        = $ETLScriptName
        mode        = $ETLScript.mode
        description = $ETLScript.description
        providerId  = $ProviderID
        # etlSourceCode = $ETLScript.etlSourceCode
    } | ConvertTo-Json -Depth 100

    try {
        $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBodyJSON @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                
                $NewETLScript = $responseContent.data.datascript
                $PostBodyJSON = @{
                    id     = $NewETLScript.id
                    script = $ETLScript.etlSourceCode
                } | ConvertTo-Json -Depth 100

                $uri = "https://"
                $uri += $Server
                $uri += '/tdstm/ws/dataingestion/dataScript/saveScript'
                $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBodyJSON @TMCertSettings
                if ($response.StatusCode -eq 200) {
                    $responseContent = $response.Content | ConvertFrom-Json
                    if ($responseContent.status -eq 'success') {
                        if ($PassThru) { return $responseContent.data.dataScript } else { return }
                    }
                }
            }    
        }
    }
     catch {
        Write-Host "Unable to create ETL script."
        return $_
    }
    
}
Function New-TMETLScript47 {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][PSObject]$ETLScript,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        [Parameter(Mandatory = $false)][switch]$Update

    )    
    ## Get Session Configuration
    $TMSessionConfig = $global:TMSessions[$TMSession]
    if (-not $TMSessionConfig) {
        Write-Host 'TMSession: [' -NoNewline
        Write-Host $TMSession -ForegroundColor Cyan
        Write-Host '] was not Found. Please use the New-TMSession command.'
        Throw "TM Session Not Found. Use New-TMSession command before using features."
    }

    #Honor SSL Settings
    if ($TMSessionConfig.AllowInsecureSSL) {
        $TMCertSettings = @{SkipCertificateCheck = $true }
    }
     else { 
        $TMCertSettings = @{SkipCertificateCheck = $false }
    }

    ## Look for an existing Script
    $ETLScriptName = $ETLScript.name
    $ExistingETLScript = Get-TMETLScript -Name $ETLScriptName -TMSession $TMSession
    
    ## If the Script should not be updated, return it
    if ($ExistingETLScript) {

        ## End the function if there is not an update to be made
        if ($Update) {
            
            ## Record the ETLScriptId for the update
            $ETLScriptID = $ExistingETLScript.id

        }
        else {
            
            ## If $PassThru is present, return the Existing ETL script.
            if ($PassThru) {
                return $ExistingETLScript
            }
            else { 
                return 
            }

        }
    }
     else {
        
        ## Lookup Provider ID
        if ([String]::IsNullOrWhiteSpace($ETLScript.provider.name)) {
            Write-Error -Message "Provider name is blank for ETL Script: $($ETLScript.name)"
            return
        }
        $ProviderID = (Get-TMProvider -Name $ETLScript.provider.name -TMSession $TMSession).id
        if (!$ProviderID) {
            # Create the provider if it doesn't exist
            $NowFormatted = (Get-Date -Format 'yyyy-MM-ddTHH:mm:ssZ' -AsUTC).ToString()
            $Provider = [PSCustomObject]@{
                id          = $null
                name        = $ETLScript.provider.name
                description = ""
                comment     = ""
                dateCreated = $NowFormatted
                lastUpdated = $NowFormatted
            }
            $ProviderID = (New-TMProvider -Provider $Provider -PassThru -TMSession $TMSession).id
        }

        
        ## There is not an existing ETL Script, Create the basic
        ## Create the shell ETL object, which must be created first
        $PostBodyJSON = @{
            description = $ETLScript.description
            mode        = $ETLScript.mode
            name        = $ETLScript.name
            providerId  = $ProviderID
        } | ConvertTo-Json -Compress

        try {
            
            $uri = "https://"
            $uri += $Server
            $uri += '/tdstm/ws/dataingestion/datascript'

            ## Post the ETL Object to the server
            Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
            $response = Invoke-WebRequest -Method 'POST' -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBodyJSON @TMCertSettings
            if ($response.StatusCode -eq 200) {
                $responseContent = $response.Content | ConvertFrom-Json
                if ($responseContent.status -eq "success") {
                
                    ## Now that the shell exists, get it's ID
                    $ETLScriptID = $responseContent.data.datascript.id
                    
                }    
            }
        }
        catch {
            Write-Host "Unable to create ETL script."
            return $_
        }

    }

    ## Now that we have an ID for the ETL script, send the source code content
    $PostBodyJSON = @{
        id     = $ETLScriptID
        script = $ETLScript.etlSourceCode
    } | ConvertTo-Json -Depth 100

    try {
        
        ## Post the Source to be saved for this script.
        $uri = "https://"
        $uri += $Server
        $uri += '/tdstm/ws/dataScript/saveScript'
        $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBodyJSON @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq 'success') {
                if ($PassThru) {
                    return $responseContent.data.dataScript
                } 
            }
        }
        elseif ($response.StatusCode -eq 204) {
            return
        }
    }
 catch {
        Write-Host "Unable to save ETL Source to server"
        return $_
    }
    
}

Function Invoke-TMETLScript {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][String]$ETLScriptName,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        [Parameter(Mandatory = $false)][bool]$QueueBatches = $false,
        [Parameter(Mandatory = $false)][bool]$MonitorBatches = $false,
        [Parameter(Mandatory = $true)][String]$DataType,
        [Parameter(Mandatory = $false)]$Data,
        [Parameter(Mandatory = $false)][String]$FilePath,
        [Parameter(Mandatory = $false)][String]$FileName,
        [Parameter(Mandatory = $false)][Int16]$ActivityId,
        [Parameter(Mandatory = $false)][Int16]$ParentActivityId = -1
    )

    ## This function wraps the appropriate version of the function to handle the Invoke-ETL Request
    if ($global:TMSessions[$TMSession].TMVersion) {
        $TMVersion = $global:TMSessions[$TMSession].TMVersion
    }
 else {
        $TMVersion = $TMVersion = Get-TMVersion -TMSession $TMSession -Server $Server
    }

    $InvocationParameters = $MyInvocation.BoundParameters
    if ($TMVersion -in @("4.5.9", '4.6.3')) {
        Invoke-TMETLScript46 @InvocationParameters
    }
    else {
        Invoke-TMETLScript474 @InvocationParameters
    }
}

Function Invoke-TMETLScript47 {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][String]$ETLScriptName,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        [Parameter(Mandatory = $false)][bool]$QueueBatches = $false,
        [Parameter(Mandatory = $false)][bool]$MonitorBatches = $false,
        [Parameter(Mandatory = $true)][String]$DataType,
        [Parameter(Mandatory = $false)]$Data,
        [Parameter(Mandatory = $false)][String]$FilePath,
        [Parameter(Mandatory = $false)][String]$FileName,
        [Parameter(Mandatory = $false)][Int16]$ActivityId,
        [Parameter(Mandatory = $false)][Int16]$ParentActivityId = -1
    )
    ## Get Session Configuration
    $TMSessionConfig = $global:TMSessions[$TMSession]
    if (-not $TMSessionConfig) {
        Write-Host 'TMSession: [' -NoNewline
        Write-Host $TMSession -ForegroundColor Cyan
        Write-Host '] was not Found. Please use the New-TMSession command.'
        Throw "TM Session Not Found. Use New-TMSession command before using features."
    }

    #Honor SSL Settings
    if ($TMSessionConfig.AllowInsecureSSL) {
        $TMCertSettings = @{SkipCertificateCheck = $true }
    }
 else { 
        $TMCertSettings = @{SkipCertificateCheck = $false }
    }

    ## If a File Path was passed and there is no data, Read the file
    if ($FileName -and -not $Data) {
        $Data = Get-Content $FileBody
    }

    if ($ActivityId) {
        ## Parent ID is only used on the root 'TransitionManager Data Import' Activity
        ## ParentID + 1 = Import Batches
        ## ParentID + 2 + n for each batch

        ## Add the ETL Processing Progress Indicators
        $ProgressIndicators = @()
        $ProgressIndicators += @{ Id = $ActivityId; Activity = 'TransitionManager Data Import'; ParentId = $ParentActivityId }
        $ProgressIndicators += @{ Id = ($ActivityId + 1); Activity = 'ETL Data Transformation'; ParentId = $ActivityId }
        $ProgressIndicators += @{ Id = ($ActivityId + 2); Activity = 'Manage Batches'; ParentId = $ActivityId }
        
        #Write Progress Indicators for each in the Array
        $ProgressIndicators | ForEach-Object {
            Write-Progress @_ -CurrentOperation 'Queued' -PercentComplete 0
        }
    }
    
    ## Fix the Server URL
    $instance = $Server.Replace('/tdstm', '')
    $instance = $instance.Replace('https://', '')
    $instance = $instance.Replace('http://', '')

    $Boundary = '----------------------------540299933173025267350719'

    ## First Test to see if the ETL Script exists or not
    if ($ActivityId) { 
        
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Validating ETL Script' -Status 'Confirming ETL Script exists in TransitionManager' -PercentComplete 5
    }
    $ETLScript = Get-TMETLScript -TMSession $TMSession -Name $ETLScriptName
    if (-Not $ETLScript) { Throw  'The ETL Script [' + $ETLScriptName + '] does not exist' }
    
    ## Build the Data Post
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/fileSystem/uploadFileETLAssetImport"
        
    ## FileName
    if (-not $FileName) {
        $FileName = ('DataForETL_' + (Get-Date -Format FileDateTime ) + '.' + $DataType)
    }
    $FileLine = 'Content-Disposition: form-data; name="file"; filename="' + $FileName + '"'
    
    $CRLF = "`r`n";
    switch ($DataType) {
        'json' { 
            $PostBody = ("-----------------------------$boundary",
                'Content-Disposition: form-data; name="uploadType"',
                '',
                'assetImport',
            
                "-----------------------------$boundary",
                $FileLine,
                "Content-Type: application/json",
                '',
                ($Data | ConvertTo-Json -Depth 100),
                "-----------------------------$boundary--",
                ''
            ) -join $CRLF
        }
        'csv' { 
            # $csvData = ($Data | ConvertTo-Csv -NoTypeInformation) -join $CRLF
            $PostBody = ("-----------------------------$boundary",
                'Content-Disposition: form-data; name="uploadType"',
                '',
                'assetImport',
                
                "-----------------------------$boundary",
                $FileLine,
                "Content-Type: application/vnd.ms-excel",
                '',
                $Data,
                '',
                "-----------------------------$boundary--",
                ''
            ) -join $CRLF
        }
        'xlsx' { 
            $PostBody = ("-----------------------------$boundary",
                'Content-Disposition: form-data; name="uploadType"',
                '',
                'assetImport',
            
                "-----------------------------$boundary",
                $FileLine,
                "Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                '',
                (Get-Content $Data -Encoding 'UTF-8' -Raw ),
                "-----------------------------$boundary--",
                ''
            ) -join $CRLF
        }
        Default { }
    }
    

    ## Upload the Data File
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Uploading Data' -Status ('Uploading ' + $FileName)  -PercentComplete 5
    }
    
    $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession ` ##-Body $PostBody `
    -ContentType ('multipart/form-data; boundary=---------------------------' + $boundary) @TMCertSettings
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ETLdataFileName = $responseContent.data.filename
        }
        else { 
            Throw "Unable to upload data to TM ETL pre-transform storage."
        }
    }

    ## With the file uploaded, Initiate the ETL script on the server
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/assetImport/initiateTransformData"
    $uri += "?dataScriptId=" + $ETLScript.id
    $uri += "&filename=" + $ETLdataFileName
                        
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                        
    ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Starting ETL Processing' -PercentComplete 5
    }
     else {
        Write-Host 'TransitionManager Data Import: Starting ETL Processing'
    }
    
    $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ETLProgressKey = $responseContent.data.progressKey
        }
        else {
            throw $responseContent.errors
        }
    }
     else {
        Throw $response
    }
    
    
    ## Supply Progress from ETL Transformation Progress
    if ($ActivityId) { 
        Write-Progress -Id $($ActivityId + 1) -Activity 'ETL Data Transformation' -CurrentOperation 'Running ETL Transformation'  -PercentComplete 0 -ParentId $ActivityId
    }
     else {
        Write-Host 'ETL Data Transformation: Starting ETL Processing'
    }
                
    ## The ETL is underway, Setup a progress URL
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/progress/" + $ETLProgressKey
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                
    ## Poll for the status of the ETL engine
    $Completed = $false
    while ($Completed -eq $false) {
            
        ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
        $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                $ETLProgress = $responseContent.data
                                    
                switch ($ETLProgress.status) {
                    "Queued" { 
                        
                        $CurrentOperation = 'ETL Queued'
                        $Status = 'Queued'
                        $ProgressString = 'Status - Queued: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2    
                        
                    }
                    "Pending" { 
                        $CurrentOperation = 'ETL Pending'
                        $Status = 'Pending'
                        $ProgressString = 'Status - Pending: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2    
                    }
                    "RUNNING" { 
                        $CurrentOperation = 'ETL Running'
                        $Status = 'Transforming Data'
                        $ProgressString = 'Status - Running: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2    
                    }
                    "COMPLETED" {
                        $EtlOutputKey = $ETLProgress.detail
                        $CurrentOperation = 'ETL Processing Complete'
                        $Status = 'Creating Import Batches'
                        $Completed = $true
                        $SleepSeconds = 0
                        $PercentComplete = 99
                        $ProgressString = "Status - ETL Processing Complete, Creating Import Batches."
                    }
                    "Failed" {
                        $CurrentOperation = 'Failed'
                        $Status = $ETLProgress.status
                        Write-Host "ETL Processing Failed "$ETLProgress.detail
                        Throw $ETLProgress.detail
                    }
                    Default { 
                        $CurrentOperation = 'State Unknown'
                        $Status = 'Unknown. Sleeping to try again.'
                        $ProgressString = "Unknown Status: " + $ETLProgress.status
                        $PercentComplete = 99
                        $SleepSeconds = 2
                    }
                }
                
                ## Notify the user of the ETL Progress
                if ($ActivityId) { 
                    Write-Progress -Id ($ActivityId + 1) `
                        -ParentId $ActivityId `
                        -Activity 'ETL Data Transformation' `
                        -CurrentOperation $CurrentOperation `
                        -Status $Status `
                        -PercentComplete $PercentComplete
                }
                else {
                    Write-Host $ProgressString
                }
                Start-Sleep -Seconds $SleepSeconds
            }    
        }    
    }

    ## Notify the user of the ETL Completion
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) `
            -ParentId $ActivityId `
            -Activity 'ETL Data Transformation' `
            -CurrentOperation 'ETL Processing Complete'  `
            -Status 'Creating Import Batches' `
            -PercentComplete 99
        Write-Progress -Id ($ActivityId + 2) `
            -ParentId $ActivityId `
            -Activity 'Manage Batches' `
            -CurrentOperation 'Creating Import Batches'  `
            -Status 'Creating Import Batches' `
            -PercentComplete 1
    }
     else {
        Write-Host 'ETL Processing Complete, Creating Import Batches'
    }

    ## With the ETL converted, Use it to create Import Batches
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/assetImport/loadData?filename="
    $uri += $EtlOutputKey
                            
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                            
    ## Post the Transformed data filename to the ETL engine to Import the Batches.
    $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $Batches = $responseContent.data
                
            ## Print the results of the ETL Transformation Process
            if ($ActivityId) { 
                Write-Progress -Id ($ActivityId + 1) `
                    -ParentId $ActivityId `
                    -Activity 'ETL Data Processing' `
                    -CurrentOperation 'Batches Created' `
                    -Status ([string]$Batches.batchesCreated + ' Batches created') `
                    -PercentComplete 100
                Write-Progress -Id ($ActivityId + 2) `
                    -ParentId $ActivityId `
                    -Activity 'Manage Batches' `
                    -CurrentOperation 'Batches Created' `
                    -Status ([string]$Batches.batchesCreated + ' Batches created') `
                    -PercentComplete 1
            }
            else {
                Write-Host "Batches Created:" $Batches.batchesCreated
            }
        }    
    }
     else {
        Throw 'Failed to Import ETL Result.'
    }
    
    ## Complete the Transformation Activity
    Write-Progress -Id ($ActivityId + 1) `
        -ParentId $ActivityId `
        -Activity 'ETL Data Transformation' `
        -CurrentOperation 'Batch Creation Complete'  `
        -Status ('Created ' + [string]$Batches.batchesCreated + 'Import Batches') `
        -PercentComplete 100 `
        -Complete

    if ($QueueBatches) {
        
        ## Assemble the batch list object. Dependencies are deliberately moved to the end
        $BatchesToProcess = [System.Collections.ArrayList]@()

        ## Separate Asset batches from dependency Batches
        $Batches.domains | Where-Object { $_.domainClass -ne 'Dependency' } | ForEach-Object { $BatchesToProcess.Add($_) | Out-Null }
        $Batches.domains | Where-Object { $_.domainClass -eq 'Dependency' } | ForEach-Object { $BatchesToProcess.Add($_) | Out-Null }
    
        ## Write Progress to the Monitor Import Batches
        if ($ActivityId) { 
            Write-Progress -Id ($ActivityId + 2) `
                -ParentId $ActivityId `
                -Activity 'Manage Batches' `
                -CurrentOperation 'Queueing Batches' `
                -Status ('Queueing ' + [string]$Batches.batchesCreated + ' batches.') `
                -PercentComplete 5
        }
        else {
            Write-Host "Queueing Batches:" $Batches.batchesCreated
        }

        ## Add a Progress Activity for each of the Import Batches
        for ($i = 0; $i -lt $BatchesToProcess.Count; $i++) {
            
            ## Set Variable for Batch
            $Batch = $BatchesToProcess[$i]

            ## Create Progress Results for the batches that were created
            if ($ActivityId) { 
            
                ## Activity Explained: The $ActivityId is considered the (Root) + 2 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                    -ParentId ($ActivityId + 2) `
                    -Activity ('Batch: ' + $Batch.batchId + ' | ' + $Batch.rowsCreated + ' ' + $Batch.domainClass) `
                    -CurrentOperation 'Queueing Batch' `
                    -Status ([String]$Batch.rowsCreated + ' ' + $Batch.domainClass + ' records created.') `
                    -PercentComplete 0
            }
            else {
                Write-Host "Batch Created: " $Batch.rowsCreated $Batch.domainClass 'records created.'
            }
        }

        ## Queue each Batch
        for ($i = 0; $i -lt $BatchesToProcess.Count; $i++) {
            
            ## Set Variable for Batch
            $Batch = $BatchesToProcess[$i]
            if ($Batch.rowsCreated -gt 0) {

                ## With the file uploaded, Initiate the ETL script on the server
                $uri = "https://"
                $uri += $instance
                $uri += "/tdstm/ws/import/batches"
                                
                Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
            
                $PostBody = @{ 
                    action = 'QUEUE'
                    ids    = $Batch.batchId
                } | ConvertTo-Json -Depth 100


                ## Post this batch to begin it's Queueing
                $response = Invoke-WebRequest -Method Patch -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBody @TMCertSettings
                if ($response.StatusCode -eq 200) {
                    $responseContent = $response.Content | ConvertFrom-Json
                    if ($responseContent.status -eq "success") {
                        
                        ## Create Progress Results for the batches that were created
                        if ($ActivityId) { 
                            
                            ## Notify the Manage Batches Activity of a queued batch
                            Write-Progress -Id ($ActivityId + 2) `
                                -ParentId $ActivityId `
                                -Activity 'Manage Batches' `
                                -CurrentOperation 'Queueing Batches' `
                                -Status ('Queued ' + $Batch.domainClass + ' Batch') `
                                -PercentComplete 5

                            ## Activity (Root) + 2 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                            Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                                -ParentId ($ActivityId + 2) `
                                -Activity ('Batch: ' + $Batch.batchId + ' | ' + $Batch.rowsCreated + ' ' + $Batch.domainClass) `
                                -CurrentOperation 'Batch Queued'  `
                                -Status 'Queued' `
                                -PercentComplete 0
                        }
                        else {
                            Write-Host "Batch Queued:" $Batch.domainClass
                        }
                    }
                    else {
                        Throw 'Failed to Queue Batch'
                    }    
                }
                else {
                    Throw 'Failed to Queue Batch.'
                }
            }
        }
    }

    ## Allow the batch to be monitored and reported on in the UI
    if ($MonitorBatches) {
        $BatchStatus = @{
            CompletedBatches = 0
            TotalBatches     = $BatchesToProcess.Count
        }
        
        ## Monitor the batches to completion
        for ($i = 0; $i -lt $BatchesToProcess.Count; $i++) {
            
            ## Set Variable for Batch
            $Batch = $BatchesToProcess[$i]
            if ($Batch.rowsCreated -gt 0) {
                ## With the file uploaded, Initiate the ETL script on the server
                $uri = "https://"
                $uri += $instance
                $uri += "/tdstm/ws/import/batch/"
                $uri += $Batch.batchId
                $uri += "/progress"
                                    
                Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                
                ## Poll for the status of the Import Batch
                if ($ActivityId) { 
                    
                    ## Activity (Root) + 2 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                    Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                        -ParentId ($ActivityId + 2) `
                        -Activity ('Batch: ' + $Batch.batchId + ' | ' + $Batch.rowsCreated + ' ' + $Batch.domainClass) `
                        -CurrentOperation 'Monitoring Progress'  `
                        -Status 'Importing' `
                        -PercentComplete 1
                }
                else {
                    Write-Host 'Getting Status for '$Batch.domainClass
                }

                ## Start a loop to run while the batch is not complete.
                $Completed = $false
                while ($Completed -eq $false) {
                    
                    ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
                    $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
                    if ($response.StatusCode -eq 200) {
                        $responseContent = $response.Content | ConvertFrom-Json
                        if ($responseContent.status -eq "success") {
                            
                            $BatchProgress = $responseContent.data
                            
                            switch ($BatchProgress.status.code) {
                                "RUNNING" { 
                                    $CurrentOperation = 'Import Running'
                                    $Status = 'Importing Batch Data'
                                    $ProgressString = $DomainClass + ' Import Running: ' + $BatchProgress.progress + "%"
                                    $PercentComplete = $BatchProgress.progress    
                                    $SleepSeconds = 2    
                                }
                                "QUEUED" { 
                                    $CurrentOperation = 'Batch Queued'
                                    $Status = 'Batch Queued'
                                    $ProgressString = $DomainClass + ' status: Queued'
                                    $PercentComplete = $BatchProgress.progress    
                                    $SleepSeconds = 2    
                                    
                                }
                                "PENDING" { 
                                    $CurrentOperation = 'Batch Pending'
                                    $Status = 'Batch Pending'
                                    $ProgressString = $DomainClass + ' status: Pending'
                                    $PercentComplete = $BatchProgress.progress    
                                    $SleepSeconds = 2
                                    
                                }
                                "COMPLETED" {
                                    $Completed = $true
                                    $CurrentOperation = 'Complete'
                                    $Status = 'Complete'
                                    $ProgressString = ($batch.domainClass + "Importing Complete")
                                    $PercentComplete = 100
                                    $SleepSeconds = 0

                                }
                                Default { 
                                    $CurrentOperation = 'Status Unknown. Retrying'
                                    $Status = 'Retrying'
                                    $ProgressString = ($batch.domainClass + "Status Unkown. Retrying")
                                    $PercentComplete = 1    
                                    $SleepSeconds = 2
                                        
                                }
                            }
                            
                            ## Display the Status of this loop
                            if ($ActivityId) { 
                    
                                ## Activity (Root) + 2 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                                Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                                    -ParentId ($ActivityId + 3) `
                                    -Activity ('Batch: ' + $Batch.batchId + ' | ' + $Batch.rowsCreated + ' ' + $Batch.domainClass) `
                                    -CurrentOperation $CurrentOperation  `
                                    -Status $Status  `
                                    -PercentComplete $PercentComplete
                            }
                            else {
                                Write-Host $ProgressString
                            }
                            
                            ## Calcuate Percentage for Manage Batches progress bar. This is based on how many batches there are, how many are done and what percentage we have in this loop
                            $ManageBatchesPercentage = ((($BatchStatus.CompletedBatches * 100) + $PercentComplete) / ($BatchStatus.TotalBatches * 100))
                            
                            Write-Progress -Id ($ActivityId + 2) `
                                -ParentId $ActivityId `
                                -Activity 'Manage Batches' `
                                -CurrentOperation 'Monitoring Progress'  `
                                -Status 'Processing' `
                                -PercentComplete $ManageBatchesPercentage
                            
                            ## Increate how many jobs are marked as completed
                            Start-Sleep -Seconds $SleepSeconds
                        }    
                    }
                }
            }
            ## Now that this batch is done, increment the Completed Batches counter
            $BatchStatus.CompletedBatches++
        }

        ## Mark the Manage Batches Activity Complete
        if ($ActivityId) {
            Write-Progress -Id ($ActivityId + 2) `
                -ParentId $ActivityId `
                -Activity 'Manage Batches' `
                -CurrentOperation 'Complete'  `
                -Status 'All Batches Imported' `
                -PercentComplete 100
        }
    }

    ## Mark the TM Import Activity complete
    if ($ActivityId) {
        Write-Progress -Id $ActivityId `
            -ParentId $ParentActivityId `
            -Activity 'TransitionManager Data Import' `
            -CurrentOperation 'Complete'  `
            -Status 'All Data Imported' `
            -PercentComplete 100
    }
}

Function Invoke-TMETLScript474 {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][String]$ETLScriptName,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        [Parameter(Mandatory = $false)][bool]$QueueBatches = $false,
        [Parameter(Mandatory = $false)][bool]$MonitorBatches = $false,
        [Parameter(Mandatory = $true)][String]$DataType,
        [Parameter(Mandatory = $false)]$Data,
        [Parameter(Mandatory = $false)][String]$FilePath,
        [Parameter(Mandatory = $false)][String]$FileName,
        [Parameter(Mandatory = $false)][Int16]$ActivityId,
        [Parameter(Mandatory = $false)][Int16]$ParentActivityId = -1
    )
    ## Get Session Configuration
    $TMSessionConfig = $global:TMSessions[$TMSession]
    if (-not $TMSessionConfig) {
        Write-Host 'TMSession: [' -NoNewline
        Write-Host $TMSession -ForegroundColor Cyan
        Write-Host '] was not Found. Please use the New-TMSession command.'
        Throw "TM Session Not Found. Use New-TMSession command before using features."
    }
    
    ## If a File Path was passed and there is no data, Read the file
    if ($FilePath -and -not $Data) {
        $Data = Get-Content $FilePath -Raw
        $FileName = (Get-Item $FilePath).Name
    }
    
    ## If a File Path was passed and there is no File Name, get the file name
    if ($FilePath -and -not $FileName) {
        $FileName = (Get-Item $FilePath).Name
    }
    
    #Honor SSL Settings
    if ($TMSessionConfig.AllowInsecureSSL) {
        $TMCertSettings = @{SkipCertificateCheck = $true }
    }
    else { 
        $TMCertSettings = @{SkipCertificateCheck = $false }
    }
    
    if ($ActivityId) {
        ## Parent ID is only used on the root 'TransitionManager Data Import' Activity
        ## ParentID + 1 = Transform Data
        ## ParentID + 2 = Import Batches
        ## ParentID + 3 + n for each batch

        ## Add the ETL Processing Progress Indicators
        $ProgressIndicators = @()
        $ProgressIndicators += @{ Id = $ActivityId; Activity = 'TransitionManager Data Import'; ParentId = $ParentActivityId }
        $ProgressIndicators += @{ Id = ($ActivityId + 1); Activity = 'ETL Data Transformation'; ParentId = $ActivityId }
        $ProgressIndicators += @{ Id = ($ActivityId + 2); Activity = 'Import Batches'; ParentId = $ActivityId }
        $ProgressIndicators += @{ Id = ($ActivityId + 3); Activity = 'Monitor Batches'; ParentId = $ActivityId }
        
        #Write Progress Indicators for each in the Array
        $ProgressIndicators | ForEach-Object {
            Write-Progress @_ -CurrentOperation 'Queued' -PercentComplete 0
        }
    }
    
    ## Fix the Server URL
    $instance = $Server.Replace('/tdstm', '')
    $instance = $instance.Replace('https://', '')
    $instance = $instance.Replace('http://', '')

    $Boundary = '----------------------------540299933173025267350719'

    ## First Test to see if the ETL Script exists or not
    if ($ActivityId) { 
        
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Validating ETL Script' -Status 'Confirming ETL Script exists in TransitionManager' -PercentComplete 5
    }
    $ETLScript = Get-TMETLScript -TMSession $TMSession -Name $ETLScriptName
    if (-Not $ETLScript) { Throw  'The ETL Script [' + $ETLScriptName + '] does not exist' }
    
    ## Build the Data Post
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/fileSystem/uploadFileETLAssetImport"
        
    ## FileName
    if (-not $FileName) {
        $FileName = ('DataForETL_' + (Get-Date -Format FileDateTime ) + '.' + $DataType)
    }

    ## Construct the PostBody and Form Data for the request
    $CRLF = "`r`n";
    $PostBody = ("-----------------------------$boundary",
        'Content-Disposition: form-data; name="uploadType"',
        '',
        'assetImport',
        "-----------------------------$boundary",
        ''
    ) -join $CRLF
    $FileUpload = @{
        Form = @{
            uploadType = 'assetImport'
            file       = Get-Item -Path $FilePath
            fileName   = $FileName
        }
    }

    ## Upload the Data File
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Uploading Data' -Status ('Uploading ' + $FileName)  -PercentComplete 5
    }
    
    try {
        ## If the file is being uploaded, append the fileupload to the command
        if ($FileUpload) {
            $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -ContentType ('multipart/form-data; boundary=---------------------------' + $boundary) @TMCertSettings @FileUpload
            
        }
        else {

            $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -ContentType ('multipart/form-data; boundary=---------------------------' + $boundary) @TMCertSettings
        }

    }
    catch {
        throw $_
    }
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ETLdataFileName = $responseContent.data.filename
        }
        else { 
            Throw "Unable to upload data to TM ETL pre-transform storage."
        }
    }

    ## With the file uploaded, Initiate the ETL script on the server
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/assetImport/initiateTransformData"
    $uri += "?dataScriptId=" + $ETLScript.id
    $uri += "&filename=" + $ETLdataFileName
                        
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession

    ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Starting ETL Processing' -PercentComplete 5
    }
    else {
        Write-Host 'TransitionManager Data Import: Starting ETL Processing'
    }
    
    $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ETLProgressKey = $responseContent.data.progressKey
        }
        else {
            throw $responseContent.errors
        }
    }
    else {
        Throw $response
    }
    
    
    ## Supply Progress from ETL Transformation Progress
    if ($ActivityId) { 
        Write-Progress -Id $($ActivityId + 1) -Activity 'ETL Data Transformation' -CurrentOperation 'Running ETL Transformation'  -PercentComplete 0 -ParentId $ActivityId
    }
    else {
        Write-Host 'ETL Data Transformation: Starting ETL Processing'
    }
            
    ## The ETL is underway, Setup a progress URL
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/progress/" + $ETLProgressKey
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                
    ## Poll for the status of the ETL engine
    ## TODO: This should be converted to a function for polling the job engine. It's nearly dupilcated now in the Import Batch watching.
    $Completed = $false
    while ($Completed -eq $false) {
            
        ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
        $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                $ETLProgress = $responseContent.data
                                    
                switch ($ETLProgress.status) {
                    "Queued" { 
                        
                        $CurrentOperation = 'ETL Queued'
                        $Status = 'Queued'
                        $ProgressString = 'Status - Queued: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2
                        Break
                        
                    }
                    "Pending" { 
                        $CurrentOperation = 'ETL Pending'
                        $Status = 'Pending'
                        $ProgressString = 'Status - Pending: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2
                        Break
                    }
                    "RUNNING" { 
                        $CurrentOperation = 'ETL Running'
                        $Status = 'Transforming Data'
                        $ProgressString = 'Status - Running: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2    
                        Break
                    }
                    "COMPLETED" {
                        $EtlOutputKey = $ETLProgress.detail
                        $CurrentOperation = 'ETL Processing Complete'
                        $Status = 'Creating Import Batches'
                        $Completed = $true
                        $SleepSeconds = 0
                        $PercentComplete = 99
                        $ProgressString = "Status - ETL Processing Complete, Creating Import Batches."
                        Break
                    }
                    "Failed" {
                        $CurrentOperation = 'Failed'
                        $Status = $ETLProgress.status
                        Write-Host "ETL Processing Failed "$ETLProgress.detail
                        Throw $ETLProgress.detail
                    }
                    Default { 
                        $CurrentOperation = 'State Unknown'
                        $Status = 'Unknown. Sleeping to try again.'
                        $ProgressString = "Unknown Status: " + $ETLProgress.status
                        $PercentComplete = 99
                        $SleepSeconds = 2
                        Break
                    }
                }
                
                ## Notify the user of the ETL Progress
                if ($ActivityId) { 
                    Write-Progress -Id ($ActivityId + 1) `
                        -ParentId $ActivityId `
                        -Activity 'ETL Data Transformation' `
                        -CurrentOperation $CurrentOperation `
                        -Status $Status `
                        -PercentComplete $PercentComplete
                }
                else {
                    Write-Host $ProgressString
                }
                Start-Sleep -Seconds $SleepSeconds
            }    
        }    
    }

    ## Update Progress and transition to Activity + 2
    Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Transformation Complete' -PercentComplete 100 -Completed
    Write-Progress -Id ($ActivityId + 2) -ParentId $ActivityId -Activity 'Importing Batches' -CurrentOperation 'Beginning Import' -PercentComplete 5

    ## With the ETL converted, Use it to create Import Batches
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/assetImport/loadData?filename="
    $uri += $EtlOutputKey
    $uri += "&dataScriptId="
    $uri += $ETLScript.id
                            
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                            
    ## Post the Transformed data filename to the ETL engine to Import the Batches.
    $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ImportJob = $responseContent.data
            $ETLProgressKey = $ImportJob.progressKey
        }
        else {
            throw 'Failed to Load Import Batch'
        }
    }
    else {
        Throw 'Failed to Load Import Batch.'
    }
    

    ## Ensure the Batches loaded and the job is finished
    ## The ETL is underway, Setup a progress URL
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/progress/" + $ETLProgressKey
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                
    ## Poll for the status of the ETL engine
    ## TODO: This should be converted to a function for polling the job engine. It's nearly dupilcated now in the Import Batch watching.
    $Completed = $false
    while ($Completed -eq $false) {
            
        ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
        $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                $ETLProgress = $responseContent.data
                                    
                switch ($ETLProgress.status) {
                    "Queued" { 
                        $CurrentOperation = 'Loading Batches Queued'
                        $Status = 'Queued'
                        $ProgressString = 'Status - Queued: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2    
                        Break
                    }
                    "Pending" { 
                        $CurrentOperation = 'Loading Batches Pending'
                        $Status = 'Pending Batch Loading'
                        $ProgressString = 'Status - Pending: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2
                        Break
                    }
                    "RUNNING" { 
                        $CurrentOperation = 'Loading Batches'
                        $Status = 'Loading Batches'
                        $ProgressString = 'Status - Running: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2
                        Break
                    }
                    "COMPLETED" {
                        $BatchGroupGuid = $ETLProgress.data.groupGuid
                        $CurrentOperation = 'Batch Loading Complete'
                        $Status = 'Loaded Import Batches'
                        $Completed = $true
                        $SleepSeconds = 0
                        $PercentComplete = 99
                        $ProgressString = "Status - Completed Loading Import Batches."
                        Break
                    }
                    "Failed" {
                        $CurrentOperation = 'Failed'
                        $Status = $ETLProgress.status
                        Write-Host "Batch Loading Processing Failed "$ETLProgress.detail
                        Throw $ETLProgress.detail
                    }
                    Default { 
                        $CurrentOperation = 'State Unknown'
                        $Status = 'Unknown. Sleeping to try again.'
                        $ProgressString = "Unknown Status: " + $ETLProgress.status
                        $PercentComplete = 99
                        $SleepSeconds = 2
                        Break
                    }
                }
                
                ## Notify the user of the ETL Progress
                if ($ActivityId) { 
                    $ProgressOptions = @{
                        Id               = ($ActivityId + 2)
                        ParentId         = $ActivityId
                        Activity         = 'Import Batch Loading'
                        CurrentOperation = $CurrentOperation
                        Status           = $Status
                        PercentComplete  = $PercentComplete
                    }
                    if ($Completed) {
                        Write-Progress @ProgressOptions -Completed
                    }
                    else {
                        Write-Progress @ProgressOptions
                    }
                }
                else {
                    Write-Host $ProgressString
                }
                Start-Sleep -Seconds $SleepSeconds
            }    
        }    
    }

    ## Update Progress and transition to Activity + 3
    Write-Progress -Id ($ActivityId + 2) -ParentId $ActivityId -Activity 'Batches Imported' -CurrentOperation 'Importing Batches Complete' -PercentComplete 100 -Completed
    Write-Progress -Id ($ActivityId + 3) -ParentId $ActivityId -Activity 'Monitoring Batches' -CurrentOperation 'Monitoring Batches' -PercentComplete 5

    ## OPTIONAL - If the switch was set to Queue the batches
    if ($QueueBatches) {
        
        ## Assemble the batch list object. Dependencies are deliberately moved to the end
        $BatchesToProcess = [System.Collections.ArrayList]@()


        ## Get the Batches that were created during the import
        ## With the ETL converted, Use it to create Import Batches
        $uri = "https://"
        $uri += $instance
        $uri += "/tdstm/ws/import/batches?groupGuid="
        $uri += $BatchGroupGuid
                                
        Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                                
        ## Post the Transformed data filename to the ETL engine to Import the Batches.
        $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                $Batches = $responseContent.data    
            }
            else {

            }
        }
        else {

        }

        ## Separate Asset batches from dependency Batches
        $Batches | Where-Object { $_.domainClass -ne 'Dependency' } | ForEach-Object { $BatchesToProcess.Add($_) | Out-Null }
        $Batches | Where-Object { $_.domainClass -eq 'Dependency' } | ForEach-Object { $BatchesToProcess.Add($_) | Out-Null }
    
        ## Write Progress to the Monitor Import Batches
        if ($ActivityId) { 
            Write-Progress -Id ($ActivityId + 3) `
                -ParentId $ActivityId `
                -Activity 'Manage Batches' `
                -CurrentOperation 'Queueing Batches' `
                -Status ('Queueing ' + [string]$Batches.Length + ' batches.') `
                -PercentComplete 5
        }
        else {
            Write-Host "Queueing Batches:" $Batches.Length
        }

        ## Add a Progress Activity for each of the Import Batches
        for ($i = 0; $i -lt $BatchesToProcess.Count; $i++) {
            
            ## Set Variable for Batch
            $Batch = $BatchesToProcess[$i]

            ## Create Progress Results for the batches that were created
            if ($ActivityId) { 
            
                ## Activity Explained: The $ActivityId is considered the (Root) + 2 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                    -ParentId ($ActivityId + 3) `
                    -Activity ($Batch.domainClassName + ' batch: ' + $Batch.id + ' | Total: ' + $Batch.recordsSummary.count) `
                    -CurrentOperation 'Queueing Batch' `
                    -Status ([String]$Batch.recordsSummary.count + ' ' + $Batch.domainClassName + ' records created.') `
                    -PercentComplete 10
            }
            else {
                Write-Host "Batch Created: " [String]$Batch.recordsSummary.count $Batch.domainClassName 'records created.'
            }
        }

        ## Queue each Batch
        for ($i = 0; $i -lt $BatchesToProcess.Count; $i++) {
            
            ## Set Variable for Batch
            $Batch = $BatchesToProcess[$i]
            if ($Batch.autoProcess -eq 0) {

                ## With the file uploaded, Initiate the ETL script on the server
                $uri = "https://"
                $uri += $instance
                $uri += "/tdstm/ws/import/batches"
                                
                Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
            
                $PostBody = @{ 
                    action = 'QUEUE'
                    ids    = $Batch.id
                } | ConvertTo-Json -Depth 100


                ## Post this batch to begin it's Queueing
                $response = Invoke-WebRequest -Method Patch -Uri $uri -WebSession $TMSessionConfig.TMWebSession -Body $PostBody @TMCertSettings
                if ($response.StatusCode -eq 200) {
                    $responseContent = $response.Content | ConvertFrom-Json
                    if ($responseContent.status -eq "success") {
                        
                        ## Create Progress Results for the batches that were created
                        if ($ActivityId) { 
                            
                            ## Notify the Manage Batches Activity of a queued batch
                            Write-Progress -Id ($ActivityId + 3) `
                                -ParentId $ActivityId `
                                -Activity 'Monitor Batches' `
                                -CurrentOperation 'Queueing Batches' `
                                -Status ('Queued ' + $Batch.domainClass + ' Batch') `
                                -PercentComplete 5

                            ## Activity (Root) + 2 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                            Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                                -ParentId ($ActivityId + 3) `
                                -Activity ($Batch.domainClassName + ' batch: ' + $Batch.id + ' | Total: ' + $Batch.recordsSummary.count) `
                                -CurrentOperation 'Batch Queued'  `
                                -Status 'Queued' `
                                -PercentComplete 0
                        }
                        else {
                            Write-Host "Batch Queued:" $Batch.domainClass
                        }
                    }
                    else {
                        Throw 'Failed to Queue Batch'
                    }    
                }
                else {
                    Throw 'Failed to Queue Batch.'
                }
            }
        }

        ## If Monitoring isn't going to occur, status should be complete here
        if ($MonitorBatches -ne $true) {
            Write-Progress -Id ($ActivityId + 3) -ParentId $ActivityId -Activity 'Batches Queued' -CurrentOperation 'Batches have been queued' -PercentComplete 100 -Completed
        }
    }

    ## Allow the batch to be monitored and reported on in the UI
    if ($MonitorBatches) {
        $BatchStatus = @{
            CompletedBatches = 0
            TotalBatches     = $BatchesToProcess.Count
        }
        
        ## Monitor the batches to completion
        for ($i = 0; $i -lt $BatchesToProcess.Count; $i++) {
            
            ## Set Variable for Batch
            $Batch = $BatchesToProcess[$i]
            if ($Batch.recordsSummary.count -gt 0) {
                ## Build the URL
                $uri = "https://"
                $uri += $instance
                $uri += "/tdstm/ws/import/batch/"
                $uri += $Batch.id
                $uri += "/progress"
                                    
                Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                
                ## Poll for the status of the Import Batch
                if ($ActivityId) { 
                    
                    ## Activity (Root) + 3 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                    Write-Progress -Id ($ActivityId + 3 + $i + 1) `
                        -ParentId ($ActivityId + 3) `
                        -Activity ($Batch.domainClassName + ' batch: ' + $Batch.id + ' | Total: ' + $Batch.recordsSummary.count) `
                        -CurrentOperation 'Monitoring Progress'  `
                        -Status 'Importing' `
                        -PercentComplete 1
                }
                else {
                    Write-Host 'Getting Status for '$Batch.domainClassName
                }

                ## Start a loop to run while the batch is not complete.
                $Completed = $false
                while ($Completed -eq $false) {
                    
                    ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
                    $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
                    if ($response.StatusCode -eq 200) {
                        $responseContent = $response.Content | ConvertFrom-Json
                        if ($responseContent.status -eq "success") {
                            
                            $BatchProgress = $responseContent.data
                            
                            switch ($BatchProgress.status.code) {
                                "RUNNING" { 
                                    $CurrentOperation = 'Import Running'
                                    $Status = 'Importing Batch Data'
                                    $ProgressString = $DomainClass + ' Status: Running - ' + $BatchProgress.progress + "%"
                                    $PercentComplete = $BatchProgress.progress    
                                    $SleepSeconds = 2
                                    break
                                }
                                "QUEUED" { 
                                    $CurrentOperation = 'Batch Queued'
                                    $Status = 'Batch Queued'
                                    $ProgressString = $DomainClass + ' Status: Queued'
                                    $PercentComplete = $BatchProgress.progress    
                                    $SleepSeconds = 2    
                                    break
                                    
                                }
                                "PENDING" { 
                                    $CurrentOperation = 'Batch Pending'
                                    $Status = 'Batch Pending'
                                    $ProgressString = $DomainClass + ' Status: Pending'
                                    $PercentComplete = $BatchProgress.progress    
                                    $SleepSeconds = 2
                                    break
                                    
                                }
                                "COMPLETED" {
                                    $Completed = $true
                                    $CurrentOperation = 'Complete'
                                    $Status = 'Complete'
                                    $ProgressString = ($batch.domainClassName + " Status: Complete")
                                    $PercentComplete = 100
                                    $SleepSeconds = 0
                                    break
                                    
                                }
                                Default { 
                                    $CurrentOperation = 'Status Unknown. Retrying'
                                    $Status = 'Retrying'
                                    $ProgressString = ($batch.domainClassName + "Status Unkown. Retrying")
                                    $PercentComplete = 1    
                                    $SleepSeconds = 2    
                                    break
                                }
                            }
                            
                            ## Display the Status of this loop
                            if ($ActivityId) { 
                    
                                $ProgressOptions = @{
                                    Id               = ($ActivityId + 3 + $i + 1)
                                    ParentId         = ($ActivityId + 3)
                                    Activity         = ($Batch.domainClassName + ' batch: ' + $Batch.id + ' | Total: ' + $Batch.recordsSummary.count)
                                    CurrentOperation = $CurrentOperation
                                    Status           = $Status
                                    PercentComplete  = $PercentComplete
                                }
                                ## Activity (Root) + 3 (to move to Monitoring Batches) + I for the looping + 1 to add a new layer
                                if ($Completed) { 
                                    Write-Progress @ProgressOptions -Completed
                                }
                                else {
                                    Write-Progress @ProgressOptions
                                }

                            }
                            else {
                                Write-Host $ProgressString
                            }
                            
                            ## TMD client now handles all progress percentage roll up when Parent id is specified. This is no longer necessary.
                            ## Calcuate Percentage for Manage Batches progress bar. This is based on how many batches there are, how many are done and what percentage we have in this loop
                            # $ManageBatchesPercentage = ((($BatchStatus.CompletedBatches * 100) + $PercentComplete) / ($BatchStatus.TotalBatches * 100))
                            
                            # Write-Progress -Id ($ActivityId + 3) `
                            # -ParentId $ActivityId `
                            # -Activity 'Manage Batches' `
                            # -CurrentOperation 'Monitoring Progress' `
                            # -Status 'Processing' `
                            # -PercentComplete $ManageBatchesPercentage
                            
                            ## Increate how many jobs are marked as completed
                            Start-Sleep -Seconds $SleepSeconds
                        }    
                    }
                }
            }
            ## Now that this batch is done, increment the Completed Batches counter
            $BatchStatus.CompletedBatches++
        }

        ## Mark the Manage Batches Activity Complete
        if ($ActivityId) {
            Write-Progress -Id ($ActivityId + 3) `
                -ParentId $ActivityId `
                -Activity 'Batches Posted' `
                -CurrentOperation 'Complete'  `
                -Status 'All Batches Imported' `
                -PercentComplete 100 `
                -Completed
        }
    }


    ## Mark the TM Import Activity complete
    if ($ActivityId) {
        Write-Progress -Id $ActivityId `
            -ParentId $ParentActivityId `
            -Activity 'TransitionManager Data Import' `
            -CurrentOperation 'Complete'  `
            -Status 'All Data Imported' `
            -PercentComplete 100 `
            -Complete
    }
}

Function Test-TMETLScript {
    param(
        [Parameter(Mandatory = $false)][String]$TMSession = "Default",
        [Parameter(Mandatory = $true)][String]$ETLScriptName,
        [Parameter(Mandatory = $false)][String]$Server = $global:TMSessions[$TMSession].TMServer,
        [Parameter(Mandatory = $false)]$AllowInsecureSSL = $global:TMSessions[$TMSession].AllowInsecureSSL,
        [Parameter(Mandatory = $false)][bool]$QueueBatches = $false,
        [Parameter(Mandatory = $false)][bool]$MonitorBatches = $false,
        [Parameter(Mandatory = $true)][String]$DataType,
        [Parameter(Mandatory = $false)]$Data,
        [Parameter(Mandatory = $false)][String]$FilePath,
        [Parameter(Mandatory = $false)][String]$FileName,
        [Parameter(Mandatory = $false)][Int16]$ActivityId,
        [Parameter(Mandatory = $false)][Int16]$ParentActivityId = -1
    )

    ## Get Session Configuration
    $TMSessionConfig = $global:TMSessions[$TMSession]
    if (-not $TMSessionConfig) {
        Write-Host 'TMSession: [' -NoNewline
        Write-Host $TMSession -ForegroundColor Cyan
        Write-Host '] was not Found. Please use the New-TMSession command.'
        Throw "TM Session Not Found. Use New-TMSession command before using features."
    }
    
    ## If a File Path was passed and there is no data, Read the file
    if ($FilePath -and -not $Data) {
        $Data = Get-Content $FilePath -Raw
        $FileName = (Get-Item $FilePath).Name
    }
    
    ## If a File Path was passed and there is no File Name, get the file name
    if ($FilePath -and -not $FileName) {
        $FileName = (Get-Item $FilePath).Name
    }
    
    #Honor SSL Settings
    if ($TMSessionConfig.AllowInsecureSSL) {
        $TMCertSettings = @{SkipCertificateCheck = $true }
    }
    else { 
        $TMCertSettings = @{SkipCertificateCheck = $false }
    }
    
    if ($ActivityId) {
        ## Parent ID is only used on the root 'TransitionManager Data Import' Activity
        ## ParentID + 1 = Transform Data
        ## ParentID + 2 = Import Batches
        ## ParentID + 3 + n for each batch

        ## Add the ETL Processing Progress Indicators
        $ProgressIndicators = @()
        $ProgressIndicators += @{ Id = $ActivityId; Activity = 'Test ETL Data Transformation'; ParentId = $ParentActivityId }
        
    }
    
    ## Fix the Server URL
    $instance = $Server.Replace('/tdstm', '')
    $instance = $instance.Replace('https://', '')
    $instance = $instance.Replace('http://', '')

    $Boundary = '----------------------------540299933173025267350719'

    ## First Test to see if the ETL Script exists or not
    if ($ActivityId) { 
        
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'ETL Data Transformation' -CurrentOperation 'Validating ETL Script' -Status 'Confirming ETL Script exists in TransitionManager' -PercentComplete 5
    }
    $ETLScript = Get-TMETLScript -TMSession $TMSession -Name $ETLScriptName
    if (-Not $ETLScript) { Throw  'The ETL Script [' + $ETLScriptName + '] does not exist' }
    
    ## Build the Data Post
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/fileSystem/uploadFileETLAssetImport"
        
    ## FileName
    if (-not $FileName) {
        $FileName = ('DataForETL_' + (Get-Date -Format FileDateTime ) + '.' + $DataType)
    }
    
    ## Construct the PostBody and Form Data for the request
    $CRLF = "`r`n";
    $PostBody = ("-----------------------------$boundary",
        'Content-Disposition: form-data; name="uploadType"',
        '',
        'assetImport',
        "-----------------------------$boundary",
        ''
    ) -join $CRLF
    $FileUpload = @{
        Form = @{
            uploadType = 'assetImport'
            file       = Get-Item -Path $FilePath
            fileName   = $FileName
        }
    }
    ## Upload the Data File
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'Testing ETL Data Transformation' -CurrentOperation 'Uploading Data' -Status ('Uploading ' + $FileName)  -PercentComplete 5
    }
    
    try {
        ## If the file is being uploaded, append the fileupload to the command
        if ($FileUpload) {
            $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -ContentType ('multipart/form-data; boundary=---------------------------' + $boundary) @TMCertSettings @FileUpload
        }
        else {
            $response = Invoke-WebRequest -Method Post -Uri $uri -WebSession $TMSessionConfig.TMWebSession -ContentType ('multipart/form-data; boundary=---------------------------' + $boundary) @TMCertSettings
        }

    }
    catch {
        throw $_
    }
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ETLdataFileName = $responseContent.data.filename
        }
        else { 
            Throw "Unable to upload data to TM ETL pre-transform storage."
        }
    }

    ## With the file uploaded, Initiate the ETL script on the server
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/dataScript/initiateTestScript"
    
    ## Create a post body
    $PostBody = [PSCustomObject]@{
        dataScriptId = $ETLScript.id
        filename     = $ETLdataFileName
        script       = ($ETLScript.etlSourceCode -replace $CRLF, '\n')
    } | ConvertTo-Json -Compress

    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession

    ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
    if ($ActivityId) { 
        Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'Testing ETL Data Transformation' -CurrentOperation 'Starting ETL Testing' -PercentComplete 5
    }
    else {
        Write-Host 'TransitionManager Data Import: Starting ETL Test'
    }
    
    $response = Invoke-WebRequest -Method Post -Uri $uri -Body $PostBody -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
    if ($response.StatusCode -eq 200) {
        $responseContent = $response.Content | ConvertFrom-Json
        if ($responseContent.status -eq "success") {
            $ETLProgressKey = $responseContent.data.progressKey
        }
        else {
            throw $responseContent.errors
        }
    }
    else {
        Throw $response
    }
    
    
    ## Supply Progress from ETL Transformation Progress
    if ($ActivityId) { 
        Write-Progress -Id $($ActivityId + 1) -Activity 'Testing ETL Data Transformation' -CurrentOperation 'Running ETL Transformation'  -PercentComplete 0 -ParentId $ActivityId
    }
    else {
        Write-Host 'Testing ETL Data Transformation: Starting ETL Processing'
    }
            
    ## The ETL is underway, Setup a progress URL
    $uri = "https://"
    $uri += $instance
    $uri += "/tdstm/ws/progress/" + $ETLProgressKey
    Set-TMHeaderContentType -ContentType JSON -TMSession $TMSession
                
    ## Poll for the status of the ETL engine
    ## TODO: This should be converted to a function for polling the job engine. It's nearly dupilcated now in the Import Batch watching.
    $Completed = $false
    while ($Completed -eq $false) {
            
        ## Post the data starting the ETL process. A progress key is provided. This progress key is what can be used to poll for the status of the ETL script
        $response = Invoke-WebRequest -Method Get -Uri $uri -WebSession $TMSessionConfig.TMWebSession @TMCertSettings
        if ($response.StatusCode -eq 200) {
            $responseContent = $response.Content | ConvertFrom-Json
            if ($responseContent.status -eq "success") {
                $ETLProgress = $responseContent.data
                                    
                switch ($ETLProgress.status) {
                    "Queued" { 
                        
                        $CurrentOperation = 'ETL Queued'
                        $Status = 'Queued'
                        $ProgressString = 'Status - Queued: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2
                        Break
                        
                    }
                    "Pending" { 
                        $CurrentOperation = 'ETL Pending'
                        $Status = 'Pending'
                        $ProgressString = 'Status - Pending: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2
                        Break
                    }
                    "RUNNING" { 
                        $CurrentOperation = 'ETL Running'
                        $Status = 'Transforming Data'
                        $ProgressString = 'Status - Running: ' + $ETLProgress.percentComp + '%'
                        $PercentComplete = $ETLProgress.percentComp    
                        $SleepSeconds = 2    
                        Break
                    }
                    "COMPLETED" {
                        $CurrentOperation = 'ETL Testing Complete'
                        $Status = 'Creating Import Batches'
                        $Completed = $true
                        $SleepSeconds = 0
                        $PercentComplete = 99
                        $ProgressString = "Status - ETL Testing Complete"
                        Break
                    }
                    "Failed" {
                        $CurrentOperation = 'Failed'
                        $Status = $ETLProgress.status
                        Write-Host "ETL Testing Failed "$ETLProgress.detail
                        Throw $ETLProgress.detail
                    }
                    Default { 
                        $CurrentOperation = 'State Unknown'
                        $Status = 'Unknown. Sleeping to try again.'
                        $ProgressString = "Unknown Status: " + $ETLProgress.status
                        $PercentComplete = 99
                        $SleepSeconds = 2
                        Break
                    }
                }
                
                ## Notify the user of the ETL Progress
                if ($ActivityId) { 
                    Write-Progress -Id ($ActivityId + 1) `
                        -ParentId $ActivityId `
                        -Activity 'Testing ETL Data Transformation' `
                        -CurrentOperation $CurrentOperation `
                        -Status $Status `
                        -PercentComplete $PercentComplete
                }
                else {
                    Write-Host $ProgressString
                }
                Start-Sleep -Seconds $SleepSeconds
            }    
        }    
    }

    ## Update Progress and transition to Activity + 2
    Write-Progress -Id ($ActivityId + 1) -ParentId $ActivityId -Activity 'Tested ETL Data Transformation' -CurrentOperation 'Testing Complete' -PercentComplete 100 -Completed
    
    ## Mark the TM Import Activity complete
    if ($ActivityId) {
        Write-Progress -Id $ActivityId `
            -ParentId $ParentActivityId `
            -Activity 'TransitionManager Data Import' `
            -CurrentOperation 'Complete'  `
            -Status 'All Data Imported' `
            -PercentComplete 100 `
            -Complete
    }
}

Function Read-TMETLScriptFile {
    param(
        [Parameter(Mandatory = $true)]$Path
    )

    
    ## Name the Input File
    $Content = Get-Content -Path $Path -Raw

    ## Ignore Empty Files
    if (-Not $Content) {
        return 
    }
    
    $ContentLines = Get-Content -Path $Path

    ## Create Automation Token Variables Parse the Script File
    New-Variable astTokens -Force
    New-Variable astErr -Force
    $ast = [System.Management.Automation.Language.Parser]::ParseInput($Content, [ref]$astTokens, [ref]$astErr)

    ##
    ## Assess the Script Parts to get delineating line numbers
    ##
    
    ## Locate the Delimiting line
    $ConfigBlockStartLine = $astTokens | `
        Where-Object { $_.Text -like '/*********TransitionManager-ETL-Script*********' } |`
        Select-Object -First 1 | `
        Select-Object -ExpandProperty Extent | `
        Select-Object -ExpandProperty StartLineNumber
    
    ## Test if the file has been written with MetaData
    if (-Not $ConfigBlockStartLine) {

        ## This file does not have metadata. Create the minimum data for the constructor below
        $EtlScriptConfig = @{
            EtlScriptName = (Get-Item -Path $Path).BaseName -replace '.TMETL', ''
            Description   = ""
            ProviderName  = (Get-Item -Path $Path).Directory.BaseName
            IsAutoProcess = $false
            Target        = $null
            Mode          = 'Import'
        }

        ## Add Variables that will be used in the construction
        $EtlScriptCode = $ContentLines
        $ConfigBlockEndLine = -1
    }
     else {

        ## This File has metadata - Work to parse it

        $ConfigBlockEndLine = $astTokens | `
            Where-Object { $_.Text -like '*********TransitionManager-ETL-Script*********/' } |`
            Select-Object -First 1 | `
            Select-Object -ExpandProperty Extent | `
            Select-Object -ExpandProperty StartLineNumber
    
        ## Adjust the Line Numbers to capture just the JSON
        $JsonConfigBlockStartLine = $ConfigBlockStartLine + 1
        $JsonConfigBlockEndLine = $ConfigBlockEndLine - 1
    

        ##
        ## Read the Script Header to gather the configurations
        ##

        ## Get all of the lines in the header comment
        $EtlConfigJson = $JsonConfigBlockStartLine..$JSONConfigBlockEndLine | ForEach-Object {
        
            ## Return the line for collection
            $ContentLines[$_ - 1]

        } | Out-String
    
        ## Convert the JSON string to an Object
        if ($EtlConfigJson) {
            $EtlScriptConfig = $EtlConfigJson | ConvertFrom-Json
        }
        else {
            $EtlScriptConfig = @{
                EtlScriptName = (Get-Item -Path $Path).BaseName
                Description   = ""
                ProviderName  = (Get-Item -Path $Path).Directory.Parent.Parent.BaseName
                IsAutoProcess = $false
                Target        = $null
                Mode          = 'Import'
            }
        }
    }

    ##
    ## Read the Script Block
    ##

    ## Note where the Configuration Code is located
    $StartCodeBlockLine = $ConfigBlockEndLine + 1
    $EndCodeBlockLine = $ast[-1].Extent.EndLineNumber

    ## Create a Text StrinBuilder to collect the Script into
    $ETLScriptStringBuilder = New-Object System.Text.StringBuilder

    ## For each line in the Code Block, add it to the Etl Script Code StringBuilder
    $StartCodeBlockLine..$EndCodeBlockLine | ForEach-Object {
        $EtlScriptStringBuilder.AppendLine($ContentLines[$_]) | Out-Null
    }

    ## Convert the StringBuilder to a Multi-Line String
    $EtlScriptCode = $EtlScriptStringBuilder.ToString()

    ## Assemble the Action Object
    $TMEtlScript = [pscustomobject]@{
        
        ## Primary Information
        id            = $null
        name          = $EtlScriptConfig.EtlScriptName
        description   = $EtlScriptConfig.Description
        
        ## Source Code
        etlSourceCode = $EtlScriptCode
        
        ## Provider
        provider      = @{
            id   = $null
            name = $EtlScriptConfig.ProviderName
        }
        
        ## Other details for the ETL Script
        dateCreated   = Get-Date
        lastUpdated   = Get-Date
    }
    
    ## Return the Action Object
    return $TMEtlScript
}