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 # MIIcRgYJKoZIhvcNAQcCoIIcNzCCHDMCAQExDzANBglghkgBZQMEAgEFADB5Bgor # BgEEAYI3AgEEoGswaTA0BgorBgEEAYI3AgEeMCYCAwEAAAQQH8w7YFlLCE63JNLG # KX7zUQIBAAIBAAIBAAIBAAIBADAxMA0GCWCGSAFlAwQCAQUABCDLDDAJ/MtODX4k # p5y9SjGJlSobumHbL05KTnTR9jOH6aCCCqAwggUwMIIEGKADAgECAhAECRgbX9W7 # 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 # aGxEMrJmoecYpJpkUe8wggVoMIIEUKADAgECAhAKRecO+XBAYPQ5XoaaebXrMA0G # CSqGSIb3DQEBCwUAMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJ # bmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0 # IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0EwHhcNMTcwNDEzMDAwMDAw # WhcNMjAwNDE1MTIwMDAwWjCBpDELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcw # FQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVSGVhbHRoIENhdGFseXN0 # LCBJbmMuMR4wHAYDVQQDExVIZWFsdGggQ2F0YWx5c3QsIEluYy4xLzAtBgkqhkiG # 9w0BCQEWIGFkbWluaXN0cmF0b3JAaGVhbHRoY2F0YWx5c3QuY29tMIIBIjANBgkq # hkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAv8AEfB5imOv8J17fvW8w+WKuE0keRub9 # 1+QzkiI+nSa9y2yADr/ZCEXqxGqDKdg47CjlvpOmKg8K88NPaTPvGN5fm7p7avmn # Cfp7IGXLGtutZ1RnFW2fYC8+kl86WinKVQ7eHLe7Rsvn9CyurIzttJpJcTikxqrr # U45yE8Iw/H9ziiwP+grfm8AiGN3C2vuxbhs8YwG2pbbn2aa5hN5q4bbFzoQ4xHGO # kFiqhRYVyGbVZNeoGTpkf/DNXJh07RuSDdcFXoh7whwwvfXhrk9Z5YzE6GEk2CUF # adTjqWHuGyfpBpY7bYZ8/mbDTmUqLNeGsTQrVmowv4r+usyK6lz6LwIDAQABo4IB # xTCCAcEwHwYDVR0jBBgwFoAUWsS5eyoKo6XqcQPAYPkt9mV1DlgwHQYDVR0OBBYE # FDCXth9LjWUWNRWEPkEw5VZAVdBSMA4GA1UdDwEB/wQEAwIHgDATBgNVHSUEDDAK # BggrBgEFBQcDAzB3BgNVHR8EcDBuMDWgM6Axhi9odHRwOi8vY3JsMy5kaWdpY2Vy # dC5jb20vc2hhMi1hc3N1cmVkLWNzLWcxLmNybDA1oDOgMYYvaHR0cDovL2NybDQu # ZGlnaWNlcnQuY29tL3NoYTItYXNzdXJlZC1jcy1nMS5jcmwwTAYDVR0gBEUwQzA3 # BglghkgBhv1sAwEwKjAoBggrBgEFBQcCARYcaHR0cHM6Ly93d3cuZGlnaWNlcnQu # Y29tL0NQUzAIBgZngQwBBAEwgYQGCCsGAQUFBwEBBHgwdjAkBggrBgEFBQcwAYYY # aHR0cDovL29jc3AuZGlnaWNlcnQuY29tME4GCCsGAQUFBzAChkJodHRwOi8vY2Fj # ZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEQ29kZVNpZ25p # bmdDQS5jcnQwDAYDVR0TAQH/BAIwADANBgkqhkiG9w0BAQsFAAOCAQEAkIewxl/k # WdhH2w7hIW0jT2WXhasjLk/UVeJtON2V7uj6J5/geg9huBlF9UDASBN9Po3sULeE # /WQ+Lxbd3BDLq+jcENPKdEE7v9NFOCzs142tBJ+tng5uSD4KCG7wStTggI8XElpu # 0uraecK21bq4T4A2uGXpruEVNdS8DkANh34AwLJWanhaavbqunHZMkjQU0oluktS # ikJ1BVeyROM0Xh11VBnM5nSftS4c8eC66ZXhsuc268wwzwb3eD81jKwXdli3SrvT # zFKtAFqzh2/1bVIceq+iT7zketpGuFTg3BOkhbiJhIEjAS9pA3v+tVKrWcdTp/HC # mT2XH0Xyeg2GhzGCEPwwghD4AgEBMIGGMHIxCzAJBgNVBAYTAlVTMRUwEwYDVQQK # EwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdpY2VydC5jb20xMTAvBgNV # BAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBDb2RlIFNpZ25pbmcgQ0ECEApF # 5w75cEBg9Dlehpp5teswDQYJYIZIAWUDBAIBBQCgfDAQBgorBgEEAYI3AgEMMQIw # ADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAcBgorBgEEAYI3AgELMQ4wDAYK # KwYBBAGCNwIBFTAvBgkqhkiG9w0BCQQxIgQgEAPQqLWzidK+TPbXB9kbT+7qxvoe # csXvt2QwLCEaofcwDQYJKoZIhvcNAQEBBQAEggEAuWGQPWHRcgTziLCmuM6MIiGU # TmMTgp/BCgPLMnw7kw8bD3TccKTW10oOjQf0sBzw/7y0trGZ5qbQlJbugABkAJKR # cJtv5vORmXskRO580Fwwnseg2YnaastJqUDeGPMvANt40KD0A3y8qi+it1B8lvAN # BK4Y4/Om3hraxgsL8XTC1lei9misgGzN/aZEksHR8A+O06IvWp+eZNUU4sh9pjDn # tDmqZWkA3JCjIKHVuR8HvJIDa76l3h96Ge9d5HI+LhN3MtNMehDMz84ZlstylSQh # ujJZVUp3iZAfJO7rVbLOp5ONdPaunuPdnsTJosZeay+/+jpeTBu0Wsy+Obiy3qGC # Dsgwgg7EBgorBgEEAYI3AwMBMYIOtDCCDrAGCSqGSIb3DQEHAqCCDqEwgg6dAgED # MQ8wDQYJYIZIAWUDBAIBBQAwdwYLKoZIhvcNAQkQAQSgaARmMGQCAQEGCWCGSAGG # /WwHATAxMA0GCWCGSAFlAwQCAQUABCDyYjt5L8/zcLmaQWb9teLxG1ZWh8NgLYLa # 5+Dna9flqgIQL08mguNYhJTtF2066PdLqBgPMjAxOTAxMTYyMzExNTNaoIILuzCC # BoIwggVqoAMCAQICEAnA/EbIBEITtVmLryhPTkEwDQYJKoZIhvcNAQELBQAwcjEL # MAkGA1UEBhMCVVMxFTATBgNVBAoTDERpZ2lDZXJ0IEluYzEZMBcGA1UECxMQd3d3 # LmRpZ2ljZXJ0LmNvbTExMC8GA1UEAxMoRGlnaUNlcnQgU0hBMiBBc3N1cmVkIElE # IFRpbWVzdGFtcGluZyBDQTAeFw0xNzAxMDQwMDAwMDBaFw0yODAxMTgwMDAwMDBa # MEwxCzAJBgNVBAYTAlVTMREwDwYDVQQKEwhEaWdpQ2VydDEqMCgGA1UEAxMhRGln # aUNlcnQgU0hBMiBUaW1lc3RhbXAgUmVzcG9uZGVyMIIBIjANBgkqhkiG9w0BAQEF # AAOCAQ8AMIIBCgKCAQEAnpWYajQ7cxuofvzHvilpicdoJkZfPY1ic4eBo6Gc8Ldb # JDdaktT0Wdd2ieTc1Sfw1Wa8Cu60KzFnrFjFSpFZK0UeCQHWZLNZ7o1mTfsjXswQ # DQuKZ+9SrqAIkMJS9/WotW6bLHud57U++3jNMlAYv0C1TIy7V/SgTxFFbEJCueWv # 1t/0p3wKaJYP0l8pV877HTL/9BGhEyL7Esvv11PS65fLoqwbHZ1YIVGCwsLe6is/ # LCKE0EPsOzs/R8T2VtxFN5i0a3S1Wa94V2nIDwkCeN3YU8GZ22DEnequr+B+hkpc # qVhhqF50igEoaHJOp4adtQJSh3BmSNOO74EkzNzYZQIDAQABo4IDODCCAzQwDgYD # VR0PAQH/BAQDAgeAMAwGA1UdEwEB/wQCMAAwFgYDVR0lAQH/BAwwCgYIKwYBBQUH # AwgwggG/BgNVHSAEggG2MIIBsjCCAaEGCWCGSAGG/WwHATCCAZIwKAYIKwYBBQUH # AgEWHGh0dHBzOi8vd3d3LmRpZ2ljZXJ0LmNvbS9DUFMwggFkBggrBgEFBQcCAjCC # AVYeggFSAEEAbgB5ACAAdQBzAGUAIABvAGYAIAB0AGgAaQBzACAAQwBlAHIAdABp # AGYAaQBjAGEAdABlACAAYwBvAG4AcwB0AGkAdAB1AHQAZQBzACAAYQBjAGMAZQBw # AHQAYQBuAGMAZQAgAG8AZgAgAHQAaABlACAARABpAGcAaQBDAGUAcgB0ACAAQwBQ # AC8AQwBQAFMAIABhAG4AZAAgAHQAaABlACAAUgBlAGwAeQBpAG4AZwAgAFAAYQBy # AHQAeQAgAEEAZwByAGUAZQBtAGUAbgB0ACAAdwBoAGkAYwBoACAAbABpAG0AaQB0 # ACAAbABpAGEAYgBpAGwAaQB0AHkAIABhAG4AZAAgAGEAcgBlACAAaQBuAGMAbwBy # AHAAbwByAGEAdABlAGQAIABoAGUAcgBlAGkAbgAgAGIAeQAgAHIAZQBmAGUAcgBl # AG4AYwBlAC4wCwYJYIZIAYb9bAMVMB8GA1UdIwQYMBaAFPS24SAd/imu0uRhpbKi # JbLIFzVuMB0GA1UdDgQWBBThpzJK7gEhKH1U1fIHkm60Bw89hzBxBgNVHR8EajBo # MDKgMKAuhixodHRwOi8vY3JsMy5kaWdpY2VydC5jb20vc2hhMi1hc3N1cmVkLXRz # LmNybDAyoDCgLoYsaHR0cDovL2NybDQuZGlnaWNlcnQuY29tL3NoYTItYXNzdXJl # ZC10cy5jcmwwgYUGCCsGAQUFBwEBBHkwdzAkBggrBgEFBQcwAYYYaHR0cDovL29j # c3AuZGlnaWNlcnQuY29tME8GCCsGAQUFBzAChkNodHRwOi8vY2FjZXJ0cy5kaWdp # Y2VydC5jb20vRGlnaUNlcnRTSEEyQXNzdXJlZElEVGltZXN0YW1waW5nQ0EuY3J0 # MA0GCSqGSIb3DQEBCwUAA4IBAQAe8EGCMq7t8bQ1E9xQwtWXriIinQ4OrzPTTP18 # v28BEaeUZSJcxiKhyIlSa5qMc1zZXj8y3hZgTIs2/TGZCr3BhLeNHe+JJhMFVvNH # zUdbrYSyOK9qI7VF4x6IMkaA0remmSL9wXjP9YvYDIwFCe5E5oDVbXDMn1MeJ90q # SN7ak2WtbmWjmafCQA5zzFhPj0Uo5byciOYozmBdLSVdi3MupQ1bUdqaTv9QBYko # 2vJ4u9JYeI1Ep6w6AJF4aYlkBNNdlt8qv/mlTCyT/+aK3YKs8dKzooaawVWJVmpH # P/rWM5VDNYkFeFo6adoiuARD029oNTZ6FD5F6Zhkhg8TDCZKMIIFMTCCBBmgAwIB # AgIQCqEl1tYyG35B5AXaNpfCFTANBgkqhkiG9w0BAQsFADBlMQswCQYDVQQGEwJV # UzEVMBMGA1UEChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQu # Y29tMSQwIgYDVQQDExtEaWdpQ2VydCBBc3N1cmVkIElEIFJvb3QgQ0EwHhcNMTYw # MTA3MTIwMDAwWhcNMzEwMTA3MTIwMDAwWjByMQswCQYDVQQGEwJVUzEVMBMGA1UE # ChMMRGlnaUNlcnQgSW5jMRkwFwYDVQQLExB3d3cuZGlnaWNlcnQuY29tMTEwLwYD # VQQDEyhEaWdpQ2VydCBTSEEyIEFzc3VyZWQgSUQgVGltZXN0YW1waW5nIENBMIIB # IjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvdAy7kvNj3/dqbqCmcU5VChX # tiNKxA4HRTNREH3Q+X1NaH7ntqD0jbOI5Je/YyGQmL8TvFfTw+F+CNZqFAA49y4e # O+7MpvYyWf5fZT/gm+vjRkcGGlV+Cyd+wKL1oODeIj8O/36V+/OjuiI+GKwR5PCZ # A207hXwJ0+5dyJoLVOOoCXFr4M8iEA91z3FyTgqt30A6XLdR4aF5FMZNJCMwXbzs # PGBqrC8HzP3w6kfZiFBe/WZuVmEnKYmEUeaC50ZQ/ZQqLKfkdT66mA+Ef58xFNat # 1fJky3seBdCEGXIX8RcG7z3N1k3vBkL9olMqT4UdxB08r8/arBD13ays6Vb/kwID # AQABo4IBzjCCAcowHQYDVR0OBBYEFPS24SAd/imu0uRhpbKiJbLIFzVuMB8GA1Ud # IwQYMBaAFEXroq/0ksuCMS1Ri6enIZ3zbcgPMBIGA1UdEwEB/wQIMAYBAf8CAQAw # DgYDVR0PAQH/BAQDAgGGMBMGA1UdJQQMMAoGCCsGAQUFBwMIMHkGCCsGAQUFBwEB # BG0wazAkBggrBgEFBQcwAYYYaHR0cDovL29jc3AuZGlnaWNlcnQuY29tMEMGCCsG # AQUFBzAChjdodHRwOi8vY2FjZXJ0cy5kaWdpY2VydC5jb20vRGlnaUNlcnRBc3N1 # cmVkSURSb290Q0EuY3J0MIGBBgNVHR8EejB4MDqgOKA2hjRodHRwOi8vY3JsNC5k # aWdpY2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290Q0EuY3JsMDqgOKA2hjRo # dHRwOi8vY3JsMy5kaWdpY2VydC5jb20vRGlnaUNlcnRBc3N1cmVkSURSb290Q0Eu # Y3JsMFAGA1UdIARJMEcwOAYKYIZIAYb9bAACBDAqMCgGCCsGAQUFBwIBFhxodHRw # czovL3d3dy5kaWdpY2VydC5jb20vQ1BTMAsGCWCGSAGG/WwHATANBgkqhkiG9w0B # AQsFAAOCAQEAcZUS6VGHVmnN793afKpjerN4zwY3QITvS4S/ys8DAv3Fp8MOIEIs # r3fzKx8MIVoqtwU0HWqumfgnoma/Capg33akOpMP+LLR2HwZYuhegiUexLoceywh # 4tZbLBQ1QwRostt1AuByx5jWPGTlH0gQGF+JOGFNYkYkh2OMkVIsrymJ5Xgf1gsU # pYDXEkdws3XVk4WTfraSZ/tTYYmo9WuWwPRYaQ18yAGxuSh1t5ljhSKMYcp5lH5Z # /IwP42+1ASa2bKXuh1Eh5Fhgm7oMLSttosR+u8QlK0cCCHxJrhO24XxCQijGGFbP # QTS2Zl22dHv1VjMiLyI2skuiSpXY9aaOUjGCAk0wggJJAgEBMIGGMHIxCzAJBgNV # BAYTAlVTMRUwEwYDVQQKEwxEaWdpQ2VydCBJbmMxGTAXBgNVBAsTEHd3dy5kaWdp # Y2VydC5jb20xMTAvBgNVBAMTKERpZ2lDZXJ0IFNIQTIgQXNzdXJlZCBJRCBUaW1l # c3RhbXBpbmcgQ0ECEAnA/EbIBEITtVmLryhPTkEwDQYJYIZIAWUDBAIBBQCggZgw # GgYJKoZIhvcNAQkDMQ0GCyqGSIb3DQEJEAEEMBwGCSqGSIb3DQEJBTEPFw0xOTAx # MTYyMzExNTNaMCsGCyqGSIb3DQEJEAIMMRwwGjAYMBYEFEABkUdcmIkd66EEr0cJ # G1621MvLMC8GCSqGSIb3DQEJBDEiBCBoKF+J/Fg3DMw36LHkUBHaSsXObuLFzjvy # 4uFzQv6LOzANBgkqhkiG9w0BAQEFAASCAQAxE6aYG6AaDmzjB2ISsVsXUycGWaud # 7+6VdQdcXZqbHffUeZIxa5B40NZtfqRstJ8oauS5Btu1Yp9sVAOZml+akRgS0sC+ # gNc+m/al9Ic4HCRoHixmbDKxrDUlaxzfHN5IPjRMvoI8f2K46R39AnpVWKG7DgCO # Iwy69QYaFAgZnRzccFVNbehLGqiIr4UCKoRs3Lsv18jHCY8l7hZ04QSctfJdhYfR # lVWoIWDRXAyQzqLIl93Oj7KN0Yjiizp+lO+RPXKF/2w9yXM6HfcPH2aQ8kWEDuSw # mdMc1cjnOdJrx0OIDSRIx6f96EwmQx+vjhz6nFgzQ5YbfnTxVnI3raUd # SIG # End signature block |