functions/Invoke-ImpactAnalysis.ps1
|
function Invoke-ImpactAnalysis { [CmdletBinding()] [OutputType([PSObject])] param ( [Parameter(Mandatory = $True)] [string]$Server, [Parameter(Mandatory = $False)] [string]$ConfigPath = ".\_impactConfig.json", [Parameter(Mandatory = $False)] [string]$OutDir = ".\_impact" ) begin { $Msg = "Impact analysis [$($Server)]"; Write-Host $Msg -ForegroundColor Magenta; Write-Verbose $Msg; Write-Log $Msg; if (!(Test-Path $ConfigPath)) { $Msg = "$(" " * 4)Unable to find configuration file in current directory or specified path"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Do { $TemplateFlag = Read-Host -Prompt 'Create a config template? (Y/N)' } while ('y', 'n' -notcontains $TemplateFlag) if ($TemplateFlag -eq 'y') { New-EmptyFile './_impactConfig.json'; Add-Content ./_impactConfig.json "{`n ""Columns"": {`n ""SQL"": {`n ""Connection"": {`n ""Database"": ""<database>""`n },`n ""FilePath"": ""./columns.sql""`n }`n },`n ""Queries"": {`n ""SQL"": {`n ""Connection"": {`n ""Database"": ""<database>""`n },`n ""FilePath"": ""./queries.sql""`n }`n },`n ""Mappings"": {`n ""CSV"": {`n ""FilePath"": ""./mappings.csv""`n }`n }`n}"; New-EmptyFile './columns.sql'; Add-Content ./columns.sql "SELECT`n /******REQUIRED******/`n tbl.DatabaseNM`n ,tbl.SchemaNM`n ,tbl.TableNM`n ,col.ColumnNM`n /********************/`n /* ADD ANY OTHER GROUPERS YOU NEED`n ,Grouper1NM?`n ,Grouper2NM?`n */`nFROM CatalystAdmin.TableBASE AS tbl`nINNER JOIN CatalystAdmin.DatamartBASE AS dm`n ON dm.DatamartID = tbl.DatamartID`nINNER JOIN CatalystAdmin.ColumnBASE AS col`n ON col.TableID = tbl.TableID`n AND col.IsSystemColumnFLG = 'N'`nWHERE dm.DatamartNM = '<MY_DATAMART>'`n AND tbl.PublicFLG = 1;" New-EmptyFile './mappings.csv'; '' | Select-Object FromDatabaseNM, FromSchemaNM, FromTableNM, FromColumnNM, ToDatabaseNM, ToSchemaNM, ToTableNM, ToColumnNM | Export-Csv './mappings.csv' -NoTypeInformation New-EmptyFile './queries.sql'; Add-Content ./queries.sql "SELECT`n /******REQUIRED******/`n obj.AttributeValueLongTXT AS QueryTXT`n /********************/`n /* ADD ANY OTHER GROUPERS YOU NEED`n ,tbl.ViewNM+' ('+b.BindingNM+')' AS QueryNM`n ,'SAM Designer' AS Grouper1NM`n ,dm.DatamartNM AS Grouper2NM`n */`nFROM CatalystAdmin.ObjectAttributeBASE AS obj`nINNER JOIN CatalystAdmin.BindingBASE AS b`n ON b.BindingID = obj.ObjectID`nINNER JOIN CatalystAdmin.TableBASE AS tbl`n ON tbl.TableID = b.DestinationEntityID`nINNER JOIN CatalystAdmin.DataMartBASE AS dm`n ON dm.DatamartID = tbl.DatamartID`nWHERE obj.ObjectTypeCD = 'Binding'`n AND obj.AttributeNM = 'UserDefinedSQL'`n AND b.BindingClassificationCD != 'SourceMart'`n AND LEN(obj.AttributeValueLongTXT) > 0`n AND tbl.TableID NOT IN`n(`n SELECT`n tbl.TableID`n FROM CatalystAdmin.TableBASE AS tbl`n INNER JOIN CatalystAdmin.DatamartBASE AS dm`n ON dm.DatamartID = tbl.DatamartID`n WHERE dm.DatamartNM = '<MY_DATAMART>'`n);" $Msg = "Configuration files created, rerun when you are ready.`r`n"; Write-Host $Msg -ForegroundColor Green; Write-Verbose $Msg; Write-Log $Msg; } Break; } else { $Msg = "$(" " * 4)Creating output directory..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; try { if (Test-Path $OutDir) { Remove-Item $OutDir -Recurse -Force | Out-Null } New-Item -ItemType Directory -Force -Path $OutDir -ErrorAction Stop | Out-Null $Msg = "$(" " * 8)Created ""$(Split-Path $OutDir -Leaf)"" directory"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; New-Item -ItemType Directory -Force -Path "$($OutDir)/raw/csv" -ErrorAction Stop | Out-Null $Msg = "$(" " * 8)Created ""$(Split-Path $OutDir -Leaf)/raw/csv"" directory"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; New-Item -ItemType Directory -Force -Path "$($OutDir)/raw/json" -ErrorAction Stop | Out-Null $Msg = "$(" " * 8)Created ""$(Split-Path $OutDir -Leaf)/raw/json"" directory"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 4)Unable to create output directory (""$(Split-Path $OutDir -Leaf)"" or ""$(Split-Path $OutDir -Leaf)/raw"")"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $Config = Get-Content $ConfigPath | ConvertFrom-Json $Properties = ($Config | Get-Member | Where-Object MemberType -eq NoteProperty).Name if (!($Properties -contains 'Columns' -and $Properties -contains 'Queries')) { $Msg = "$(" " * 8)Configruation file (""$(Split-Path $ConfigPath -Leaf)"") must contain all of the the following properies: Columns, Queries"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $MappingsFlag = $False; if ($Properties -contains 'Mappings') { $MappingsFlag = $True; } $Msg = "$(" " * 4)Getting data..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; #region Columns (REQUIRED) $ColumnsPath = $Config.Columns.SQL.FilePath if (!(Test-Path $ColumnsPath)) { $Msg = "$(" " * 4)Unable to find ""$(Split-Path $ColumnsPath -Leaf)"" specified in the configuration file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } else { try { $ColumnsSQL = Get-Content $ColumnsPath | Out-String try { $ColumnsDb = $Config.Columns.SQL.Connection.Database if (!($ColumnsDb)) { $Columns = Invoke-Sqlcmd -Query $ColumnsSQL -ServerInstance $Server } else { $Columns = Invoke-Sqlcmd -Query $ColumnsSQL -ServerInstance $Server -Database $ColumnsDb } $Msg = "$(" " * 8)$(($Columns | Measure-Object).Count) records from query ""$(Split-Path $ColumnsPath -Leaf)"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $Properties = ($Columns[0] | Get-Member | Where-Object MemberType -eq Property).Name if (!($Properties.ToLower() -contains 'databasenm' -and $Properties.ToLower() -contains 'schemanm' -and $Properties.ToLower() -contains 'tablenm' -and $Properties.ToLower() -contains 'columnnm')) { $Msg = "$(" " * 8)Sql query must contain the following columns: DatabaseNM, SchemaNM, TableNM, and ColumnNM"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $UpdatedColumns = @() $I = 0 foreach ($Column in $Columns) { $Fqn = "$($Column.DatabaseNM.ToLower()).$($Column.SchemaNM.ToLower()).$($Column.TableNM.ToLower() -replace 'base$', '').$($Column.ColumnNM.ToLower())" $UpdatedColumn = New-Object PSObject $UpdatedColumn | Add-Member -Type NoteProperty -Name `$ColumnId -Value $I.ToString() $UpdatedColumn | Add-Member -Type NoteProperty -Name `$Fqn -Value $Fqn $UpdatedColumn | Add-Member -Type NoteProperty -Name `$Queries -Value @() if ($MappingsFlag) { $UpdatedColumn | Add-Member -Type NoteProperty -Name `$Mappings -Value @() } foreach ($Property in $Properties) { $UpdatedColumn | Add-Member -Type NoteProperty -Name $Property -Value $Column.$Property } $UpdatedColumns += $UpdatedColumn $I++ } $Columns = $UpdatedColumns; } catch { $Msg = "$(" " * 8)Unable to establish a connection to db or execute query"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 8)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } catch { $Msg = "$(" " * 4)Unable to get the contents of the ""$(Split-Path $Config.Queries.SQL.FilePath -Leaf)"" file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } } #endregion #region Queries (REQUIRED) $QueriesPath = $Config.Queries.SQL.FilePath if (!(Test-Path $QueriesPath)) { $Msg = "$(" " * 4)Unable to find ""$(Split-Path $QueriesPath -Leaf)"" specified in the configuration file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } else { try { $QueriesSQL = Get-Content $QueriesPath | Out-String try { $QueriesDb = $Config.Queries.SQL.Connection.Database if (!($QueriesDb)) { $Queries = Invoke-Sqlcmd -Query $QueriesSQL -ServerInstance $Server -MaxCharLength 8000000 } else { $Queries = Invoke-Sqlcmd -Query $QueriesSQL -ServerInstance $Server -Database $QueriesDb -MaxCharLength 8000000 } $Msg = "$(" " * 8)$(($Queries | Measure-Object).Count) records from query ""$(Split-Path $QueriesPath -Leaf)"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $Properties = ($Queries[0] | Get-Member | Where-Object MemberType -eq Property).Name if (!($Properties.ToLower() -contains 'querytxt')) { $Msg = "$(" " * 8)Sql query must contain the following column: QueryTXT"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $I = 0 $UpdatedQueries = @() foreach ($Query in $Queries) { $UpdatedQuery = New-Object PSObject $UpdatedQuery | Add-Member -Type NoteProperty -Name `$QueryId -Value $I.ToString() $UpdatedQuery | Add-Member -Type NoteProperty -Name `$Query -Value $Query.querytxt $UpdatedQuery | Add-Member -Type NoteProperty -Name `$Columns -Value @() foreach ($Property in $Properties | Where-Object { $_.ToLower() -ne 'querytxt' }) { $UpdatedQuery | Add-Member -Type NoteProperty -Name $Property -Value $Query.$Property } $UpdatedQueries += $UpdatedQuery $I++ } $Queries = $UpdatedQueries; } catch { $Msg = "$(" " * 8)Unable to establish a connection to db or execute query"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 8)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } catch { $Msg = "$(" " * 4)Unable to get the contents of the ""$(Split-Path $Config.Queries.SQL.FilePath -Leaf)"" file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } } #endregion #region Mappings (OPTIONAL) if ($MappingsFlag) { $MappingsPath = $Config.Mappings.CSV.FilePath if (!(Test-Path $MappingsPath)) { $Msg = "$(" " * 4)Unable to find ""$(Split-Path $MappingsPath -Leaf)"" specified in the configuration file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } else { try { $Mappings = Get-Content $MappingsPath | ConvertFrom-Csv } catch { $Msg = "$(" " * 4)Unable to parse the contents of the ""$(Split-Path $Config.Mappings.CSV.FilePath -Leaf)"" file"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } $Msg = "$(" " * 8)$(($Mappings | Measure-Object).Count) records from csv ""$(Split-Path $MappingsPath -Leaf)"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $Properties = ($Mappings[0] | Get-Member | Where-Object MemberType -eq NoteProperty).Name if (!($Properties.ToLower() -contains 'fromdatabasenm' -and $Properties.ToLower() -contains 'fromschemanm' -and $Properties.ToLower() -contains 'fromtablenm' -and $Properties.ToLower() -contains 'fromcolumnnm' -and ` $Properties.ToLower() -contains 'todatabasenm' -and $Properties.ToLower() -contains 'toschemanm' -and $Properties.ToLower() -contains 'totablenm' -and $Properties.ToLower() -contains 'tocolumnnm')) { $Msg = "$(" " * 8)Csv file must contain the following columns: FromDatabaseNM, FromSchemaNM, FromTableNM, FromColumnNM, ToDatabaseNM, ToSchemaNM, ToTableNM, ToColumnNM"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } foreach ($Mapping in $Mappings) { $Fqn = "$($Mapping.FromDatabaseNM.ToLower()).$($Mapping.FromSchemaNM.ToLower()).$($Mapping.FromTableNM.ToLower() -replace 'base$', '').$($Mapping.FromColumnNM.ToLower())" $Index = $Columns.'$Fqn'.indexOf($Fqn) if ($Index -gt -1) { $AddMapping = New-Object PSObject $AddMapping | Add-Member -Type NoteProperty -Name ToDatabaseNM -Value $Mapping.ToDatabaseNM $AddMapping | Add-Member -Type NoteProperty -Name ToSchemaNM -Value $Mapping.ToSchemaNM $AddMapping | Add-Member -Type NoteProperty -Name ToTableNM -Value $Mapping.ToTableNM $AddMapping | Add-Member -Type NoteProperty -Name ToColumnNM -Value $Mapping.ToColumnNM $Columns[$Index].'$Mappings' += $AddMapping } } $Msg = "$(" " * 8)$(($Columns.'$Mappings' | Measure-Object).Count) columns assigned mappings"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; $MappingsFlag = $Columns | Where-Object { ($_.'$Mappings' | Measure-Object).Count -eq 0 } | Select-Object @{ n = 'FromDatabaseNM'; e = { $_.DatabaseNM } }, @{ n = 'FromSchemaNM'; e = { $_.SchemaNM } }, @{ n = 'FromTableNM'; e = { $_.TableNM } }, @{ n = 'FromColumnNM'; e = { $_.ColumnNM } }, ToDatabaseNM, ToSchemaNM, ToTableNM, ToColumnNM } } #endregion } catch { $Msg = "$(" " * 4)Unable to parse the contents of ""$(Split-Path $ConfigPath -Leaf)"""; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 4)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } } } process { $Msg = "$(" " * 4)Parsing queries..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; $ScriptStart = (Get-Date) $DataColumns = $Columns if ($MappingsFlag) { $DataMappings = @() foreach ($Column in $Columns) { foreach ($Mapping in $Column.'$Mappings') { $ColumnMapping = New-Object PSObject $ColumnMapping | Add-Member -Type NoteProperty -Name `$ColumnId -Value $Column.'$ColumnId' $ColumnMapping | Add-Member -Type NoteProperty -Name ToDatabaseNM -Value $Mapping.ToDatabaseNM $ColumnMapping | Add-Member -Type NoteProperty -Name ToSchemaNM -Value $Mapping.ToSchemaNM $ColumnMapping | Add-Member -Type NoteProperty -Name ToTableNM -Value $Mapping.ToTableNM $ColumnMapping | Add-Member -Type NoteProperty -Name ToColumnNM -Value $Mapping.ToColumnNM $DataMappings += $ColumnMapping } } } try { $I = 0; $J = 0; $Total = ($Queries | Measure-Object).Count; $DataQueriesToColumns = @() $DataQueries = @() foreach ($Query in $Queries) { if ($I -eq 0) { $Msg = "$(" " * 8)$(("{0:P0}" -f ($J/$Total)).PadLeft(5)) $($J.ToString().PadLeft($Total.ToString().Length))/$($Total) ...parsing..."; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } $Q = $True; $ParsedTables = $(Invoke-SqlParser -Query $Query.'$Query' -Log $False -SelectStar $False -Brackets $False) foreach ($ParsedTable in $ParsedTables) { foreach ($ParsedColumn in $ParsedTable.Columns) { $Fqn = "$($ParsedTable.FullyQualifiedNM.ToLower() -replace 'base$', '').$($ParsedColumn.ColumnNM.ToLower())" $Index = $DataColumns.'$Fqn'.indexOf($Fqn) if ($Index -gt -1) { $Match = New-Object PSObject $Match | Add-Member -Type NoteProperty -Name `$QueryId -Value $Query.'$QueryId' $Match | Add-Member -Type NoteProperty -Name `$ColumnId -Value $DataColumns[$Index].'$ColumnId' $DataQueriesToColumns += $Match if ($Q) { $DataQueries += $Query; $Q = $False; } } } } $I++; $J++; if ($I -eq 100) { $I = 0; } } $ScriptEnd = (Get-Date) $RunTime = New-Timespan -Start $ScriptStart -End $ScriptEnd $Msg = "$(" " * 8)$(("{0:P0}" -f ($Total/$Total)).PadLeft(5)) $($Total)/$($Total) Done ~ $("Elapsed Time: {0}:{1}:{2}" -f $RunTime.Hours, $Runtime.Minutes, $RunTime.Seconds)"; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)An error occurred during query parsing"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; $Msg = "$(" " * 8)$($Error[0])"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; Break; } foreach ($Column in $DataQueriesToColumns) { $IXColumn = $DataColumns.'$ColumnId'.IndexOf($Column.'$ColumnId') $IXQuery = $DataQueries.'$QueryId'.IndexOf($Column.'$QueryId') $QueryObj = New-Object PSObject; $QueryObj | Add-Member -Type NoteProperty -Name '$QueryId' -Value $DataQueries[$IXQuery].'$QueryId'; $ColumnObj = New-Object PSObject; $ColumnObj | Add-Member -Type NoteProperty -Name '$ColumnId' -Value $DataColumns[$IXColumn].'$ColumnId'; $DataColumns[$IXColumn].'$Queries' += $QueryObj; $DataQueries[$IXQuery].'$Columns' += $ColumnObj; } #region CSV files $Msg = "$(" " * 4)Creating output csv files..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; try { $DataColumns | Select-Object * -ExcludeProperty '$Mappings', '$Queries' | Export-Csv -Path "$($OutDir)/raw/csv/columns.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/columns.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/columns.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $DataQueries | Select-Object * -ExcludeProperty '$Query', '$Columns' | Export-Csv -Path "$($OutDir)/raw/csv/queries.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/queries.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/queries.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $DataQueriesToColumns | Export-Csv -Path "$($OutDir)/raw/csv/queries-to-columns.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/queries-to-columns.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/queries-to-columns.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } if ($MappingsFlag) { try { $DataMappings | Export-Csv -Path "$($OutDir)/raw/csv/mappings.csv" -NoTypeInformation -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/mappings.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/mappings.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $MappingsFlag | Export-Csv -Path $OutDir/raw/csv/unmapped.csv -NoTypeInformation $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/csv/unmapped.csv"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/csv/unmapped.csv"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } #endregion #region JSON files $Msg = "$(" " * 4)Creating output json files..."; Write-Host $Msg -ForegroundColor Gray; Write-Verbose $Msg; Write-Log $Msg; try { $DataColumns | ConvertTo-Json -Depth 100 -Compress | Out-File "$($OutDir)/raw/json/columns.json" -Encoding Default -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/json/columns.json"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/json/columns.json"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } try { $DataQueries | ConvertTo-Json -Depth 100 -Compress | Out-File "$($OutDir)/raw/json/queries.json" -Encoding Default -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/json/queries.json"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/json/queries.json"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } if ($MappingsFlag) { try { $MappingsFlag | ConvertTo-Json -Depth 100 -Compress | Out-File $OutDir/raw/json/unmapped.json -Encoding Default -Force $Msg = "$(" " * 8)File created ""$(Split-Path $OutDir -Leaf)/raw/json/unmapped.json"""; Write-Host $Msg -ForegroundColor White; Write-Verbose $Msg; Write-Log $Msg; } catch { $Msg = "$(" " * 8)Something failed when creating the ""$(Split-Path $OutDir -Leaf)/raw/json/unmapped.json"" files"; Write-Host $Msg -ForegroundColor Red; Write-Verbose $Msg; Write-Log $Msg 'error'; } } #endregion } end { $Msg = "Success!`r`n"; Write-Host $Msg -ForegroundColor Green; Write-Verbose $Msg; Write-Log $Msg; } } # SIG # Begin signature block # MIIcSQYJKoZIhvcNAQcCoIIcOjCCHDYCAQExDzANBglghkgBZQMEAgEFADB5Bgor # BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG # KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCDLDDAJ/MtODX4k # p5y9SjGJlSobumHbL05KTnTR9jOH6aCCCqMwggUwMIIEGKADAgECAhAECRgbX9W7 # ZnVTQ7VvlVAIMA0GCSqGSIb3DQEBCwUAMGUxCzAJBgNVBAYTAlVTMRUwEwYDVQQK # EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xJDAiBgNV # BAMTG0RpZ2lDZXJ0IEFzc3VyZWQgSUQgUm9vdCBDQTAeFw0xMzEwMjIxMjAwMDBa # Fw0yODEwMjIxMjAwMDBaMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2Vy # dCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lD # ZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwggEiMA0GCSqGSIb3 # DQEBAQUAA4IBDwAwggEKAoIBAQD407Mcfw4Rr2d3B9MLMUkZz9D7RZmxOttE9X/l # qJ3bMtdx6nadBS63j/qSQ8Cl+YnUNxnXtqrwnIal2CWsDnkoOn7p0WfTxvspJ8fT # eyOU5JEjlpB3gvmhhCNmElQzUHSxKCa7JGnCwlLyFGeKiUXULaGj6YgsIJWuHEqH # CN8M9eJNYBi+qsSyrnAxZjNxPqxwoqvOf+l8y5Kh5TsxHM/q8grkV7tKtel05iv+ # bMt+dDk2DZDv5LVOpKnqagqrhPOsZ061xPeM0SAlI+sIZD5SlsHyDxL0xY4PwaLo # LFH3c7y9hbFig3NBggfkOItqcyDQD2RzPJ6fpjOp/RnfJZPRAgMBAAGjggHNMIIB # yTASBgNVHRMBAf8ECDAGAQH/AgEAMA4GA1UdDwEB/wQEAwIBhjATBgNVHSUEDDAK # BggrBgEFBQcDAzB5BggrBgEFBQcBAQRtMGswJAYIKwYBBQUHMAGGGGh0dHA6Ly9v # Y3NwLmRpZ2ljZXJ0LmNvbTBDBggrBgEFBQcwAoY3aHR0cDovL2NhY2VydHMuZGln # aWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJlZElEUm9vdENBLmNydDCBgQYDVR0fBHow # eDA6oDigNoY0aHR0cDovL2NybDQuZGlnaWNlcnQuY29tL0RpZ2lDZXJ0QXNzdXJl # ZElEUm9vdENBLmNybDA6oDigNoY0aHR0cDovL2NybDMuZGlnaWNlcnQuY29tL0Rp # Z2lDZXJ0QXNzdXJlZElEUm9vdENBLmNybDBPBgNVHSAESDBGMDgGCmCGSAGG/WwA # AgQwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQuY29tL0NQUzAK # BghghkgBhv1sAzAdBgNVHQ4EFgQUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHwYDVR0j # BBgwFoAUReuir/SSy4IxLVGLp6chnfNtyA8wDQYJKoZIhvcNAQELBQADggEBAD7s # DVoks/Mi0RXILHwlKXaoHV0cLToaxO8wYdd+C2D9wz0PxK+L/e8q3yBVN7Dh9tGS # dQ9RtG6ljlriXiSBThCk7j9xjmMOE0ut119EefM2FAaK95xGTlz/kLEbBw6RFfu6 # r7VRwo0kriTGxycqoSkoGjpxKAI8LpGjwCUR4pwUR6F6aGivm6dcIFzZcbEMj7uo # +MUSaJ/PQMtARKUT8OZkDCUIQjKyNookAv4vcn4c10lFluhZHen6dGRrsutmQ9qz # sIzV6Q3d9gEgzpkxYz0IGhizgZtPxpMQBvwHgfqL2vmCSfdibqFT+hKUGIUukpHq # aGxEMrJmoecYpJpkUe8wggVrMIIEU6ADAgECAhAMMCpTLsjxo9FR9hag8ePUMA0G # CSqGSIb3DQEBCwUAMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJ # bmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0 # IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwHhcNMjAwMzMxMDAwMDAw # WhcNMjMwNTEwMTIwMDAwWjCBpzELMAkGA1UEBhMCVVMxDTALBgNVBAgTBFV0YWgx # FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVIZWFsdGggQ2F0YWx5 # c3QsIEluYy4xHjAcBgNVBAMTFUhlYWx0aCBDYXRhbHlzdCwgSW5jLjEwMC4GCSqG # SIb3DQEJARYhYWRtaW5uaXN0cmF0b3JAaGVhbHRoY2F0YWx5c3QuY29tMIIBIjAN # BgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA2fY0HWdxDJezDOsbHp7f9u/lrrD5 # nuZ1mENMgvixlrtC/KXgBRXlcWH7ajIOKljKnWCSAZwlZy4nFGbMagKmMzohXUXg # xo94u5nCdiBa/kgPazNGpL0AyGgX2VARMbcpm8Gdy+/uH3Kc7L91lcoGZVVBnVIt # 1oj5iXURqmhL83TrMyYqyj3XOH0So8Y10FVLPSukocMzMqBIRgvn/7EP0iWtOjXx # +o1wB5Ql+z9G3NCqF6CKE/Pn355XYbbmjF7BPzKoOjocHO6VU2uEflJWq1ZFb0QY # /tAosyyLYi9kFfO1damtJfRbbsVqavwg2UeQkzhg9CpB6eSsmBXPlFHudQIDAQAB # o4IBxTCCAcEwHwYDVR0jBBgwFoAUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHQYDVR0O # BBYEFFjfHOOIre2C4m9NCk8TFJlDwMxUMA4GA1UdDwEB/wQEAwIHgDATBgNVHSUE # DDAKBggrBgEFBQcDAzB3BgNVHR8EcDBuMDWgM6Axhi9odHRwOi8vY3JsMy5kaWdp # Y2VydC5jb20vc2hhMi1hc3N1cmVkLWNzLWcxLmNybDA1oDOgMYYvaHR0cDovL2Ny # bDQuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwTAYDVR0gBEUw # QzA3BglghkgBhv1sAwEwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNl # cnQuY29tL0NQUzAIBgZngQwBBAEwgYQGCCsGAQUFBwEBBHgwdjAkBggrBgEFBQcw # AYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tME4GCCsGAQUFBzAChkJodHRwOi8v # Y2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEQ29kZVNp # Z25pbmdDQS5jcnQwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQsFAAOCAQEAsBxn # 9yJAQi+9cJPZpJvOEV6iHaOBGv8898wNJCc4eB5g8WPziEY70GZVeqEdx3z0wS8U # QQIr19Hkju2NFZjDtzB9z1jAc/9EgqFGoCZbPijv1EYAa2oOVAp1BPbLjqBSdXqu # 2mzqo14CJ30oNom9ep9F6LGZ5zEoPsMrJejSbJGr4EacrksX8C8qeFklc7FzwiGk # GX7IQxidrrhOm2fOvGGAAxnvNYAR0FqJK0LiWWPSt5R/j63H/6HQtqD2sLevI3+O # bRP74TPchDobFmWlSogX9oB63E7fsbDAqecY0cRPQ6tVWK53Ke2sB514nahFjZDa # mxsa3/acZWL659ly3jGCEPwwghD4AgEBMIGGMHIxCzAJBgNVBAYTAlVTMRUwEwYD # VQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAv # BgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EC # EAwwKlMuyPGj0VH2FqDx49QwDQYJYIZIAWUDBAIBBQCgfDAQBgorBgEEAYI3AgEM # MQIwADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgorBgEEAYI3AgELMQ4w # DAYKKwYBBAGCNwIBFTAvBgkqhkiG9w0BCQQxIgQgEAPQqLWzidK+TPbXB9kbT+7q # xvoecsXvt2QwLCEaofcwDQYJKoZIhvcNAQEBBQAEggEAmMPPwse/ReMQlhh7zA8Z # fJCwO78DuZUuhah7qtWJSUG38ybMPXU56lYBmXiaz7h5+c4w9ilDWxFsQYY0GKc/ # EfeZtZeLZNzMyv9utspoJzOtaBnnTMWmeX9KDbdVIjLdlKBZPbxPuQvHapKHMoKL # fjxTnmoUxNnCIDwRiCVmR+OkCL1u5uusXqtVZ2uM4NSVLtAKgbvKIPiY1cMXTviC # q3+lkU8PeEfztCoc3rwqFuZPteO3qPCdfEYxrAhP72BQVnP9wxljrqiPyYZDYdyO # JwVFcgTI8GEDonPmlc1IFwBuRqNM26nBLdG9A9nUKl7ztFFtnv4CXY4GM7DQMX/o # 2KGCDsgwgg7EBgorBgEEAYI3AwMBMYIOtDCCDrAGCSqGSIb3DQEHAqCCDqEwgg6d # AgEDMQ8wDQYJYIZIAWUDBAIBBQAwdwYLKoZIhvcNAQkQAQSgaARmMGQCAQEGCWCG # SAGG/WwHATAxMA0GCWCGSAFlAwQCAQUABCB0PX7H4O+5/22R+VBOeUtZz7eqF6oT # VfoKP93k0ralOwIQe0MP4wYns2uIU1qHcuiJgRgPMjAyMDA1MDUyMzIzMjRaoIIL # uzCCBoIwggVqoAMCAQICEATNP4VornbGG7D+cWDMp20wDQYJKoZIhvcNAQELBQAw # cjELMAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQ # d3d3LmRpZ2ljZXJ0LmNvbTExMC8GA1UEAxMoRGlnaUNlcnQgU0hBMiBBc3N1cmVk # IElEIFRpbWVzdGFtcGluZyBDQTAeFw0xOTEwMDEwMDAwMDBaFw0zMDEwMTcwMDAw # MDBaMEwxCzAJBgNVBAYTAlVTMRcwFQYDVQQKEw5EaWdpQ2VydCwgSW5jLjEkMCIG # A1UEAxMbVElNRVNUQU1QLVNIQTI1Ni0yMDE5LTEwLTE1MIIBIjANBgkqhkiG9w0B # AQEFAAOCAQ8AMIIBCgKCAQEA6WQ1nPqpmGVkG+QX3LgpNsxnCViFTTDgyf/lOzwR # KFCvBzHiXQkYwvaJjGkIBCPgdy2dFeW46KFqjv/UrtJ6Fu/4QbUdOXXBzy+nrEV+ # lG2sAwGZPGI+fnr9RZcxtPq32UI+p1Wb31pPWAKoMmkiE76Lgi3GmKtrm7TJ8mUR # DHQNsvAIlnTE6LJIoqEUpfj64YlwRDuN7/uk9MO5vRQs6wwoJyWAqxBLFhJgC2ki # jE7NxtWyZVkh4HwsEo1wDo+KyuDT17M5d1DQQiwues6cZ3o4d1RA/0+VBCDU68jO # hxQI/h2A3dDnK3jqvx9wxu5CFlM2RZtTGUlinXoCm5UUowIDAQABo4IDODCCAzQw # DgYDVR0PAQH/BAQDAgeAMAwGA1UdEwEB/wQCMAAwFgYDVR0lAQH/BAwwCgYIKwYB # BQUHAwgwggG/BgNVHSAEggG2MIIBsjCCAaEGCWCGSAGG/WwHATCCAZIwKAYIKwYB # BQUHAgEWHGh0dHBzOi8vd3d3LmRpZ2ljZXJ0LmNvbS9DUFMwggFkBggrBgEFBQcC # AjCCAVYeggFSAEEAbgB5ACAAdQBzAGUAIABvAGYAIAB0AGgAaQBzACAAQwBlAHIA # dABpAGYAaQBjAGEAdABlACAAYwBvAG4AcwB0AGkAdAB1AHQAZQBzACAAYQBjAGMA # ZQBwAHQAYQBuAGMAZQAgAG8AZgAgAHQAaABlACAARABpAGcAaQBDAGUAcgB0ACAA # QwBQAC8AQwBQAFMAIABhAG4AZAAgAHQAaABlACAAUgBlAGwAeQBpAG4AZwAgAFAA # YQByAHQAeQAgAEEAZwByAGUAZQBtAGUAbgB0ACAAdwBoAGkAYwBoACAAbABpAG0A # aQB0ACAAbABpAGEAYgBpAGwAaQB0AHkAIABhAG4AZAAgAGEAcgBlACAAaQBuAGMA # bwByAHAAbwByAGEAdABlAGQAIABoAGUAcgBlAGkAbgAgAGIAeQAgAHIAZQBmAGUA # cgBlAG4AYwBlAC4wCwYJYIZIAYb9bAMVMB8GA1UdIwQYMBaAFPS24SAd/imu0uRh # pbKiJbLIFzVuMB0GA1UdDgQWBBRWUw/BxgenTdfYbldygFBM5OyewTBxBgNVHR8E # ajBoMDKgMKAuhixodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vc2hhMi1hc3N1cmVk # LXRzLmNybDAyoDCgLoYsaHR0cDovL2NybDQuZGlnaWNlcnQuY29tL3NoYTItYXNz # dXJlZC10cy5jcmwwgYUGCCsGAQUFBwEBBHkwdzAkBggrBgEFBQcwAYYYaHR0cDov # L29jc3AuZGlnaWNlcnQuY29tME8GCCsGAQUFBzAChkNodHRwOi8vY2FjZXJ0cy5k # aWdpY2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEVGltZXN0YW1waW5nQ0Eu # Y3J0MA0GCSqGSIb3DQEBCwUAA4IBAQAug6FEBUoE47kyUvrZgfAau/gJjSO5PdiS # oeZGHEovbno8Y243F6Mav1gjskOclINOOQmwLOjH4eLM7ct5a87eIwFH7ZVUgeCA # exKxrwKGqTpzav74n8GN0SGM5CmCw4oLYAACnR9HxJ+0CmhTf1oQpvgi5vhTkjFf # 2IKDLW0TQq6DwRBOpCT0R5zeDyJyd1x/T+k5mCtXkkTX726T2UPHBDNjUTdWnkcE # EcOjWFQh2OKOVtdJP1f8Cp8jXnv0lI3dnRq733oqptJFplUMj/ZMivKWz4lG3DGy # kZCjXzMwYFX1/GswrKHt5EdOM55naii1TcLtW5eC+MupCGxTCbT3MIIFMTCCBBmg # AwIBAgIQCqEl1tYyG35B5AXaNpfCFTANBgkqhkiG9w0BAQsFADBlMQswCQYDVQQG # EwJVUzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNl # cnQuY29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVkIElEIFJvb3QgQ0EwHhcN # MTYwMTA3MTIwMDAwWhcNMzEwMTA3MTIwMDAwWjByMQswCQYDVQQGEwJVUzEVMBMG # A1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMTEw # LwYDVQQDEyhEaWdpQ2VydCBTSEEyIEFzc3VyZWQgSUQgVGltZXN0YW1waW5nIENB # MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvdAy7kvNj3/dqbqCmcU5 # VChXtiNKxA4HRTNREH3Q+X1NaH7ntqD0jbOI5Je/YyGQmL8TvFfTw+F+CNZqFAA4 # 9y4eO+7MpvYyWf5fZT/gm+vjRkcGGlV+Cyd+wKL1oODeIj8O/36V+/OjuiI+GKwR # 5PCZA207hXwJ0+5dyJoLVOOoCXFr4M8iEA91z3FyTgqt30A6XLdR4aF5FMZNJCMw # XbzsPGBqrC8HzP3w6kfZiFBe/WZuVmEnKYmEUeaC50ZQ/ZQqLKfkdT66mA+Ef58x # FNat1fJky3seBdCEGXIX8RcG7z3N1k3vBkL9olMqT4UdxB08r8/arBD13ays6Vb/ # kwIDAQABo4IBzjCCAcowHQYDVR0OBBYEFPS24SAd/imu0uRhpbKiJbLIFzVuMB8G # A1UdIwQYMBaAFEXroq/0ksuCMS1Ri6enIZ3zbcgPMBIGA1UdEwEB/wQIMAYBAf8C # AQAwDgYDVR0PAQH/BAQDAgGGMBMGA1UdJQQMMAoGCCsGAQUFBwMIMHkGCCsGAQUF # BwEBBG0wazAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tMEMG # CCsGAQUFBzAChjdodHRwOi8vY2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRB # c3N1cmVkSURSb290Q0EuY3J0MIGBBgNVHR8EejB4MDqgOKA2hjRodHRwOi8vY3Js # NC5kaWdpY2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290Q0EuY3JsMDqgOKA2 # hjRodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290 # Q0EuY3JsMFAGA1UdIARJMEcwOAYKYIZIAYb9bAACBDAqMCgGCCsGAQUFBwIBFhxo # dHRwczovL3d3dy5kaWdpY2VydC5jb20vQ1BTMAsGCWCGSAGG/WwHATANBgkqhkiG # 9w0BAQsFAAOCAQEAcZUS6VGHVmnN793afKpjerN4zwY3QITvS4S/ys8DAv3Fp8MO # IEIsr3fzKx8MIVoqtwU0HWqumfgnoma/Capg33akOpMP+LLR2HwZYuhegiUexLoc # eywh4tZbLBQ1QwRostt1AuByx5jWPGTlH0gQGF+JOGFNYkYkh2OMkVIsrymJ5Xgf # 1gsUpYDXEkdws3XVk4WTfraSZ/tTYYmo9WuWwPRYaQ18yAGxuSh1t5ljhSKMYcp5 # lH5Z/IwP42+1ASa2bKXuh1Eh5Fhgm7oMLSttosR+u8QlK0cCCHxJrhO24XxCQijG # GFbPQTS2Zl22dHv1VjMiLyI2skuiSpXY9aaOUjGCAk0wggJJAgEBMIGGMHIxCzAJ # BgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5k # aWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBU # aW1lc3RhbXBpbmcgQ0ECEATNP4VornbGG7D+cWDMp20wDQYJYIZIAWUDBAIBBQCg # gZgwGgYJKoZIhvcNAQkDMQ0GCyqGSIb3DQEJEAEEMBwGCSqGSIb3DQEJBTEPFw0y # MDA1MDUyMzIzMjRaMCsGCyqGSIb3DQEJEAIMMRwwGjAYMBYEFAMlvVBe2pYwLcIv # T6AeTCi+KDTFMC8GCSqGSIb3DQEJBDEiBCCVX/7Glid6UPVsURt9L64v5OluUCHq # S27Zb0KO2LdW7zANBgkqhkiG9w0BAQEFAASCAQAgZHiucFNJGbZlWUieL/kxLPIE # BR/lO30Rx/wIiUL58JdqHQf4ol4/bCsdQuRLea3wyKSTWACG8EdlBoGpdk3T54DN # T2q1asI1APlMlckHSCU3yXdKTJ9HzrMeSof7+P1DgOc8PFacOYaK/Wk23wOzLOsw # 0xuXeQOGy+0rW3g6UolplhU4DDZ/vUn1Gb15s4C7Ejs86QmoCQoX+6RMIQcVX5XL # OBCIW1aLFBTIGIn9JFhAB/+P6zHx2nq3p02QSHl1VhQ/vAlA8sTsehVY7dDrrmK8 # 0SzXZmbQt0mwiVZxulpscu5AVaoPtTqud4LRa0T9p5EOY3KxIW9bn+GpVQfM # SIG # End signature block |